Cell Data in Formulas/functions

  • Thread starter Thread starter The Boondock Saint
  • Start date Start date
T

The Boondock Saint

Does anyone know if you can have use cell data in a formula or function.

Basically... If a cell = 5... i need another function to look at data in
A5... however... if the cell = 6.. i need it to look up A6....

any ideas...?
 
Basically... If a cell = 5... i need another function to look at data in
A5... however... if the cell = 6.. i need it to look up A6....
Click to expand...

You can use the INDIRECT function, which takes the address of a range (as a
text string) as an argument, and returns the contents of that range.
Supposing the cell with the number in is B7 you would do:

=INDIRECT("A"&B7)

The '&' is used here to concatenate two strings. Also note that only the
'A' has quotation marks around it.

Stewart
 
Cool, that sounds good... How could i put it into this function

=IF(Sheet1!A5>0,"Dead Head","Standard Placings")

Basically the A5 is the part of the equation which im trying to get
dependant on the cell...
 
... How could i put it into this function
=IF(Sheet1!A5>0,"Dead Head","Standard Placings")

Basically the A5 is the part of the equation which im trying to get
dependant on the cell...
Click to expand...

Replace the A5 cell name in the above formula with the INDIRECT function:
=IF(INDIRECT("Sheet1!A"&B7)>0,"Dead Head","Standard Placings")

A word of warning: the INDIRECT function is volatile, which means it will
recalculate every time _anything_ changes on your spreadsheet. This can
become very time-consuming on a large sheet.

Stewart
 
I presume that there is one lookup cell where you will change a value,
and all other cells remain the same.
so if that cell was located B1, and the range you look up within is
column A then...

=IF(INDIRECT("A"&$B$1)>0,"Dead Head","Standard Placings")
 
S. I. Becker wrote...
....
Replace the A5 cell name in the above formula with the INDIRECT function:
=IF(INDIRECT("Sheet1!A"&B7)>0,"Dead Head","Standard Placings")

A word of warning: the INDIRECT function is volatile, which means it will
recalculate every time _anything_ changes on your spreadsheet. This can
become very time-consuming on a large sheet.
Click to expand...

So use INDEX instead.

=IF(INDEX(Sheet1!$A:$A,B7)>0,"Dead Head","Standard Placings")
 
Back
Top