Using a cell reference within a cell reference in a formula

[david]

I believe it is possible to use a cell reference as one component of a
formula but I can't remember how. What I want the overall formula to do
is pick up the letter component from another cell reference - can
anyone help please?

Thanks

David

 

[Franz Verga]

Nel post *david* ha scritto:

I believe it is possible to use a cell reference as one component of a
formula but I can't remember how. What I want the overall formula to
do is pick up the letter component from another cell reference - can
anyone help please?

Thanks

David

Hi David,

You can use the INDIRECT function.

Try to check the on line guide, then If you should need any help, post your
formula here.

I'll show you an example:

=SUM(INDIRECT(A1&"2:"&A1&"100"))

so if in A1 there is B the above formula is equivalent to:

=SUM(B2:B100)


Be aware that if you use the INDIRECT function in connection with another
file, for example:

=SUM(INDIRECT([WB2]Sheet1!A1&"2:"&[WB2]Sheet1!A1&"100"))

also the second file should be open to have a result; if not, you will have
a #REF! error.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[david]

Thank you - I'll try it

David

Nel post *david* ha scritto:

I believe it is possible to use a cell reference as one component of a
formula but I can't remember how. What I want the overall formula to
do is pick up the letter component from another cell reference - can
anyone help please?

Thanks

David

Hi David,

You can use the INDIRECT function.

Try to check the on line guide, then If you should need any help, post your
formula here.

I'll show you an example:

=SUM(INDIRECT(A1&"2:"&A1&"100"))

so if in A1 there is B the above formula is equivalent to:

=SUM(B2:B100)


Be aware that if you use the INDIRECT function in connection with another
file, for example:

=SUM(INDIRECT([WB2]Sheet1!A1&"2:"&[WB2]Sheet1!A1&"100"))

also the second file should be open to have a result; if not, you will have
a #REF! error.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy