Need to copy formula sheet w/o reference to other file

[inspekta76]

I've searched here, there everywhere and tried 50 different ways to do this with absolutely no luck!!!!!!!!!!!!!!
I have a monthly file with a worksheet for each day and at the end there is a summary worksheet-with formulas cumulating the values for the month. When I try to copy that worksheet to put at the end of the next months file all the formulas refer to the other file i.e. =SUM([SEPT2008TCCACOSTRECOVERYBILLINGSUBMISSION.xls]SEPT1:SEPT30!O28). The cells are the same in every month...the only difference is the name of the worksheets July1,July2...next month Aug1,Aug2.
I'm using simple formulas =SUM(SEPT1:SEPT30!G28)I know theres got to be an easier way then retyping every formula in every cell for my month end summary.
Any help would be appreciated-I'm rackin my brain.

 

[GlennUK]

Change the formula to text using Edit/Replace = to # /Replace All. Then copy the formulas, then Edit/Replace on the copied block to change SEPT to AUG / Replace All, then do Edit/Replace # to = /Replace All, to convert text entries to formulas again.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[bluedragon2]

First of all, I would have one sheet with ALL of your data. Then if you need daily and summary sheets, set them up to pull data from that ONE sheet. You would then be able to copy workbooks as needed without changing formulas.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

I second Blue's comments.

I know it seems like a big pain to reformat the whole thing, but trust us, it will be much, much, much easier - not to mention faster - in the long run.

Put your data in a normalized table and include a column for Date. Reporting will be a breeze because Excel's many analysis & reporting tools are set up to work on properly-stored data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

Agree with Blue and John. However in the interim you can change all references to the previous months workbook using Edit, Links, Change Source and pointing to the current month file.

Gavin

 

[GSCaupling]

hmm, switch the whole thing to Access, perhaps?