I have a workbook (we are using them for electronic purchase orders) and I need to be able to copy a worksheet but have the formula in it increment like it would if I copied the formula in a sheet.
In other words in sheet2 I1 the formula is ='Sheet1'!$I$1+1. When I copy sheet2 and it becomes sheet3, I need the formula in I1 to say ='Sheet2'!$I$1+1.
How can i do this?
You can get a string containing the current sheet name with the following formula
=cell("filename",A1)
If you are using the standard Sheet# for your pages, then you can get the current sheet number with this formula (assuming the previous one was in B3)
=RIGHT(B3,LEN(B3) - FIND("]Sheet",B3,1)-5)
Next calculate the number for the previous sheet
=VALUE(B4)+1
Now you can use the indirect function to address a cell in the previous sheet
=INDIRECT("Sheet"&B5&"!B1")
In Sheet4, this will reference B1 in Sheet3. Copy it to Sheet5 and it will reference B1 in Sheet 4.
