=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


wrote in message
oups.com...
On Mar 29, 12:11 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
V,

=INDIRECT("'" & C4 & "'!C" & A4)

or if C176 needs to also increment by column:

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

HTH,
Bernie
MS Excel MVP

wrote in message

oups.com...



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
sheetapplebecause 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- Hide quoted text -

- 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