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 runs 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.
Recently, I created charts which reflect the sales data in the worksheets in different views for better analysis. There will be a different new worksheet for the charts for each week - week 1 charts, week 2 charts...etc. Data for the charts is created by referencing the sales data in the respective week.
I have only created the first sheet of charts. The data used for the charts in 'week 1 charts' sheet references the sales data in the 'week 1' sheet. To create the charts for the week 2 charts, I copied and pasted the data from the 'week 1 charts' sheet into the 'week 2 charts' sheet. When I use Find and Replace to change the references in all of the formulas from 'Week 1!' to Week 2, I get the Update Values window. This will require something like 600 manual steps for me to complete for each of the new chart sheets. 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.
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?
Recently, I created charts which reflect the sales data in the worksheets in different views for better analysis. There will be a different new worksheet for the charts for each week - week 1 charts, week 2 charts...etc. Data for the charts is created by referencing the sales data in the respective week.
I have only created the first sheet of charts. The data used for the charts in 'week 1 charts' sheet references the sales data in the 'week 1' sheet. To create the charts for the week 2 charts, I copied and pasted the data from the 'week 1 charts' sheet into the 'week 2 charts' sheet. When I use Find and Replace to change the references in all of the formulas from 'Week 1!' to Week 2, I get the Update Values window. This will require something like 600 manual steps for me to complete for each of the new chart sheets. 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.
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?
