Modifying Old Formula to Reference a SheetList

[Rick-O-Shay]

Hi,

I have a pretty complex worksheet. I have the desired outcome of what I want by using this formula

Please Login or Register  to view this content.

but what I need to do is modify the formula to reference a SheetList because I cannot rename my tabs. I know that this formula does work (below) but it doesn't fully work because this formula refers to column A only. I guess I am not savvy enough to modify the below so that if this formula were copied up or over that it would refer to the appropriate columns just like the one above does.

Please Login or Register  to view this content.

Sorry, I hope I have provided enough information.

[XLent]

so, a few options, but if you want to just modify the previous iteration you should find you can replace the Sheet text parameter in the ADDRESS call

Please Login or Register  to view this content.

FWIW, in more general terms, INDIRECT does handle R1C1 notation, this can be useful when looking to create dynamic relative references (within a literal string) e.g.

Please Login or Register  to view this content.

so, you could equally adapt your 2nd approach on this basis, you would just need to ensure that your initial RC position is adjusted such that it aligns to first target cell.

R1C1 notation examples: https://www.excelforum.com/excel-pro...ml#post2171545