VBA code: Change cell reference in cell formulas based on a single cell or drop down menu?

Hi Everyone,

I was wondering if it is possible to have a vba code that can change a cell reference either based on a single cell value or a drop down menu?

For Example:
In a workbook, I have 8 worksheets. First 7 are MON, TUE, WED, THU, FRI, SAT, and, SUN. The eighth worksheet, a range of cells contain formulas that reference to MON. I want to change MON to the other worksheets and change from that worksheet to another worksheet, and so on.

Is this possible? Please let me know, I have been google search couldn't find anything. I also apologize for not having a workable workbook.

Thank you!

Thank you NeedforExcel!

Attached are the excel files. In master.xlsx, A1:A20 are the formula I want to change from MON to other worksheets.

Thank you

Thanks for your solution but I applied this to my original workbook and I notice that my workbook won't update unless I have the other workbook opened. Is it still possible to use indirect while being able to have the other workbook closed and still update?

Thank you