Repetative references to Sheets

Copper Contributor

HI All

 

I am setting up a spreadsheet, where i have separate sheets for each Month and a summary sheet, where I want to pull in specific data from each of the month sheets. 

 

I want specific cells in the summery sheet to reference specficic cells in the 'month' sheets. 

 

For each month in the summery sheet, i want to reference about 10 different cells in the month sheets. 
At the moment i am doing this with the simple formula ='24 April'!D2. etc. 

 

However, every time i add a month sheet, and therefore row in the summery sheet, i have to ammend the reference in each of the 10 cells. 

 

Is there a way that i can make this more efficient, i.e. only changing the '24 April' reference in one place rather than 10?

 

I hope that makes sense, any help would be greatly appreciated. 

 

 

 

1 Reply

@JamesKirkmanCroft 

 

Maybe this is not a world-changing solution but the INDIRECT function may help you.

 

You can set up an auxiliary range in your summary sheet with the names of all months. Then, use, for instance:

 

=INDIRECT(B1&"!"&"D2")

 

Where B1 would be a cell in your summary sheet containing "24 April". In that way, you can keep adding months and then dragging the formulas accordingly so that the only manual action would be to drag the existing formulas...