I'm trying to find a way to alter or replace a text word (Sheet Name) contained in formulas throughout an entire worksheet.
The workbook will contain multiple sheets. They are as follows; "Year to Date", "Period", "Jan", "Feb", "Mar" and so on for the rest of the year.
I want to use a Data Validation drop down list, on the "Period" sheet containing all of the calendar months.
I currently have multiple formulas within the "Period" sheet that reference one of the month sheets so that it can pull the data for that month from that sheet. Instead of having multiple "Period" sheets, I'd like to use a cell, lets say A1 for argument sake to use a Data Validation drop down list to pick which month I would like the "Period" sheet to reference.
If the current formulas on the "Period" sheet are set to reference the "Jan" sheet, I would like the formula or VBA to find all instances of "Jan" in the formulas and change them to the month selected in the A1 drop down list.
If "Feb" is chosen in A1, all instances of "Jan" in the formulas on that sheet would be replaced with "Feb" so all data on that sheet would reflect the period associated with February.
I hope that makes sense.
Thanks in advance.