Sheet Value in Cell? Is this possible

[velocityinc]

I have the following scenario

Cell C4 has the text "apple" in it and C4 is on sheet ALPHA

In Cell C18 on sheet ALPHA, the cell references a separate sheet
"apple" and cell c176.

How can I reference c18 such that i dont have to manually link it to
sheet apple because I would like to drag the cell across for other
such scenarios and would like to keep the reference dynamic and linked
to all sheets in the workbook and not just a specific worksheet.

Please kindly advise on the values to enter into C18 that I could then
drag across for values in D4, E4, and so on.

Thanks a bunch.
V

 

[Guest]

If I understand correctly, I think this might work for you:

=INDIRECT("'"&C4&"'!C176")

HTH,
Elkar

 

[Bernie Deitrick]

V,

=INDIRECT("'" & C4 & "'!C176")

or if C176 needs to also increment by column:

=INDIRECT("'" & C4 & "'!" & ADDRESS(176,COLUMN()))

HTH,
Bernie
MS Excel MVP

 

[velocityinc]

V,

=INDIRECT("'" & C4 & "'!C176")

or if C176 needs to also increment by column:

=INDIRECT("'" & C4 & "'!" & ADDRESS(176,COLUMN()))

HTH,
Bernie
MS Excel MVP











- Show quoted text -

Is there a way to create a link to another cell with numbers on
separate rows like 12, 13, 14 and drag this formula down =INDIRECT("'"
& $C$5 & "'!$C11") such that i dont have to manually update c11 to c12
to c13 and so on? please advise, thanks. v

 

[Bernie Deitrick]

=INDIRECT("'" & C4 & "'!C" & A4)
which would drag down to
=INDIRECT("'" & C5 & "'!C" & A5)

or

=INDIRECT("'" & C$4 & "'!C" & A4)
which would drag down to
=INDIRECT("'" & C$4 & "'!C" & A5)


HTH,
Bernie
MS Excel MVP