If statement linking data from one sheet to another

  • Follow


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

5 Replies
1835 Views

(page loaded in 0.037 seconds)


Reply: