Drop down box coding to another cell

[mdurkin]

Hi guys, I hope someone here can help me out. I'm making a payroll database for my guys' timesheets.

I have hit a wall - I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.

list.jpg

HELLLPP!!!!

Thanks guys!

M.D

[6StringJazzer]

How do you want to calculate the value in the cells in row 7 (not "cell 7") based on the selection in row 6? The dropdown just selects a shift. Do you mean that row 7 should have the number of hours in the shift depending on the shift? Somewhere you must have a list of these shifts that you use to set up the dropdowns. In the next column put the hours for each shift. Then use VLOOKUP on the value selected in row 6 to look up the desired value in row 7.

By the way, attaching an image has minimal value. Just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

[Palmetto]

You need a two column look up table to correlate the shift with the times.

Column-1 of the table would consist of the shift names (daytime, nighttime, etc.)
Column-2 of the table would consis of the related shift times.

Say this table was on sheet two in the range of A1:B10.

If cell A1 on sheet 1 contains your drop down selection, then in "cell 7", you would use this formula:

=VLOOKUP(A1,Sheet2!$A$1:$B$10,2,FALSE)

Is that what you were looking for?

You could avoid using a lookup table by using the LOOKUP function:

=LOOKUP(A1,{"Daytime","Nightime"},{"0700-1500","1500-2300"})

The list items in the first set of brackets {} must be in ascending order with the items in the second set listed or order correlated to the first set.