Change Cell reference sheet name

[atuljadhavnetafim]

Dear expert,

i have three sheets "Case1", "Case2" and "Case3" which has same formate and data as below, where Jan to Jun numbers are same in all three sheets but Jul to Dec numbers are differente in all the sheets., all three sheets Jan to June are Actual numbers and Jul to Dec are forecast numbers but in all three sheets Forecast numbers are different.

Now i have "Summary" sheet which also has same format and has Jan to June Actual numbers but Jul to Dec numbers comming from "Case1" sheets,
now what i want, i want to change the link in Summary sheet from "Case1" to "Case2" Or from "Case3"., and this changes will be frequent.

So, is there any way by clicking or by filtering somthing the cell reference will change automatically, i can do it manually by find and replace but since i have 40+ sheets and everytime it is not possible to change manually.

Thanks in advance.

 

[Saurabhj]

Hi Atul,

Please check if below solution works:

1. Create a list of sheet name using Data Validation in sheet "Summary". (Created in Cell I1 as below screenshot)

2. In Summary Sheet, for showing data of selected sheet name in drop down, use below formula: (In Screenshot, formula is used in cell D4)

=INDIRECT($I$1 & "!" & ADDRESS(ROW(),COLUMN()))

3. Drag the formula for all required cells. (from D4 to O6).

4. Now change the sheet name in drop down.

5. The data will change automatically.