Cell Data in Formulas/functions

[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...?

[S. I. Becker]

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

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

[The Boondock Saint]

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


"S. I. Becker" <stewart@becker.nospam> wrote in message
news:u$8IS6NbFHA.2984@TK2MSFTNGP15.phx.gbl...
> > 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....
>
> 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
>
>
>

[S. I. Becker]

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

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

[stevie.holmes@gmail.com]

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")

[Harlan Grove]

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.

So use INDEX instead.

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