refer to multiple excel sheets

0
votes

My Excel-Workbook contains a number of sheets which each contain simulation data. The first sheet however, will be used to aggregate averages over all simulations. Each sheet contains the data of a single simulation.

Now what I would like to do is to have a single reference in which I can enter the names of all simulation sheets and then use this reference in all other charts / tables to perform things like calculating averages over all the sheets.

What I got so far is this:

=AVERAGE('Simulation 1:Simulation 2:Simulation 3'!C2:C8)

However, I have many of these cells and I frequently add new simulation sheets. Therefore I don't want to maintain every single cell like this. Instead I would like to have a single cell in which I maintain the sheets that are used. I tried this:

'Simulation 1:Simulation 2:Simulation 3'

This cell has the text "'Simulation 1:Simulation 2:Simulation 3'" as plain text. I created a name called "EnabledSheets" on the cell and altered the other cells to:

=AVERAGE(EnabledSheets!D2:D8)

This does not work however. Does anyone know a way to make this work? I really have many sheets and their number changes very frequently...

Thank you!

1
excelvba

1 Answers

4
votes

I would create two bracketting sheets.....alpha and omega and place all your actual data sheets between these.

Then a formula like:

=AVERAGE(alpha:omega!A1)

would require no adjustment as data sheets are added or removed.