Pivot Tables producing lists of text with multiple criteria and total count

[Bob McG]

Hi - hope that one of you incredible Excel Ninjas can help.
Trying to produce a status report/analysis of Projects using Pivot Tables but getting frustrated with organising data, totals and unnecessary spaces. Customer is very particular about layout. I think the attached (simplified) spreadsheets are self-explanatory but happy to answer any questions. The Desired Outcome sheet is what I am looking for.
The problem needs to be solved using Formulas & Pivot Tables, not Macros or VBAs, as I need to pass the management of this on to staff who do not have access to these tools or the necessary skills.
A big thank you to anyone who can help.

 

[Nebu]

Hi:

Find the attached.

The table in your data tab will give you what you are looking for without using pivot tables.

Note: You also do not need column D to G (Data Organisation to feed Pivot sheet ) for the formulas to work.
Thanks

 

[Bob McG]

Hi:

Find the attached.

The table in your data tab will give you what you are looking for without using pivot tables.

Note: You also do not need column D to G (Data Organisation to feed Pivot sheet ) for the formulas to work.
Thanks

Nebu,

Thanks for this this, looks great. However it doesn't seem to work when I add new projects, which I need it to do automatically without the need to change the formulas. Regards

 

[Chihiro]

Your main issue is the blank rows that need to be inserted between each region (along with formatting). Without VBA, there really isn't a way to do what you are looking for dynamically. There will be some manual aspect always.

 

[Bob McG]

Your main issue is the blank rows that need to be inserted between each region (along with formatting). Without VBA, there really isn't a way to do what you are looking for dynamically. There will be some manual aspect always.

Chihiro,

That's what I suspected, but thanks for looking at it anyway.

 

[p45cal]

The problem needs to be solved using Formulas & Pivot Tables, not Macros or VBAs, as I need to pass the management of this on to staff who do not have access to these tools or the necessary skills.

Well, as you've found, doing this with formulae or pivots is difficult; I certainly wouldn't want to do it with formulae - that would be very difficult to maintain bearing in mind there are different numbers of regions and different numbers of projects, AND merged cells. Being able to do it with Pivots would have been good since pivots are very good at displaying data, and they're very fast. However, it look verry difficult either way.

Macro/VBAs is the way to go. Macros come automatically with Excel, so are you saying people aren't allowed to use them? You need vba for user-defined functions. It would surprise me. If that's the case you're out of luck.
One major advantage of macros is that the people who use them do not have to maintain them - all they have to do is click a button or some such. They don't have to understand how they work.

That said, in the attached is a macro, you can start it by clicking the button on the Data sheet. It asks the user to select the source data range, then adds a fresh sheet and places a new table there. Once done, the user can copy/cut/paste the table where he wants, it's just plain data with a little formatting, no formulae.

The table below your table on the Desired Outcome sheet was created by the macro. It's very close to yours, except for some bold text I haven't bothered to correct.