create summary dashboard from multiple worksheets

[smulder1]

Hi - I've been playing around with using Excel for reporting and I want to create a dashboard for these reports. So far, I've created it by entering each cell with "='sheet name'!C1" etc, which is fine in principle, but as the number of project reports will vary month to month, and the sheet names could change, I feel like this could be somewhat unreliable and outdated pretty quickly.

Ultimately, what I want is pretty straightforward - I have a sheet named "Dashboard". On that sheet I want to start in cell A3 and populate my table.

In Column A I want to reference cell D5 on all the worksheets.
In column B, D1
In column C, D3
In column D, P3
In column E, D9
In column F, L9

That's the same on all sheets. However, there's a conditional formula applied in column G. At present, that formula is:
=IF(E4="R",'BES 12'!$D$21&"
"&'BES 12'!$O$21,'BES 12'!$D$11)

The idea here is that the cell populates with one reference if a project is flagged as red ("R") but another reference if it's flagged as either Amber ("A") or Green ("G"). This alpha value is, in itself, pulled across from other sheets and is in column E of the Dashboard.

As such, I'm a bit stuck on how to futureproof this, and make it so we can generate a dashboard based on a varying number of worksheets, all with different and non-sequential names (so the INDIRECT function won't work very well).

Can anyone help with this please?

 

[smulder1]

=IF(E4="R",'BES 12'!$D$21&"
"&'BES 12'!$O$21,'BES 12'!$D$11)

Just realised that this isn't the best example - 'BES 12' is obviously one of the sheets, but most of the sheets are project names, so there is no standard for this.

 

[oldbrewer]

in a macro sheets(1) will always refer to the first sheet regardless of the name of the sheet

if the code state is say 1,2,3 then using offset match based on code state will return the desired cell

think about this then come back with more specific help request

 

[smulder1]

Not sure I understand? Thought the request was pretty specific. I have lots of sheets in a standard template, and I need to import all of the data in specific cells into a list in one cover sheet. I've seen loads of similar requests before, so I'm not sure where I need to be more specific?

 

[oldbrewer]

I meant if you grab the first sheet, copy the cell to master sheet, grab the second sheet and so on, you can have an error trap for when ALL sheets have been grabbed.

so you want D5 on every sheet to be listed down in col A, D1 in col B and so on.

If somebody else can help with counting all the sheets in the workbook and using that total in the macro there would be no need for the error trap