If statement linking data from one sheet to another

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!   
   

1
Utf
5/27/2010 9:07:47 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

5 Replies
2849 Views

Similar Articles

[PageSpeed] 40

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
Utf
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
Utf
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
Utf
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
Utf
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
Utf
5/27/2010 9:49:12 PM
Reply:

Similar Artilces:

* In IF Statement
Novice Excel 2003 user who knows absolutely nothing about VBA. I am trying to use an IF statement referencing "1*" in my criteria but it doesn't seem to be able to do this. I have departments "AR" in cell F1, "BSC" in F2, "Finish" in F3, and "Surface" in F4. AR is equal to 1, BSC equal to 2, Finish equal to 3, and Surface equal to 4. In B2:B29 I reference department numbers 1, 2, 3, or 4, and then a letter equal to A through V. Basically what I am looking for is a formula that will enter the department name in C2:C29 based on th...

If Statement inside If statement....
Hello all, I'd like to start by thanking all on this forum for the great advice and assistance you provide! Saved me more times than I can count! OK.... Here is the formula I currently have... =IF(J16="W",ABS(SUM(H16/I16)),- I16) This formula works great however in place of "ABS(SUM(H16/I16))" I need to tell it to do that if the value in H16 is negative. If H16 is positive I want it to perform "=I16*(H16/100)" I'm not sure I explained what I want clearly so I'm going to put it in words.... IF J16 is "W" then if the value in H16 is negat...

Data validation through code
Hi Experts, You know that DATA VALIDATION works only in case of entering values in cells by typing (prvents entering wrong values). It does not work in case of pasting. It also does not work in case of cells having formula. I read in some books that we can use worksheet_Change event or worksheet_Calculate to validate data through code. But code can validate data only after cell is changed. It means we can not prevent wrong entries, we also can not restore original values. Please tell me, is my conception right? or is there any way to handle this problem. Regards, Diva You are right that Exce...

Statements: Numbering and then being able to apply based on state
Have a client who can have 20,000 invoices for a customer in a month. The customer only gets a statement. Would like to be able to number statements and then if the customer pays the full amount be able to pay based on the statement number. Thought about using lockbox processing to try and set up a format based on the electronic data that comes back with the check as well. But need to find if anyone has a solution for the statement numbering We created a customization for a customer about 5-6 years ago that does something like this...it was pretty involved. From what I recall, ther...

3 different data ranges to chart on one graph
I have a graph I am trying to create that in essence has three different types of data in it, but they are all associated. I am charting 2000-2005 information. I would have 7 columns of data Year / Spending / %change from prior year / Segments / % change / Project counts / %change from prior year For example this would be what 2001 would look like... 2001/1,500,000/56%/22,000,000/-34%/16,000/84% So my numbers are all over the scale. Can I do this? Any charting advice would be greatly appreciated. Hi Donna - One of the principles of effective data presentation, is to keep it s...

Links for G.D.
Hallo. Could somebody suggest any links for graphic designers? Logotypes, free photos, icons, printing, free soft. Tired of googling:) Thanks. Try alt.design.graphics -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "sp" <info@wek.lt> wrote in message news:e0RTecARFHA.164@TK2MSFTNGP12.phx.gbl... > Hallo. Could somebody suggest any links for graphic designers? > Logotypes, free photos, icons, printing, free soft. Tired of googling:) > Thanks. > > It's not always a good idea to post that you want l...

Can I create a statement from a spreadsheet
I have created a spreadsheet of 90 people going on a trip for our school showing the amount of the trip and each payment and six or seven payments with check numbers. I also have their current balance. Would I be able to print a statement of their account? The short answer is "yes". However, the best method of doing this, depending on how your data is arranged, is probably to use the XL sheet as a data source for a data merge in Word. In article <2EAF704F-6D0D-4C68-BDF7-EEA5F38EB85E@microsoft.com>, pfb99 <pfb99@discussions.microsoft.com> wrote: > I have crea...

Link to task in web page
How can I place a link on a webpage to point to my Tasks folder in outlook? ...

Linking to other sheets
Lets say I have a table on another sheet, and I want to lookup a name there, then report the value sitting next to it. The sheets names are such that they have something like "My Sheet 1" then "My Sheet 2" then "My Sheet 3" etc. Forgetting the complication of VLOOKUP, since I know how to do that, how would I refer to the sheets, but get it to change from cell to cell? For example, in cell C2 I have ='My Sheet 1'!E1044. How would I get it so that I could just drag it to cell C3 and it would say ='My Sheet 2'!E1044? Is there any way to do this?...

IF OR Statements
Anybody know how to write a formula for a statement involving "if" and "or" statements such as: if (cell x=? and (cell y=? or cell z=?)) then cell Q=? or blank In general terms: =IF(AND(X=?,OR(Y=?,Z=?)),?,"") This formula would have to be in cell Q An example with actual cells: =IF(AND(A1=10,OR(A2=15,A3=20)),100,"") This formula must be in the cell to which the result should be returned. A formula can't "reach out" and put a value in another cell; it just returns a value to the cell that has the formula. Please note th...

Time Shift effieceincy data
New to excel but have been presented with this project. Any and all help sincerely appreciated. 8 hr shift efficiency data Shift time 8 hrs. Need to document time spent moving, on break/lunch, paper movement 0800 on duty 0845 starts initial shift 0900 movement for 45” 0945 stops movement for break 1000 start again 1045 break 1100 movement for 1 hr 1200 lunch 1300 movement 1400 movement until 1430 1430 complete paper movement until 1500 1500 off duty Thanks would like to put in a chart for productivity -- John ...

link to browser
all of a sudden when I try to use the link imbedded in a mail message the browser asks for a file link. help Browse to the folder that has IE or Netscape or whatever your default browser is and click on the executable for the browser. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, john asked: | all of a sudden when I try to use the link imbedded in a | mail message the browser asks for a fi...

linking web pages
I have created a simple web site containing 5 pages below the home page, looks good in the navigational structure...and I have added a link bar (based on nav. strct) for all 6 pages. In FP, I can hold the ctrl key and select any of my link bar buttons and it will take me to the appropriate page. When I publish it will only show me the home page without the link bars (no text or bar). The navigation web site view shows the levels correctly, but when I view the hyperlink view, it does not show that any of my pages are linked to each other. Can anyone help me through this? Thank you-...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Data validation problem
Hail to you all.... First post here and hopefully the last... Second, sorry if i'm not on the right board... Third, sorry for my not perfect english.... Well enough "being sorry thing" here my problem... I'm currently validating a form in excell and some bug just got int the equation wich i cannot remove (solve).... I got a list of text value c4:c28 wich contain some number like "p-12" i got a cell g4 wich CANNOT take any of the value in c4:c28 here what i got thus far : =MAJUSCULE(G4)<>RECHERCHE(MAJUSCULE(G4);C4:C28;C4:C28) it in french so i will give y...

Statement Line Items not purging off statement
I have a few customer statements that although payments and returns have been applied to invoices and paid in full are not purging off their statement?? There are a few different ways to achieve this outcome. This method is when NOT using National Accounts. I find most customers do not want to run the Paid Transaction Removal process too soon, in case of voids and credits are required. We modify the RM Statement and add a cacluated field to supress the body when the current transaction amount is zero. First step is to link the RM Open File to the RM_Statements_TRX_Temp, us...

RE: Multiple 2 cell into another
How do I Multiple 2 cells on the same row into a third cell in another cell? Not side by side. Example: I have the number 3 in cell E. I need to Multiple it with the amount in Cell F (which is $) and put total in side H. Example (E) 3 x (F)$18.00 = (H) $54.00 Thank You Mark =E1*F1 -- Regards, Peo Sjoblom "Mark" <drdinoz@hotmail.com> wrote in message news:30D19D44-080A-4442-AFDC-0FEB7918432C@microsoft.com... > How do I Multiple 2 cells on the same row into a third cell in another cell? Not side by side. Example: I have the number 3 in cell E. I need to Multip...

Raw Data Placement
Does anyone know where I can learn more about charting data? One of my problems is: I never know where to place the raw data (in columns or rows) in order to chart the data the way I want it. Thank you all Jon Peltier has a Charting tutorial that may help you: http://www.peltiertech.com/Excel/ChartsHowTo/index.html Colleen wrote: > Does anyone know where I can learn more about charting > data? One of my problems is: I never know where to place > the raw data (in columns or rows) in order to chart the > data the way I want it. > > Thank you all -- Debra Da...

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Update local table with Sybase ODBC data in update query
I need a couple of lines of code here. I have a linked Access97 table tblAccess, and a linked Sybase table tblSybase, in my Access97 db. I use a key field to link them. When I run the update query on these 2 tables to update a field in tblAccess, I get a popup to log into Sybase, which must be remedied in order to automate the process. I tried inserting a connection string: ODBC;DSN=[DSN name];UID=[login];PWD=[pwd];DATABASE=[dbase];, in the appropriate query property, but then it can't find tblAccess. I am running this query from VBA, so how do I get this thing working? Thanks, Bill R...

Attempting to call a sub from another wb using a Userform option b
Please assist. I have an userform that has several options button each calls a sub proc from another workbook I am getting run-time error 1004 Method Run of object_Application failed when I click on the button when the other workbook close . When the workbook is open I am getting run-time error 1004 Cannot run the macro wbMain.xlsm!cmdOpenfile. The macro may not be available in this workbook or all macros may be disabled.but with a different reason. My macros options are enabled and has trusted access. I am baffled because the code is very simple: Application.run "...

Quick Links on Home Page
If you set up a couple of custom quick links on your home page, is it being stored in a central location so that if you log in from a different computer (or in our case get connected to one of several GP app servers in our Citrix farm), you get the same home page? I am busy testing GP 2010 so this is new to me (we are in v9 currently). Also, is it possible to “copy” a home page to different users if you want to assign the same set of quick links to all of them? Thanks -- Pieter ...

Finding automatic links in a worksheet
Hi, When I open an Excel file that I'm helping someone with, I get the message "The workbook you opened contains automatic links to information in another workbook...." The author of this workbook has revised this file many times and would like to clean it up. Is there a fairly simple way to find the location of the outside references? Thanks much, Reney Langlois Hi try the following add-in to find/delete links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindLink.zip -- Regards Frank Kabel Frankfurt, Germany Reney Langlois wrote: > Hi, > When I open ...

Details in statement
One of my employees' turned something on in store operations to show details on every statement I print I need to turn it off how? ...

Excel 98 Question
Not sure if this is an issue on our LAN or if this is an Excel 98 bug - I have got some spreadsheets that after I update the source data for charts (on manual calculation) the actual chart will not reflect the change. If I select the series and hit enter on the formula bar, the chart updates. Any suggestions? thanks -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11624 View this thread: http://www.excelforum.com/showthread.php?threadid=319019 I don't use Excel 98...