Forum Discussion
Controlling XLookup with Dropdown
Hello,
I'm a little new to excel, so go easy on me - I don't know all of the jargon just yet but I'm learning fast. 🙂
I am trying to allow a user of a workbook to select an option from a dropdown list and their selection alters what my XLookup is referencing as a lookup array in a pivot table on another sheet.
So if my dropdown list is H1 - I'm working on a code like...
=IFERROR(ABS(XLOOKUP(A3,'SALES Pivot'!A:A,'SALES Pivot'!$H$1)),"")
The issue is... $H$1 (the drop down) is on the page that the XLOOKUP is operating in.
Any ideas? I originally had an XLOOKUP that was searching 2 lookup_values and avoiding using a pivot table... but I am running a few hundred XLOOKUPS on each sheet and it was making the workbook very slow... I was hoping that simplifying the xlookup by using a pivot table would help... but it has broken my drop down list toggle. That worked well with this:
=IFERROR(ABS(XLOOKUP(A5&$H$1,FullDATA!A:A&FullDATA!B:B,FullDATA!E:E)),"")
I'll note that I have multiple sheets referencing this same Pivot Table.
Thanks in advance! 🙂
- so a few things. you are right that you can't just call out H1 as part of that cell reference like that. You could, but I recommend NOT, use the INDIRECT() function to 'paste' together the refence you need. In fact I'm not even going to write that formula out because I recommend against it.
What I will recommend is going back to your original formula EXCEPT use Table References. So step 1 is to go to the FullDATA sheet and highlight the data area (actually just selecting any cell in the area is usually enough for Excel to figure out the table boundaries) and select under HOME->Format as a Table . The pop-up will confirm the area of the data table and if the table has a header row. Then select how you want it to look (doesn't really matter). Once the Table is formatted there should be a Table menu item and in the ribbon for the Table all the way on the left you can see it is probably named Table1. Change that to something more meaningful like myData. Then in the Formula you will replace FullData!A:A with something like myData[FirstName]. In this way Excel will only look at your rows of data in the table (and the table will automatically grow/shrink as you add/remove data) and not do evaluations on the entire column.
The second option is to use GETPIVOTDATA() function but that function is a bit harder to explain without having a file and sample data, but there are plenty of youtube videos too.
Next I want to ask/question why you have hundreds of XLOOKUPS on the sheet and if you might benefit from other functions instead like FILTER().
7 Replies
- so a few things. you are right that you can't just call out H1 as part of that cell reference like that. You could, but I recommend NOT, use the INDIRECT() function to 'paste' together the refence you need. In fact I'm not even going to write that formula out because I recommend against it.
What I will recommend is going back to your original formula EXCEPT use Table References. So step 1 is to go to the FullDATA sheet and highlight the data area (actually just selecting any cell in the area is usually enough for Excel to figure out the table boundaries) and select under HOME->Format as a Table . The pop-up will confirm the area of the data table and if the table has a header row. Then select how you want it to look (doesn't really matter). Once the Table is formatted there should be a Table menu item and in the ribbon for the Table all the way on the left you can see it is probably named Table1. Change that to something more meaningful like myData. Then in the Formula you will replace FullData!A:A with something like myData[FirstName]. In this way Excel will only look at your rows of data in the table (and the table will automatically grow/shrink as you add/remove data) and not do evaluations on the entire column.
The second option is to use GETPIVOTDATA() function but that function is a bit harder to explain without having a file and sample data, but there are plenty of youtube videos too.
Next I want to ask/question why you have hundreds of XLOOKUPS on the sheet and if you might benefit from other functions instead like FILTER().- BTW, I kinda jumped right in there so let me step back. Hi and welcome and don't worry about being new, we are just here to try and help.
I know new members can't attach their files but maybe you could tell us more about what you are doing because as I was hinting above there are lots of ways to do things in excel and some can be much easier than others. Also what version of Excel do you have? I mentioned the FILTER() function which is new with Excel 2021 (I believe) and certainly in Excel 365. If you have a sample of your workbook that doesn't have confidential/private/personal info you could also share it using SharePoint or DropBox or something similar.Also! It is important to note that I have to copy and paste this data into the workbook once a week and upload it to a sharepoint portal for teams to use. Which is why I thought a pivot table would *maybe* remove one or two steps of making the table, naming, and removing unnecessary columns. I could copy and paste and move on with my life. LOL
