Test what formula is in a cell and change it Excel 2007

[Russ33]

Good Evening,

I have a workbook with reports broken out into different tabs. Each tab is a different item with weekly sales data as well as lines for last 52wks, 13wks, 4wks, and FYTD. I have a summary page that lists all the items down the left hand side of the page ie.(A1) and the data extending out from each item ie.(B1:N1). The data for each item on the summary page is linked to the column in that items corresponding tab.

I have buttons at the top of the summary page that will allow the user to change the data to 52wks, 13wks, 4wks, and FYTD. When the user clicks a button it will change what data is shown for all items on the summary page to what they have selected.

I have tried recording macros that will change the reference formula with no luck so I guess the next thing to do it write a script that will test what formula is being referenced in the summary page and change it to show what the user selected.

All the formulas in the summary page are exactly the same except for the name of the tab it is referencing. So, what I would like to happen is when the button is clicked it searches the page for what formula (or cells being referenced in the formula) are being used and change it what they need to be. I am guessing some sort of IF statement or switch is to be used? Any help on getting started would be grand. Or a shell of some code to do the find and replace. Thanks.


EDIT: I need to state in what rows on the individual tabs the data I need resides.
52wks = row 80
13wks = row 81
4wks = row 82
FYTD = row 83

The formula in each cell on the summary page would read something like: =itema!$A$80 if the data being shown is from the 52wks row or =itema!$A$81 if the data being shown is from the 13wks row and so on. The VBA script just needs to change the last two characters in the formula.

[Bob Phillips]

Can you post an example workbook so that we can picture it?

[Russ33]

Ok, here is an example workbook. You will notice the buttons I am talking about at the top of the Summary page. I can record a macro that works great when I am only working with two buttons (it's just a copy and replace macro) but when I have more than than two I can't get anything to work. Any help or guidance would be great.

I saved it as a macro enables workbook. The buttons are not active.

[Russ33]

Anyone? At least some guidance on what I would need to do to accomplish this?

[Russ33]

Surely someone here has some insight or advice.