Create a Dynamically Generated List Based on Criteria

[PacSum]

I've exhausted the googlez and yet to find exactly what I'm looking for.

I have two worksheets. Sheet two is a drop in report from another source. Sheet one aims to provide a simple way for a user to query data on sheet two. Below is a watered down example of what I'm looking for. This table represents how many miles each person ran on a specific day:


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Tony[/TD]
[TD]Sally[/TD]
[TD]Martha[/TD]
[TD]Billy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD][/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

On sheet 1, I want to have 2 drop downs that in conjunction will populate a result in a third cell:

[TABLE="width: 500"]
<tbody>[TR]
[TD]NAME (static dropdown list)[/TD]
[TD]DAY (dynamic dropdown list)[/TD]
[TD]RESULT[/TD]
[/TR]
</tbody>[/TABLE]

Name will contain all 4 names at all times. Simple enough. Here's the caveat. I want the DAY drop down to be dynamically populated ONLY with days > 0.

Ex 1. Select Tony in cell A1, cell B1 contains Monday, Tuesday, Saturday. Select Monday in cell B1 and cell C1 populates with 5.
Ex 2. Select Billy in cell A1, cell B1 contains all 7 days. Select Tuesday in cell B1 and cell C1 populates with with 1.

I know I could manually create 4 lists relative to each person's name. But imagine this example amplified x100.

How would one go about creating a list from this format in this manner?

 

[PacSum]

Howdy,

Maybe I did not understand the problem requirement ... but ...

Create dynamic range for names (automatic extension to the addition of another name), and range for weekdays.
In sheet 1 where you want to get the result you use a formula like:
= Index (range with data, match (cell with name in dropdown, .....), match (cell with weekdays in dropdown, ......))

The range for names is simple.

The issue is populating the list for weekdays. I don't want all 7 days. I ONLY want days which could bring up a non zero amount.

 

[PacSum]

Not sure what the problem is, try this Example file, see if it helps!!
https://app.box.com/s/5y49zjtrvmh92ooma6nf4mx7dru7xwqw

Yep. Works perfect. I actually created it from scratch and it worked as well. When i tried to apply to my application, it failed.

The problem is, in my infinite wisdom, I assumed with the basic knowledge I have of excel and VBA that I'd be able to transcribe what you sent to actually fit my true data set, which isn't the same, but pretty closely mirrors it. As you can see, I failed miserably.

To be more precise, this is exactly how the data is laid out:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tim[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[TD]Sue[/TD]
[TD]Gus[/TD]
[TD]Cam[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 5[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Parameter 6[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In a post preview I realize that the visual doesn't properly represent cells.

Names start on cell D7.
Parameters (days in previous example) related to names start on cell A10
Data begins in cell D10

And I may not have articulated appropriately before, but no cell will be blank. All non-data is represented by a 0. 0 should omit the parameter from showing in the drop down, not a blank.

@MickG, thanks again for the help. Even if you don't end up seeing this message, I very much appreciate you getting me started. I'll be sure to pay it forward once I begin to learn all of this a little bit more.

On that note, do you have any advice on courses/tutorials/how-to's etc on working with advanced excel projects like this?

 

[Eric W]

To expand a bit on pgc01's comment, you can do all this without VBA. In L1 (or some range of your choice) put the formula below and drag down as necessary. Then set up a Name in the Name Manager called List2, with the formula:

=OFFSET(Sheet1!$L$1,0,0,SUM(--(Sheet1!$L$1:$L$10<>"")))

Now select B1, go to Data Validation, select List, and enter

=List2

as the source. Finally, enter the C1 formula in. You should end up with something like this:

	A	B	C	D	E	F	G	H	I	J	K	L	M
Joe	Parameter 3
Parameter 3
Parameter 6
Tim	Bob	Joe	Sue	Gus	Cam
Parameter 1
Parameter 2
Parameter 3
Parameter 4
Parameter 5
Parameter 6

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]Parameter 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=INDEX($D$10:$I$15,MATCH($B$1,$A$10:$A$15,0),MATCH($A$1,$D$7:$I$7,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]L1[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$10:$A$15,SMALL(IF(INDEX($D$10:$I$15,0,MATCH($A$1,$D$7:$I$7,0))<>0,ROW($A$10:$A$15)-ROW($A$10)+1),ROWS($L$1:$L1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tom.Jones]

The range for names is simple.

The issue is populating the list for weekdays. I don't want all 7 days. I ONLY want days which could bring up a non zero amount.

You have already received an answer from Erik W, to have just those days / parameters that are not zero.

 

[PacSum]

You have already received an answer from Erik W, to have just those days / parameters that are not zero.

This is true. But this was not the case when you wrote your message or when I responded to you.

@Eric W
This worked perfectly. Thank you.

 

[Tom.Jones]

This is true. But this was not the case when you wrote your message or when I responded to you.

You are right. I have assumed that the part with days> 0 is something easy for you and I only proposed the data extraction solution.
Next time I will read more carefully and I will not make any assumptions ...