Forum Discussion
Excel Pivot Table & XLOOKUP
GSAInsp I looked at it again and made your workbook more transparent. Hiding columns, colouring cells drastically is something that stresses me out. Sorry. So, when you say "FILTER is my weak point" do you mean that you don't understand how to use the FILTER function or that you don't have the FILTER function?
In case it's the first option, the attached file links the dropdown to a dynamic FILTER range, rather than to the output of a pivot table. Hopefully it works for you.
GSAInsp Thanks, but am having difficulties fin following what you are trying to do. The pivot table is based on external data, so I can't trace where AHJ comes from. Or I'm just confused.
1) I type in the Postal Code in {Property!G2} and it set the LOOKUP_value
2) {Property!H2} then using XLOOKUP for the "CIty" from {'Postal Code'!A:A} and returns {'Postal Code'!B:B}
3) {Property!H2} then using XLOOKUP for the "County" from {'Postal Code'!A:A} and returns {'Postal Code'!C:C}
********************************************
What I want to do next is when {Property!I2} list the "County"
4) AHJ (Property!I5} pulls list from the Building Official PIVOT TABLE.
**Note: Hidden columns A-J in the Building Official tab
I hope this helps explain better.
Instead of PivotTable you may Power Query you source, FILTER() result by parameters and use returned spill for data validation.
Filter PivotTable based on cell value that's with VBA.
- I guess the Power Query & FILTER() are my weak points.
GSAInsp I looked at it again and made your workbook more transparent. Hiding columns, colouring cells drastically is something that stresses me out. Sorry. So, when you say "FILTER is my weak point" do you mean that you don't understand how to use the FILTER function or that you don't have the FILTER function?
In case it's the first option, the attached file links the dropdown to a dynamic FILTER range, rather than to the output of a pivot table. Hopefully it works for you.
