Jul 07 2022 01:57 PM
Jul 07 2022 01:57 PM
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:
I'll note that I have multiple sheets referencing this same Pivot Table.
Thanks in advance!
Jul 07 2022 03:26 PMSolution
Jul 07 2022 03:37 PM
Jul 07 2022 04:02 PM - edited Jul 07 2022 04:08 PM
Thanks for the detailed response and the kind words. I am using Excel 365.
So I am creating a workbook for 7 stores to use and all 7 stores have different data sets. The data dump that I am forced to work with has columns of Item Number, Store Number, Retail Price, Units Sold, and Quantity On Hand. It is about 21,000 rows long. It is important to note that the Item Number is duplicated across all 7 stores... hence why I need to xlookup by both Item Number and Store Number.
So my XLookup is referencing the data dump table by Item Number and Store Number, and returning either retail price, quantity on hand, and units sold last month.
The book has about 15 sheets(broken up by distributor of the items in question), with anywhere from 30 to 500 Item Numbers on each sheet. So in the rows, each item number will have 3 xlookups - returning Price, QOH, and Sales.
More or less, I am creating a tool to help stores order. So they will select their store number from a drop down on each sheet and it will pull the respective data from the data set and drop it into the sheet.
I'm really trying to increase the performance of the sheet so it loads quickly and doesn't snag. I did what you suggested on one of the rows and it works flawlessly.
Let me know if you have any other ideas - but this is definitely an improvement!
Jul 07 2022 04:12 PM - edited Jul 07 2022 04:12 PM
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
Jul 07 2022 06:09 PM
Jul 08 2022 10:06 AM
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.
Jul 08 2022 10:30 AM