I have a spreadsheet with many tabs. I am trying to develop a summery sheet that will ‘auto fill’ certain rows of data and still remain dynamic. What I am doing: in Column I, I have a number; I want this to be the row number for finding the data in the other worksheet. I have If Statements in Columns A thru E for filling the data if Column I has a number in it. If Column I is empty then nothing appears. Formula is: =IF(I53 <> "","=+Agency Reallocation data!D&I53","") But, when I use this formula I get =+Agency Reallocation data!D&I53 Can someone help me make the formula work so that when Column has a positive integer in it the formula will get the correct data. Thanks for your time!

0 |

5/27/2010 9:07:47 PM

Jacob: Thanks for the help, but it didn't work I get the #Ref error. "Jacob Skaria" wrote: > Try > =IF(I53>0,INDIRECT("'+Agency Reallocation data'!D" & I53),"") > > -- > Jacob (MVP - Excel) > > > "Mathew" wrote: > > > I have a spreadsheet with many tabs. I am trying to develop a summery sheet > > that will ‘auto fill’ certain rows of data and still remain dynamic. > > > > What I am doing: in Column I, I have a number; I want this to be the row > > number for finding the data in the other worksheet. I have If Statements in > > Columns A thru E for filling the data if Column I has a number in it. If > > Column I is empty then nothing appears. Formula is: > > > > =IF(I53 <> "","=+Agency Reallocation data!D&I53","") > > > > But, when I use this formula I get =+Agency Reallocation data!D&I53 > > Can someone help me make the formula work so that when Column has a positive > > integer in it the formula will get the correct data. Thanks for your time! > > > >

0 |

5/27/2010 7:53:45 PM

Check out your sheet name. Do you have a "+" in front? -- Jacob (MVP - Excel) "Mathew" wrote: > Jacob: Thanks for the help, but it didn't work I get the #Ref error. > > "Jacob Skaria" wrote: > > > Try > > =IF(I53>0,INDIRECT("'+Agency Reallocation data'!D" & I53),"") > > > > -- > > Jacob (MVP - Excel) > > > > > > "Mathew" wrote: > > > > > I have a spreadsheet with many tabs. I am trying to develop a summery sheet > > > that will ‘auto fill’ certain rows of data and still remain dynamic. > > > > > > What I am doing: in Column I, I have a number; I want this to be the row > > > number for finding the data in the other worksheet. I have If Statements in > > > Columns A thru E for filling the data if Column I has a number in it. If > > > Column I is empty then nothing appears. Formula is: > > > > > > =IF(I53 <> "","=+Agency Reallocation data!D&I53","") > > > > > > But, when I use this formula I get =+Agency Reallocation data!D&I53 > > > Can someone help me make the formula work so that when Column has a positive > > > integer in it the formula will get the correct data. Thanks for your time! > > > > > >

0 |

5/27/2010 8:44:34 PM

Try =IF(I53>0,INDIRECT("'+Agency Reallocation data'!D" & I53),"") -- Jacob (MVP - Excel) "Mathew" wrote: > I have a spreadsheet with many tabs. I am trying to develop a summery sheet > that will ‘auto fill’ certain rows of data and still remain dynamic. > > What I am doing: in Column I, I have a number; I want this to be the row > number for finding the data in the other worksheet. I have If Statements in > Columns A thru E for filling the data if Column I has a number in it. If > Column I is empty then nothing appears. Formula is: > > =IF(I53 <> "","=+Agency Reallocation data!D&I53","") > > But, when I use this formula I get =+Agency Reallocation data!D&I53 > Can someone help me make the formula work so that when Column has a positive > integer in it the formula will get the correct data. Thanks for your time! > >

0 |

5/27/2010 9:41:54 PM

I dont know if this is a fix because I am not sure what you are trying to achieve but your quotes are around too much of the data if you are intending to return the value form the 'agency' sheet. The only quoted element should be the name of the sheet:- =IF(I53 <> "",'Agency Reallocation data'!D&I53,"") However, The D&I53 also looks dodgy. I *think* what you are trying to achieve is:- =IF(A1 <> "",INDEX('Agency Reallocation data'!D:D,A1,1),"") this looks up (index function) the content of an array ('Agency Reallocation data'!D:D), starting at row (a1) and colum 1 (ie the first column in the array) hth RegMigrant "Mathew" wrote: > I have a spreadsheet with many tabs. I am trying to develop a summery sheet > that will ‘auto fill’ certain rows of data and still remain dynamic. > > What I am doing: in Column I, I have a number; I want this to be the row > number for finding the data in the other worksheet. I have If Statements in > Columns A thru E for filling the data if Column I has a number in it. If > Column I is empty then nothing appears. Formula is: > > =IF(I53 <> "","=+Agency Reallocation data!D&I53","") > > But, when I use this formula I get =+Agency Reallocation data!D&I53 > Can someone help me make the formula work so that when Column has a positive > integer in it the formula will get the correct data. Thanks for your time! > >

0 |

5/27/2010 9:48:13 PM

RegMigrant You are the man, or Woman! Thank you! "Reg" wrote: > I dont know if this is a fix because I am not sure what you are trying to > achieve but > your quotes are around too much of the data if you are intending to return > the value form the 'agency' sheet. > > The only quoted element should be the name of the sheet:- > =IF(I53 <> "",'Agency Reallocation data'!D&I53,"") > > However, The D&I53 also looks dodgy. I *think* what you are trying to > achieve is:- > > =IF(A1 <> "",INDEX('Agency Reallocation data'!D:D,A1,1),"") > > this looks up (index function) the content of an array ('Agency Reallocation > data'!D:D), starting at row (a1) and colum 1 (ie the first column in the > array) > > hth > > RegMigrant > > > > "Mathew" wrote: > > > I have a spreadsheet with many tabs. I am trying to develop a summery sheet > > that will ‘auto fill’ certain rows of data and still remain dynamic. > > > > What I am doing: in Column I, I have a number; I want this to be the row > > number for finding the data in the other worksheet. I have If Statements in > > Columns A thru E for filling the data if Column I has a number in it. If > > Column I is empty then nothing appears. Formula is: > > > > =IF(I53 <> "","=+Agency Reallocation data!D&I53","") > > > > But, when I use this formula I get =+Agency Reallocation data!D&I53 > > Can someone help me make the formula work so that when Column has a positive > > integer in it the formula will get the correct data. Thanks for your time! > > > >

0 |

5/27/2010 9:49:12 PM

Hi, A user has been complaining of getting junk email that are not addressed to her but in the TO field is addressed to random addresses. For example: 34dfgj$3@domain.com, 450ug*$%@domain.com ... etc. PLease help because I'm a novice at Exchange and don't know why she's the only one getting this. How do I fix this? Thanks. What product are you using for anti-spam? On Thu, 7 Apr 2005 16:59:19 -0700, "Geeo" <huypham98@hotmail.com> wrote: >Hi, >A user has been complaining of getting junk email that are not addressed to >her but in the TO field is addres...

Hello. Here is my dilemma. I have two worksheets. One with records that include first name, last name & company. The second worksheet has first name, last name, company and email address. I am trying to 'fill in the blanks' on worksheet 1. What I want to do something that says if first name & last name & company name match in both workbook 1 & workbook 2, then place the email address found in worksheet 2 to the appropriate cell in worksheet 1. Is it possible? ...

I have a complex header that I need to be part of the sheet and cannot put in the header section so I have put it in and used Repeat Rows at Top of Page to ensure it is on every page. I would like to insert the page number in Cell J5 (within the Repeat Rows area) and would like it to change on every page. Is there anyway this can be done? I'm not an advanced user, can copy and paste a macro but do not know how to write my own or use VBA. THANKS! Kathryn hi, Kathryn ! > I have a complex header that I need to be part of the sheet and cannot put in the header section > so...

Hi I would like to do the following but have no idea what the format of the formula should be so any help appreciated: =IF(AND(EP86426="Standard",AA86426>0),1,0) So I want cell EP86426 to contain the word standard and I want the cell AA86426 (which in itself has a formula which returns 1 or 0) to be greater than 0. this part of the formula works but I also want to look at cell CK86426 and if its value is greater than zero I want it to return a 1 in cell AB86426 I hope that is clearly explained lol Thanks in advance Sam "Sam Hamman" <sam@theham.com> wrote: ...

Have to make some cards up. I need to do several on a sheet, when I set it up to do more than one card, how do you do it so it numbers them, say starting aat an odd number, then in numerical order from there? Build a database with consecutive numbers and use mail merge. Help here: http://office.microsoft.com/en-us/publisher/CH062524751033.aspx You can use Excel, Access, a Word table or Publisher to build your list. There are other databases. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Bruce" <notreally@idontw...

hi I would like to drop values into 5 columns AC-AH depending upon the value entered into column N. The ranges will be <4999 5000-9999 10000-29999 30000-49999 50000-99999 >100000 I have successfuly done the first criteria based upon one argument (less than 4999) However, the next band i am struggling with the greater and less than within IF - HELP please!!! This is what I have but not working.... =IF(AND(N1626>=5000,<9999),N1626,"") thanks in advance Test the borders just outside: =IF(AND(N1626>4999,N1626<10000),N1626,"") -- Gary'''...

I have created a graph and I know how to add a data table to the bottom that has the data in the graph. However, I wish to put different data in that table. Is there a way to do that? You can similate the effect. See Custom Chart Table http://www.tushar-mehta.com/excel/newsgroups/data_table/index.htm -- Regards, Tushar Mehta www.tushar-mehta.com Multi-disciplinary business expertise + Technology skills = Optimal solution to your business problem Recipient Microsoft MVP award 2000-2005 In article <4DC8A259-AB15-4AB5-8805-03AE6D173CD1@microsoft.com>, ratio@discussions.micros...

Hey everyone, =IF(A1=0,9,A1-1) This formulas says if A1=0, make it 9 and subtract 1 How do I make it say if A1 equals 0, make A2= 5 and if A1=5 make A2=0 But there are 5 conditions. They mirror each other. Example: a 0 is a 5 and a 5 is a 0. 0=5, 5=0 1= 6, 6=1 2=7, 7=2 3=8, 8=3 4=9, 9=4 How do I make the cell meet all of these conditions. Thank you for your help. -- tenaj ------------------------------------------------------------------------ tenaj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32691 View this thread: http://www.excelforum.com/showthr...

Am attempting to count the number of conditions in a data field with a certain stated value; in a group summary and then a report summary (i.e. count the Number of records where the sum of hours is between 0 and 24). I "sum of hours" an aggregate total or a field in the report's record source? If it is a field, you can use an expression like: =Sum(Abs([sum of hours]<= 24)) -- Duane Hookom Microsoft Access MVP "Lindy" wrote: > Am attempting to count the number of conditions in a data field with a > certain stated value; in a group summary and then a r...

Ever since I have ran the GDI Plus scanner tool (I had already installed XP SP2) Money 03 will not download my statements properly. I can save them to my computer, but previously I had been opening them when queried, and the updates were then automatically made to my file. But now nothing happens - including not getting the "your file has successfully been updated" message. Can this be fixed? Is there some new security setting? OR, can someone give me the instructions for saving the download, and then getting Money to update my file from that. hanks. In microsoft.public...

I have worksheet (destination) in which several cells are linked to a worksheet (source) in another workbook. My destination worksheet has not been updated with the latest values in the source sheet, so all the cells are filled with 0s, etc. Cell D2 in my destination worksheet is currently linked to V2 in the source worksheet. I'd like to make E2 be linked to V2. When I click on V2 (source), choose Copy, click on E2 (destination), and choose Paste Special > Paste Links, two problems occur: 1. The link is copied but it's copied as $V$2 (whereas all of the other links don't have...

I have an excel file that is on a ssl website and it has links to other excel files on the same site. I use Office 2003 to update the links in the file. The first problem I have is that the update proces is very slow. The other problem I have is that for some of the links I have to open the source file to be able to update the values. Any suggestions? ...

How do I choose every ninth row to be my Y data on a chart? I have 15,000 rows so picking one individually will be time consuming. Thank you. Holly Jon Peltier has instructions for this in his Excel Charting FAQ article: http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=209#jon024 Holly wrote: > How do I choose every ninth row to be my Y data on a > chart? I have 15,000 rows so picking one individually > will be time consuming. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

I am creating an email newsletter for my school alumni. I have no problem linking text boxes when I create the newsletter..however, when the text box is NOT selected there is no means of toggling to the continuation. No place to click.. I tried emailing the newsletter to myself and all I see is the message, continued on page 2 but no means of toggling to the continuation. Help?? You cannot create a two "page" email, try making the newsletter physically longer with File | Page Setup - if that's what you are trying to do. "Chris, Hudson Valley" <Chris, Hudson ...

I have a graff for sales. Data series 1 = Budget Data series 2 = Actual I want the value that is shown with data series 2 to be a % of the value of data series 1. ...

how do i create a link that when you click on it, it will open the pdf document in a new window? ...

Hi All I am trying to find a way to write and if/then statement. I have a column(A) with gas stations (MoBil, Citgo, Etc) next to it I have a column (B) of gas MPG. I want to average each type of gas. So I was thinking of adding a column (C) for each type of gas. If A#=C(header), THEN place B# in C#. I can then take and average the column. I just don't know how to write it? OR is there a simplier way to write it? Thanks Bill -- bhowe ------------------------------------------------------------------------ bhowe's Profile: http://www.excelforum.com/member.php?action=getinfo&user...

Hi, I have an Excel spreadsheet with a hyperlinked image (.tif) on each row, total of 85 rows. The image opens when clicked. How can I e-mail the spreadsheet file and transport the linked files (images) with it so that it will ope nin the same way for the receiver?? tezmo ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Tezmo They will work if the receiver put them in the same named folder in the same location. Alternatively, you will have to write some code that o...

I've used wlm for over a year now. For a few days now I have been getting 0x80191002 error for one of the hotmail accounts. The mail is not getting into wlmail. Other hotmail accounts and pop ARE unaffected. After research in this group I've tried 1. emptying outbox and deleted folders and doing message store compaction 2. deleting HKEY_CURRENT_USER\Software\Microsoft\Windows Live Mail\SearchFolderVersion to fix possible quick view problem 3. moving message store to new location with tools/options/advanced/maintenance\store folder 4. waiting a few days. All to no ava...

I'm wondering if I'm doing my message handling all wrong. Currently when I send my thread a messages (using postthreadmessage from my main app.) I handling it in the PreTranslateMessage function. While reading the MSDN I noticed that it said that all messages come in to the run function and are then passed on to the PreTranslateMessage function. Is this true? What are the remifications of leaving it like it is if it's working? Thanks Tom tawright915 wrote: > I'm wondering if I'm doing my message handling all wrong. Currently > when I send my thread a messages ...

Hello! I'm trying to link to a lead and it basically works like this: http://crmserver/SFA/leads/edit.aspx/?id={#leadid#} But that menubar on the left "Information" "Activities" "Notes" won't work properly in that new window. Instead of the activities or notes listing the links will open a "New Lead" form (with a second top menubar...) in the frame on the right. Can someone help me make this work? Thanks. ...

Hi ,I have a program like the following lines, my question is when I execute the program in VS6 environment ,it can execute successfully and correctly, but when I recompile this program is VS2005 environment, the program can still run, but the result is not correct! Could anybody tell me why? //--codes starts here typedef long PRICE; typedef unsigned long AMOUNT; typedef double MONEY; struct RPA_MIN { time_t Time; PRICE PriceO; PRICE PriceC; PRICE PriceMax; PRICE PriceMin; AMOUNT Amount; AMOUNT Mone...

I have the following code on the On Format property of one of my Headers on my report: Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer) If ChangePri = True Then Priority.BackColor = vbGreen Priority.FontWeight = 800 Else Priority.BackColor = vbWhite Priority.FontWeight = 400 If min = True Then sono.BackColor = vbYellow sono.FontWeight = 800 Else sono.BackColor = vbWhite sono.FontWeight = 400 If missed = True Then QtyShi...

Is it possible to test if a workbook has external links to another workbookl what i am trying to do is display a message when a user closes a workbook saying the workbook has external links. How would i code this?? I believe there is a security setting that does this check when the workbook opens(Excel 2007). "hervinder" <hervinder@discussions.microsoft.com> wrote in message news:08A95302-287D-4E91-A841-84F44CA309A4@microsoft.com... > Is it possible to test if a workbook has external links to another > workbookl > > what i am trying to do i...

Situation I have many contacts linked to an Account. So ... I get a new account address ... do I need to drill into each record and update each contact .... or is there a way to link them, or auto update related records Rhett ...