Extracting results based on multiple criteria.

[Lost Paul]

This one is a bit difficult to explain, but here goes.

We've got 2 tables that need to calculate results against each other.
1 is OK, as the data is 2-dimensional.
The other however has more going on.

Attached example file shows both the structure & sample data, on 'Starting data' tab.
With what I'm looking to produce to calculate against.

The filtering / criteria are;
* Starting data, column V = Y
* Month-Yr from in 'Hoped for result' row 3

I've used a partial Pivot to filter, but I cannot seem to get my head around how to produce the necessary result

Any ideas?

[HansV]

Can't you simply add the months you want to the pivot table?

S2379.png

[hamster]

you can do that via Power Query then Pivot

file updated, forgot about Live filter

[snb]

I don't see the problem.
No need for a second worksheet.

[Lost Paul]

Ah, I thought I'd mess that up.

What we're trying to do is create a table that displays Demand Vs Capacity.
So that would be summarising the required project resources & minus off the allocated resources (people), in a single table.

Ideally, the user would select the first month-yr header, which would change the following 11 month headers & the results would change accordingly.

I'm sure there's still stuff I've missed, but hopefully you can get a better view of what I'm trying to achieve.

Updated file attached.

[Lost Paul]

Sorted.

Quite convoluted; combination of pivot tables, Xlookups & Value

[HansV]

Congratulations!

[p45cal]

Sorted.

Quite convoluted; combination of pivot tables, Xlookups & Value

Could you post your solution here? I was working on it, but couldn't get your hoped-for results, then I can see where I was going wrong and maybe offer a less convoluted solution…

[Lost Paul]

Hi again,

Here's a rough example of my solution.
Highlighted in yellow are the changes.

In short, used a pivot to select the relevant data.
Then used VALUE & XLOOKUP to denote column count.
Followed up with VLOOKUP, using the previous column count.

HTH

Got an error attaching the file
Will try again in a bit

[Lost Paul]

OK - try again

Nope; HTTP error :(

[DocAElstein]

I had some similar issues once when uploading files or images, ( https://eileenslounge.com/viewtopic.php?f=12&t=31753 ), you might want to play around with the http or https showing in your Browser's URL bar when accesing the Lounge, that might help
( If at first you don't see any http or https showing on your Browser's URL at the start of the address, then copy the full address, paste it in Word or a text file, - that should reveal the full address you are using, including the first http or https bit, then change that first http or https bit, then copy and paste the modified address back in your Browser's URL bar )
Alan

[Lost Paul]

Nope - no joy.

Got all sorts of IT issues here today.
Will try again later

[Lost Paul]

Sorry - seems like I cannot attach file

[HansV]

The maximum file size is 250 KB.

[Lost Paul]

It's only 50KB

[HansV]

Weird. Could you email it to me (hans dot vogelaar at gmail dot com), then I'll try to attach it.

[HansV]

Here is the workbook sent by Lost Paul.

ForEileen.xlsx

[p45cal]

re:"What we're trying to do is create a table that displays Demand Vs Capacity."

in the attached, pivot table based on a Power Query merge of the two source tables, only accounting for the 'Live' rows from each.
Currently probably showing the wrong stuff, but easy enough to change what it shows.
For each Project/Role combination and for each month, it's showing Requirement from the Project Data sheet, and %allocation from the Resource data sheet (It could (and/or) show a field called Resources which is just a count of rows from the Resource data sheet). It would be easy to subtract one from the other to show a shortfall either within Power Query or within the pivot table.

A slicer could be added to restrict which months show.