Forum Discussion
Returning list from 3D array
I have a weekly (Saturday) market and 40 vendors. I have a matrix with months as the rows and Saturday dates as the columns (1-5). I ask vendors to check the dates they want to be there. I create worksheets 1-40 for the vendors, where the vendors are either the worksheet name or in a NAME field on the worksheet. The worksheets have identical arrays, showing which Saturdays a vendor has checked.
How do I return a list of vendors who’ve checked a given Saturday (I’m clicking on)?
(a) Is there a more elegant way to do this?
(b) What function do I need to generate that list?
Paul-
Just wanted to check back in and see if you were able to resolve your issue with the sample file provided?
17 Replies
Paul-
I think this is a better setup.
Try these modifications:
1. Have one worksheet for all vendors
2. Have a Vendor Dropdown
3. Have a month Drop Down
4. Have a weekend Dropdown
5. Have a Query Worksheet that will tell you, who will perform or be in attendance on X date.
I've set it up where the dates of the Saturday's will narrow down to a specific month when the month is selected. i.e. If you select February it will only show Saturday's in February. If you select March it will only show Saturday's in March.
Use an array formula to help identify the vendors for a particular Saturday:
={IFERROR(INDEX(Data!$A$2:$C$100,SMALL(IF(Data!$C$2:$C$100=Criteria,ROW(Data!$A$2:$C$100)-1),ROW(2:2)),COLUMN()),"")}
Please see the attached file for an example
- A quick question: why doesn’t vendor 1 show up in the list for 2018-01-06? The Query seems as if it should return them as well.
Paul-
I went ahead and updated the formula. There was a reference issue that was preventing the formula from pulling in Vendor 1. It should work without issue now. I used 1/6 and 1/20 as test cases.
- Thnk you! I will brood on this after coffee.
