I want to compare last week's data to current data (sheets are named by date)

I have a file with a sheet for each day of the year and want to compare the current data to the prior week. I am trying to do this by using the sheet name so that when I copy and rename the week's sheets, the correct links will be preserved (at the moment, I am changing the sheet name manually). I have a formula that can get the sheet name and cell, using the current sheet name less 7, but I don't seem to be able to use that in a formula? e.g. = "'"&text(b2-7,"d-mmm-yy")&"!'D84". Or should I be using a vlookup with a variable sheet name somehow?

Try using the INDIRECT() function. INDIRECT("'"&text(b2-7,"d-mmm-yy")&"!'D84") might do the trick for you.

Originally Posted by outofthehat

Try using the INDIRECT() function. INDIRECT("'"&text(b2-7,"d-mmm-yy")&"!'D84") might do the trick for you.

I tried this but I got a #REF error?

It does work actually - I had the '! around the wrong way.... I ended up with =INDIRECT("'"&TEXT($B$2-7,"d-mmm-yy")&"'!"&CELL("address",B84))

Thanks.