Insert New Worksheet

[Jonathan Lee]

Hi,

I have worksheet 1 and worksheet 2. Worksheet 2 has formulas referencing to worksheet 1.

When I make a copy of worksheet 2 and re-name to worksheet 3, in the same workbook, the formulas are still referencing to worksheet 1.

Manually changing the formulas in worksheet 3 is tedious and I also do not want to use the find and replace. Cause it might change fields I don't want to change.

Is there a way to have the new worksheet referencing to worksheet 2 automatically？ And when I make a copy of worksheet 3, the referencing changes to worksheet 3?

Thanks in advance.

 

[Norie]

Jonathan

I don't know of any way to do that automatically, other than using some sort of find/replace.

Why don't you want to use find/replace?

What 'fields' don't you want to change?

 

[Jonathan Lee]

Thanks Norie for your prompt reply.

Unfortunately my users are not excel users. The find and replace maybe too complicated for them. Need something simpler...

 

[ClaireS]

Try using the INDIRECT function to look at the sheet before the current one. Find a cell in all sheets (say $A$1) that you can reference to give you the sheet number of this sheet, then use something like this to reference the previous sheet:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDIRECT("Sheet"&SHEET($A$1)-1&"!Z99")[/FONT]

My formula isn't doing anything except referencing Z99 in the previous sheet. You would need to adapt this for all cell references to the previous worksheet, so it would take a lot of setting up, but should be OK once you have. Or use Replace: if you select the column(s) with the formulas you want to change, it will limit the changes to the selected cells.

Good luck!

 

[Jonathan Lee]

Thanks Claries.

I expanded the formula as follows:

=INDIRECT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1),1)+1,1024)-1&"!n19")