Use find/Replace to change all formulas in new worksheet not working

[TMLstan]

I have a sales report that posts weekly sales data for ten stores - 225 rows with ten columns. One worksheet for each week - week 1, week 2, week 3, week 4. At the end of each month, a macro is run to clear this data, add it to Period To Date and Year To Date totals, then new data is posted each week by the individual stores.

I am trying to add four new worksheets, one additional sheet for each week which plots that week's data in several charts for better analysis. There will be a new worksheet for the charts for each week - week 1 charts, week 2 charts...etc. in addition to the original four worksheets. Data used for the charts is obtained by referencing the sales data in the worksheet for that respective week.

I have created the first sheet of charts for week 1. The data used for the charts in 'week 1 charts' sheet references the sales data in the 'week 1' sheet.

Now, to create the charts for the week 2, I want to just copy and paste the data from the 'week 1 charts' sheet into the new 'week 2 charts' sheet and then use Find and Replace to change the references in all of the formulas from 'Week 1!' to 'Week 2!'. When I do this, I get the Update Values window instead of the changes. This will require something like 600 manual steps for me to complete for each of the new chart sheets. But, I also don't want to have to manually build each of the subsequent chart sheets to reference the appropriate week of sales data.

There has to be a way to do this in one operation. I have tried what @reidlej previously posted in April, 2011 which is to find/replace all = with ##, find/replace all Week 1 with Week 2 and then find/replace all ## to =. It produces the same Update Values window. I have also de-selected "Ask to update automatic links" in the Advanced Options in Excel as was suggested in another thread.

Any suggestions on how I can cut/paste formulas from one chart worksheet to another then update all week 1 references to week 2 without manual intervention or re-building each worksheet for weeks 2, 3 and 4?

 

[6StringJazzer]

The data used for the charts in 'week 1 charts' sheet references the sales data in the 'week 1' sheet.

I'm unclear on where these references are located. Are these:

Formulas in cells?
In the Series formula for the chart series?
In named ranges?

How many charts do you have?

I have a macro to do Find & Replace in chart series definitions, but I don't want to confuse the matter until I know exactly what you need.

 

[TMLstan]

Jeff, thanks for your prompt response.

The references are formulas in cells.

Data is entered into the Week 1, Week 2...Week 4 Sales worksheets.
- there are ten stores/columns
- there are 37 sales entries (Gross Sales, LY Gross Sales, Y-Y, Online Sales, LY Online Sales, % Gross Sales, Y-Y, Delivery Sales, LY Delivery Sales,....COGS, Labor, Operating Profit, etc) for current week, the same 37 categories for PTD and, once again, the same 37 for YTD for a total of 254 Rows (including some spacing and title rows).
- there is one such worksheet for Week 1, another for Week 2, etc.

I am adding four new worksheets, one in-between each of the above referenced weekly worksheets - Week 1 Charts, Week 2 Charts, etc.
To build the data set for the charts, I am simply "referencing" most of the data in the sales worksheets into the new chart worksheets using formulas ='Wk 1!E6'
I did this manually to build the Week 1 Charts worksheet then built the charts - there are 20 charts
I was hoping to just copy the reference formulas in the Week 1 Chart worksheet into the Week 2 Chart Worksheet and then just to a find/replace to change every reference formula from 'Wk1!E6' (or whatever cell) to 'Wk 2!E6'. There are around 600 such references that need to be changed for each new worksheet so this could be done in one find/replace vs. manually building each one of these 600 references to their week 2 data sheet, then again for week 3 and again for week 4.

This seemed to work the first time I tried it. But now, every time I do it, it returns an Update Values window for each of the 600 references. The Updates Value window wants me to select a file for each entry.

I hope this clarifies it a little bit. Sorry for the long response. I am probably not using the correct terminology for the references and functions which makes it more difficult for you.

 

[6StringJazzer]

The Updates Value window wants me to select a file for each entry.

This suggests that there is no worksheet in your file with the name you are trying to change it to, so it is asking for another file where it might be.

I am having trouble diagnosing this any further without the file. Do you have a way to share it? (Google Drive, Dropbox, OneDrive, etc.)

 

[TMLstan]

Yes, of course. Tell me how to send you a Dropbox invite and I will.

 

[6StringJazzer]

You can just post a link here, marking the Share as Can View.