Duplicating Formulas in Workbook

kls · May 17th, 2002, 02:19 PM

I have a workbook that has a Summary Sheet with 10 sheets (Sheet1 thru Sheet10). Each Sheet has the formula: =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) in cell A1 which gives me the Sheet Tab name. The formula works as I add the formula to each Sheet; only when I go back thru the sheets the cell with the formula now has the last sheet name I added. The Summary sheet has the formula =Sheet1!A1. The reason for this is that the user's will be changing the Sheet Tab names and the Summary sheet will show each tab name.

Your help is greatly appreciated!

Mudface · May 17th, 2002, 02:27 PM

That's an odd one

. Got the same result, pressing F9 on each sheet restored the original sheetname, if that's of any help. If not, and there's no better solutions, a bit of code in the Worksheet_Activate to force a recalculation might work OK for you.

PHMayfield · May 17th, 2002, 03:26 PM

I think Cell("filename",a1) would fix this. For some reason it "anchors" the formula to the sheet the formula is on (otherwise it always calculates off of the active sheet at the time you entered or recalculated it). The a1 is arbitrary, but some cell needs to be there for the formula to "stick."

[ This Message was edited by: PHMayfield on 2002-05-17 14:27 ]

kls · May 17th, 2002, 03:32 PM

Yes!!! Adding the A1 reference worked. Thanks!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 17th, 2002, 02:19 PM	#1
kls Board Regular Join Date: May 2002 Posts: 69	I have a workbook that has a Summary Sheet with 10 sheets (Sheet1 thru Sheet10). Each Sheet has the formula: =MID(CELL("filename"),SEARCH("]",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1) in cell A1 which gives me the Sheet Tab name. The formula works as I add the formula to each Sheet; only when I go back thru the sheets the cell with the formula now has the last sheet name I added. The Summary sheet has the formula =Sheet1!A1. The reason for this is that the user's will be changing the Sheet Tab names and the Summary sheet will show each tab name. Your help is greatly appreciated!

May 17th, 2002, 02:27 PM	#2
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	That's an odd one . Got the same result, pressing F9 on each sheet restored the original sheetname, if that's of any help. If not, and there's no better solutions, a bit of code in the Worksheet_Activate to force a recalculation might work OK for you.

May 17th, 2002, 03:26 PM	#3
PHMayfield New Member Join Date: Apr 2002 Location: Phoenix, AZ, USA Posts: 29	I think Cell("filename",a1) would fix this. For some reason it "anchors" the formula to the sheet the formula is on (otherwise it always calculates off of the active sheet at the time you entered or recalculated it). The a1 is arbitrary, but some cell needs to be there for the formula to "stick." [ This Message was edited by: PHMayfield on 2002-05-17 14:27 ]

May 17th, 2002, 03:32 PM	#4
kls Board Regular Join Date: May 2002 Posts: 69	Yes!!! Adding the A1 reference worked. Thanks!