Alter all formulas based on cell value

Hello,

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.

Re: Alter all formulas based on cell value

MrRedli,

Thank you for the response. Reading up on the Indirect function, I'm not sure it's going to give me what I'm looking for. If I'm not mistaken (which I may be) I would have to go and alter every one of my existing formulas to include the indirect function. If this is the case then I really would like to avoid that as there are hundreds of formulas referencing over 100 employee names, so theres no way to quickly alter on a mass level.

Here's an example of the formula.

=SUMIFS('Jan'!$E:$E,'Jan'!$B:$B,"*Employee's Name*", 'Jan'!$D:$D,"<30000000")

I'm hoping that there's a way to use the Replace function to change the 'Jan' in the formulas based on what's selected in the Data Validation drop down list. If I can't use a drop down list then I would be open to having the user input the name of the month manually. Doing it this way opens the door to errors if the month isn't typed in correctly.

If this can be done via the Indirect function, I'd appreciate some guidance on how to make it work.

Re: Alter all formulas based on cell value

I'm hoping somebody has some ideas about this.

If the Indirect function can work in this situation, I'd appreciate some guidance as I'm not sure how to apply it to perform the task I require.