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

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

HTH,
Elkar


" wrote:

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