how can I insert a sheet and the formula change depending on sheet name automatically

[Bobgiant]

I have data on reference sheet. And after I copy a sheet1, and rename it sheet2, I want the calculation change based on the reference sheet. Insdie the excel file, the yellow part is exactly what I need but I think it is too complicated. Maybe someone has better method?

[janmorris]

when you use sheet references they do not change when you duplicate a sheet. you use sheet references when you want it to be locked to the sheet, if you dont want it locked to the sheet then do NOT use the sheet reference.

so, for the given formula on Sheet1!F7:

Please Login or Register  to view this content.

if you want it to follow with the new sheet (Sheet2), then you need to remove the "Sheet1!" particle, like this:

Please Login or Register  to view this content.

then make the duplicate and the new sheet (Sheet2) will just use cell reference without the forced sheet reference.

[Bobgiant]

But for sheet2 I do not want reference!K6? I want Reference!K7

[ben8563]

But for sheet2 I do not want reference!K6? I want Reference!K7

Please Login or Register  to view this content.