Forum Discussion
Solved
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 dro...
- 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.
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
- Actually I think a pivot table may be exactly what you want. You can add Slicers (or regular filters) to filter the table based on store, item numbers or what ever else you need. Without seeing and knowing exactly what you have and what you need it is hard but from the sounds of it, I think you are on the right track with the pivot table. If you really can't get that to work I would then recommend looking at that FILTER() function so something like FILTER( myDATA, (myDATA[store]="store 1")*(myDATA[item#]>100)*(myDATA[item#]<300),"none found") will return all the data from store 1 and item#s between 100 and 300.
I think this is 100% the only option (pivot tables) - as my table changes in size slightly from week to week causing the paste to execute VERY VERY slowly. Something about the AutoExpand feature not allowing a table to flex properly.
I'm going to try this again with pivot tables and hopefully I can reference columns in the pivot table much like I can with a regular table. That fix made my workbook VERY fast - here is to the pivot table method maintaining that speed. 🙂
- not sure why the autoexpand of the table isn't working for you, might be something to do with how you are pasting.
I would think pivot table would have same issue in that a pivot table pulls from a table and if the table isn't autoexpanding ...
Finally you can't reference pivot table columns/data the same as a table but as i noted I think a better way for you to go is to add a slicer on the pivot table page instead of using dropdowns and recreating the data/table.