Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 27th 10, 10:07 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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!



  #2   Report Post  
Old May 27th 10, 10:41 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default If statement linking data from one sheet to another

Try
=IF(I530,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   Report Post  
Old May 27th 10, 10:48 PM posted to microsoft.public.excel.worksheet.functions
Reg Reg is offline
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
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   Report Post  
Old May 27th 10, 10:49 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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!




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 08:05 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017