- last edited on
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?
05-20-2018 08:58 PM
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:
Please see the attached file for an example
05-21-2018 12:42 AM
05-21-2018 06:37 AM
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.
05-21-2018 06:45 AM
05-21-2018 07:14 AM
Here are a few resources to help learn about array formulas. The most important thing is that you enter them using the key combination CTRL + SHIFT + ENTER. It may also help to go to the formulas tab and use the evaluate formula option:
05-21-2018 07:31 AM
06-05-2018 11:49 AM
Hi Matt Mickle,
I’m sorry - I must have expressed myself badly. I need to enter all the data in advance for the vendors. As it stands, I seem to be able to enter data in the “data” worksheet for a given date, and pull up the vector of vendors who’ll be there on that date in the “query” worksheet - provided that all the vendors in the “data” worksheet are showing that date.
I don’t seem to be able to enter a matrix of dates for the vendors (vendor 1 will be there on June 02, 09, 16, 23, July 07, 14, 21 etc, vendor 2 will be there on June 02, July 07 etc) and subsequently click on a date in the dropdown menu in the “query” worksheet and get a vector of vendors FOR THAT DATE.
Here’s the example I’ve been working with.
Am I missing a step somewhere, please?
06-05-2018 12:11 PM
As you mentioned I believe there is a disconnect:
1. Can you identify the inputs and what format you would like to enter them?
2. Can you please provide an example of how you would like the data displayed?
3. A non-sensitive example of the data may help
Basically I need a before and after. What you plan to enter and what you expect to get back.
06-05-2018 02:44 PM
What you sent in the first place looked as if it did what I needed! The data isn’t sensitive, and you have most of it already. What I want to enter is the list of vendors (1-40), which I assumed I could do in worksheet 3. You’d already entered the dates, which I truncated to show May-December.
What I want - and thought I could do - is to select a vendor in worksheet 1, select a month and then select the dates which the vendor applied for (for example, the first Saturday in each month, first and third Saturday, Saturdays between October and December for seasonal items …).
I then expected to be able to select a Saturday in worksheet 2 and see a list of vendors who applied for that date, select another Saturday and see another list.
Repeating (to be sure I’m clear this time): I want to pick a vendor from the dropdown list in w/sheet 1 and use the dropdown lists to enter a series of Saturdays (the vendor’s ideal selection) which are then stored. After entering all the vendors and Saturdays, I want to select a Saturday in w/sheet 2 and get a list of vendors requesting that Saturday.
Input: vendor applications, checking requested Saturdays in a 5-week by 8-month grid.
Manual entry in Excel.
Output: list of vendors for a given Saturday (this is why my first thought was the 3D array).
Ideal additional output (can be done manually, but …): list of vendors for a given month showing the number of days requested (as a basis for monthly billing).
I hope I got it right this time!
06-06-2018 12:52 PM
It does, but it only shows vendors if I have just entered them in “data” for the specific date. If I enter another date, that vendor disappears from the “query” output.
As the goal is to enter all the dates the vendors want to participate and then use the “query” output for individual dates to see how many (and which) have applied for a given date (will the market be too crowded, do we need to expand street closures etc), this doesn’t quite work for this purpose.
06-06-2018 01:29 PM
Try looking at the file I have attached. I have provided all three outputs you requested. Hope this helps.
06-08-2018 07:50 AMSolution
Just wanted to check back in and see if you were able to resolve your issue with the sample file provided?
by JLK2004 on May 29, 2020
by Ravitosh Kumar on May 29, 2020
by MS Excel Team on May 29, 2019