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