Forum Discussion
Excel Pivot Table & XLOOKUP
I have an Excel (Office 365 for Business & Windows 11) file that utilizes a XLOOKUP based on a postal code to populate the city and county fields. From there I have a separate “Data Validation” List that pulls information from a Pivot Table.
From there I have Pivot Table that based on the County it provides a list of Building Officials (AHJ)
What I would like for Excel to do is when the County from the Postal Code is identified then the Pivot Table uses the result from that field to return the list of Building Officials for the user to select from.
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.
9 Replies
Riny_van_Eekelen here is the file.
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.
- Sorry:
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.
GSAInsp Could you upload a workbook? The pictures alone aren't very meaningful.
