Forum Discussion
Displaying data from a table based on selected criteria
It's always more helpful if you can post a copy of the spreadsheet itself. That said, let me see if I can give some general answers to your questions. But I'd also encourage you to post your original, so long as it contains no private or proprietary information.
Let me take your last question first. You wrote: I have set up bundle 1 materials on sheet 2 of my workbook as a 3 column table (item ID, part name & quantity needed). That is the preferred display for the order sheet. Or I wondered If I should combine the data into 1 column thereby having only 1 cell per item by using the CONCATENATE formula that can be updated with changes to the table (i.e. change name, increase amounts, etc)? Would that allow the data to pull over easier?
To which I'd say, you're always better off with distinct data elements in their own columns. It is far easier to concatenate the separate cells when that's needed than to un-concatenate if you store them all together. It's easier to update individual pieces when the data are stored separately.
I am creating material order sheets. The goal is to have 1 order sheet for the end user to update selections on and submit. On the order sheet I want to have the ability to choose from multiple bundled options.
This is a bit confusing:
- are you going to be sending the entire workbook, with all of its backup data sheets, to all of your users?
- Or do you mean by "user" an employee in the front office or on the phone, interacting with a customer?
- And are you then, subsequently, going to be selecting from among the bundled options?
A bundled option will have a list of parts associated with that bundle. When I select a particular bundle, I want the list of items associated with that bundle to populate on the order sheet. My thoughts are to set up sheet 1 of my workbook to be the order sheet. Subsequent sheets will be data reference sheets that I will store the data for the bundled lists. How do I choose bundle 1 on the order sheet and have the list of materials from the referenced sheet populate?
There are probably several tools (Excel functions) that could do this. A lot here would depend on the layout of the data. So here in particular is where it would help if you could post a copy of the workbook as it currently exists, recognizing it's still a work in process. That said, if you want to just work on your own, I'd suggest looking into the use of tables as the repository of data, and then the use of one of several functions to extract the appropriate data for the order sheet. In particular,
- XLOOKUP
- FILTER
Here's a website that can help with understanding how those work. https://exceljet.net/
mathetes I created a sample workbook that is set up in the way I envision the order sheet/workbook should be set up. This is a file that is going to be sent to field techs to use when ordering materials from our warehouse. I can PW protect the data on the tabs to prevent corruption. But I want to be able to have the tech choose random parts on the first section and then choose bundles options for jobs that require a lot of standard parts - all on 1 order page (1st tab). I don't want them to have to go beyond the first tab when choosing parts. BTW, there are 4 or 5 times more parts in actual bundles than what I have listed on the attached. Is there a way to grab the bundle parts (from other tabs) based on the option chosen and have them populate on the "order page" in a table as I have illustrated? Or do you see a better way to set this up? Thanks in advance for any advice you can give!
Keep all your bundles on one page, in one table. That's always preferable. Excel excels in parsing single databases. It's possible to use separate tabs and separate tables, but doing so almost always interferes with the simplest solution. Learn to differentiate the raw data (Input) from the filtered and sorted or selected Output end of things. Don't make the mistake of trying to splitting your raw data up; use a column or two in the single database to accomplish that, and then use criteria in FILTER to do the actual sifting/sorting.
mathetes Thank you so much! That is exactly what I wanted!!
I discovered the power of FILTER through this YouTube video, produced by MIcrosoft, featuring a person who makes an occasional showing here on the techcommunity pages. https://www.youtube.com/watch?v=9I9DtFOVPIg
It (along with the others described) is amazing in what you can accomplish with it.