Create table based off drop down box selection

[jonnynacker]

Hey All,

Im trying to create a table based off values in another reference worksheet which will be generated from a value selected from a drop down box.

The values in this newly generated table is then to have a dropdown box next to them in which W or L can be selected.

the link below contains the same question with the attached spreadsheet

Populate Table from database based on defined value

Any help will be very much appreciated!

 

[jonnynacker]

your previous upload is what I am working off to which I reference Sheet3,

https://www.dropbox.com/s/w4klpmrtaz8057v/Book3 EXP DBx.xlsm?dl=0

and referring to the linked document in the line above;

1. Yes it does

2. You will see the Horizontal list with "Hours" and "OP Number" is a replica of the list generated in sheet1 from the drop down in sheet1 B3.

3. Sheet1 cell B5 will be a drop down with its source being sheet2, B2 down

4. the drop down list in column F under cell F6 "At Work / Leave" sheet 1 is an automatically generated drop down list which contains "W" which stands for "At Work" and "L" which stands for "On Leave". So if the Employee is At Work "W" then there name is to be brougt across in to the gorizontal list in sheet3 along with the corresponding "Hours" and "OP Number".


Essentially the Coordinator (B5) along with all employees who are At work "W" are to be brought across in to sheet3 in the horizontal list with "Hours" and "OP Number" under their name.

 

[L. Howard]

Okay, so on to this:

3. Sheet1 cell B5 will be a drop down with its source being sheet2, B2 down

You know how to put a drop down in B5 with a list referring to sheet 2, B2 column, or not?

Howard

 

[jonnynacker]

Yes, all good with that

 

[L. Howard]

Okay, I will make a macro that will do that also referring to a named range CoordList which will be the Sheet 2 B column name list. (may not be needed??)
I presume you will have more than the few names presently shown.

1. So now, to be clear in my mind, we have the two drop down (B3 & B5, plus the ones in column F "W/L"),and some body will administer the worksheet and will use the drop downs in F to assign W's or L's to the names column D.

2. Will there ALWAYS be a "W" or an "L" or can there be BLANK?

3. Now when you select a coord. in B5:
a. What happens with that name ??
b. Do all the names in D that have a "W" in F go to sheet 3 in the fashion/style as previous, except we are using these "W" names over on sheet 3, yes - no?

Lets deal with 1, 2, 3 now.

But perhaps a heads up on the SUPERVISOR and SHIFT, (briefly) will they come into play on sheet 3 also?

Howard

 

[jonnynacker]

I will be fine to make the dropdown for B5 asi ts not finalized, the Cell just needs to be referenced to take the name over to sheet3 horizontal list

1. that is correct, it will be administered at the end of each shift

2. It will never be blank (Should not be), it will always typically be "W", so if it could be generated with a "W" initially that would be great.

3.a. It is to go across to sheet3 with the other "Employee names" in the fashion/style as previous
b. yes


Supervisor and shift will not come in to play. Supervisor will be the person filling out this sheet and shift will just be for future reference

 

[L. Howard]

Okay, try this, does NOT generate the W's auto, so you have to manually do so. But if there are no W's then there is a error checker and pop up warning.



So on this example select a B3 name. (Here is where you will get the pop up warning for no W's, if none)

Then select a B5 name.

Now check sheet 3.

Am I on the right track?

Howard

Edit: The example workbook has an error and need some more work.

Will post a reworked version when I get it.

H

 

[jonnynacker]

Yep, sounds good.

Thank you so much for doing this

 

[L. Howard]

As per post #26.

Try this.

https://www.dropbox.com/s/dwd0vv5ognhhlvh/Book3 EXP DBx (1).xlsm?dl=0

Howard

 

[jonnynacker]

Perfect!
thank you howard

 

[L. Howard]

Okay, great and you are welcome.

The auto writing of the W's is not there. Seems if you want them auto written in then where is the need for the L's or for that matter even the drop downs with the choices?

But I don't have a full grasp of how the sheets will be administered.

Do you want/need a macro to produce the drop down in B5?

Howard