Changing sheet lookup reference when copy/pasting?

[AJY]

Hi,

I have what should be a very simple excel problem but i cannot seem to get my head around it!

I have one worksheet in which the formula in each colum looks up a different sheet.

So column A, as part of the formula, looks up sheet1, colum b should then use the exact same formula but look up sheet2 and so on.

The problem i have is that once ive created the formula in colum A (formula is quite long SUMPRODUCT function) i cannot simply copy and paste it to all other colums? Where it is looking up a cell reference its easy to change it from static to dynamic etc, but is there a way to do this with the sheet reference?

It would not sound like a massive amount of work to simply overtype the reference in cell "B2" with the correct sheet ref and then copy/paste down the rows, however in reality there are breaks in the sheet and also 14 sheets all of which use a similar set up (with just one change to the formula in each).

Does this make sense, and is there an easy answer!

Thanks

[NBVC]

To change a sheet name as you copy a formula, you will need to employ the INDIRECT function.

Check here for some guidance: http://www.contextures.com/xlFunctions05.html#RefSheet