Creating a Summary Sheet populating from other worksheets

[FergusC]

Hi everyone, and thanks in advance for all of your help! I use this forum all of the time but this is my first post (as I can usually find the answer) so please bare with me...

I am trying to do something that I thought would be fairly simple, but it is turning out not to be. In essence I have a number of identical sheets, one for each project, and I would like a summary sheet, or master sheet, that consolidates all of the information from the other sheets.

Where is gets slightly more complicated is that I would like the summary sheet to populate with the sheet names of the various projects. For example, I have a table of 5 columns and 5 rows on each sheet, some cells are blank and some contain an 'x' or some other arbitrary value. The master sheet contains the same table but each cell populates with the name of the sheet where an 'x' appears in that cell.

So far I have this;

{=INDEX(Sheet_List,MATCH("x",INDIRECT("'"&Sheet_List&"'!"&CELL("address",C3)),0),1)}

I have a 'helper' sheet with a list of worksheet names (Sheet_List" and I am using a CELL reference within the INDIRECT to avoid a forced absolute cell reference so that I can copy this formula across the other cells in the summary sheet. In the final application there are hundreds of cells so I need this formula to be dynamic to avoid editing each one. The above formula works, but only seems to search the first sheet, not all. I think this issue is created by the INDIRECT resolving the Sheet_List named range but I'm not sure how else to achieve what I want...

The final application for this is a sheet with a series of tasks down the left hand side and week numbers along the top. A cross is placed in the week number column when each task is to be completed. The summary sheet then shows a full 'calendar' showing when each task is to be completed for each project. I started with a simple IF statement, checking for a 'x' in the first sheet, and then the next which worked fine until we started introducing a task being carried out on a specific week for multiple projects, hence the above modification.

Apologies for the lengthy and confusing post, I hope it makes sense! I have attached an example book.

 

[David Evans]

Have you considered laying out your data in a single table with Project Number, Row Header, Column Header, Value and I believe you can then Pivot off that dataset to produce your report.

 

[FergusC]

David,

Thanks for your response. I don't really follow though... Are you suggesting a single table for all projects?

I would like to have a sheet for each project that can be populated individually with the summary sheet updating as this is done. Additional projects may be added at a later stage.

I'd really appreciate if you could perhaps upload and example based on the data in my sample sheet. I think I'm misunderstanding what you're suggesting...

Thanks in advance!

 

[Mike86]

It's kind of clunky, but something like this gets you close:

=IF('P1'!C3<>""," P1","")&IF('P2'!C3<>""," P2","")&IF('P3'!C3<>""," P3","")

Copy equation through the table. It doesn't know to put the "," in, so just does an extra space

 

[Nebu]

Hi:

The attached macro will do what you are looking for.

Thanks

 

[FergusC]

Hi both, thanks for the replies!

Nebu, your macro does exactly what I'm after, the only issue being that because it uses VBA the process of adding an additional sheet becomes complex and I would have to do it myself. I would like to achieve a result where I can show someone how to add an additional sheet and modify the formulas on the summary sheet with relative ease...

Mike your response is almost exactly what I am after! I didn't realise you could link IF statements in that way, learning every day... Does anyone know how to take Mike's formula and separate the outputted values if multiple values exist? Currently Cell C3 reads "P1P2", any ideas how to make it read "P1, P2"? Preferably without making it read ", P2" if the first sheet returns false.

Thanks so much for all your help guys!

 

[Mike86]

Just add a comma inside the " P#" area for everything after P1. ex: ", P#" will put a comma in. It just looks odd if the first hit isn't P1.

You could also build an if statement check inside each if to see if there's an earlier hit. This will start getting complicated quickly and rapidly makes the statement huge.