A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

If statement linking data from one sheet to another



 
 
Thread Tools Display Modes
  #1  
Old May 27th 10, 10:07 PM posted to microsoft.public.excel.worksheet.functions
mathew
external usenet poster
 
Posts: 75
Default 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!


Ads
  #2  
Old May 27th 10, 10:41 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 8,521
Default If statement linking data from one sheet to another

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!
>
>

  #3  
Old May 27th 10, 10:48 PM posted to microsoft.public.excel.worksheet.functions
Reg
external usenet poster
 
Posts: 48
Default If statement linking data from one sheet to another

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,A1,1),"")

this looks up (index function) the content of an array ('Agency Reallocation
data'!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!
>
>

  #4  
Old May 27th 10, 10:49 PM posted to microsoft.public.excel.worksheet.functions
mathew
external usenet poster
 
Posts: 75
Default If statement linking data from one sheet to another

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,A1,1),"")
>
> this looks up (index function) the content of an array ('Agency Reallocation
> data'!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!
> >
> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
If statement linking data from one sheet to another Mathew Excel Worksheet Functions 1 May 27th 10 09:44 PM
linking data between sheet SPL Excel Discussion (Misc queries) 0 February 11th 09 05:51 PM
Linking data from a new sheet (semi-)automatically Alison KS Excel Discussion (Misc queries) 1 March 21st 07 12:26 PM
If data is on one sheet, linking to another nevergiveup New Users to Excel 2 June 27th 06 09:33 PM
Linking Data from 1 sheet to another sheet Warwick Everett Excel Worksheet Functions 5 December 21st 05 02:49 PM


All times are GMT +1. The time now is 04:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.