V,

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

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