Problem dragging formulas in a Spreadsheet

[Peter42]

Hi,

I have a spreadsheet, which has lots of tabs and then a summary tab will collates information from each seperate sheet.

In one column is a formula ='co450'!k22

I have created some new tabs and have tried to drag this formula down, with the aim that the co450 number will count on i.e. co451, co452 etc. When i try and drag the formula to co part does not change, but the K22 increases. I have fixed this by making the K part an absolute formula, but still the first part will not change. I have tried copy pasted etc. as well

Any help greatly appreciated.

Thanks

Peter

[teylyn]

Hello,

The sheet name will not change if a formula is dragged, only the cell reference.

If you need the sheet name to behave in a similar way, then use INDIRECT()

=INDIRECT("co"&ROW(A450)&"!k22")

This will return the same as =co450!k22 and when you drag the indirect formula down, the number at the end of the sheet name will be incremented by 1, as if you had entered

=co451!k22
=co452!k22

and so on.

cheers,

[TMS]

You'd need to use INDIRECT

If, in cell A2, you put: "co450", no quotes and in cell B2, you put the formula: =INDIRECT("'"&A2&"'!K22")

You can drag down and it will increment the sheet name/number and keep the cell address static.


Regards, TMS