Combining data validation and custom forumla

[RobV1]

Hi I was wondering if anyone can provide some guidance with a problem I'm having.

I'm currently attempting to create a sheet to track staff leave from a table which is to be used as a default, but editable by user with a drop down.

So far I'm managed to use a nested vlookup and Hlookup to transfer data across between tables, but can't figure out how to add in the drop down when attempting to use the data validation element.

Perhaps easier with a few tables to explain:

This is the table i'm wanting to populate - called 'leave'

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]01/12/2017[/TD]
[TD]02/12/2017[/TD]
[TD]03/12/2017[/TD]
[TD]04/12/2017[/TD]
[TD]05/12/2017[/TD]
[TD]06/12/2017[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]TM1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TM2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the default table value I want to use - called staff:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]01/12/2017[/TD]
[TD]02/12/2017[/TD]
[TD]03/12/2017[/TD]
[TD]04/12/2017[/TD]
[TD]05/12/2017[/TD]
[TD]06/12/2017[/TD]
[/TR]
[TR]
[TD]Column[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]TM1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]TM2[/TD]
[TD]NWD[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]NWD[/TD]
[TD]0.8[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

This is the list of Drop downs I wish to add to 'Leave' - called Hours
[TABLE="width: 500"]
<tbody>[TR]
[TD]NWD[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]0.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Sorry, thats a lot of text.

Table - staff - populates using drop downs, so far so good.
Table - leave - has the following nested lookup =VLOOKUP($A4,'Staff list'!$A$3:$H$62,HLOOKUP(B$3,'Staff list'!$A$1:$H$2,2,FALSE),FALSE) which populates the 'leave' table based on the 'staff' table, however, i want the 'leave' table to also include a drop down feature so that if there are changes to the standard week worked, these can be edited by user but within the defined limits of the 'Hours' table.

Unfortunately I've drawn a complete blank, I'm pretty sure I need to use the custom formula option in data validation but its not as straight forward as I would have hoped.

Unfortunately VBA isn't an option for me with this one either as i've absolutely no knowledge of VBA at present, nor do the intended users.

Please can anyone help? Thank you in advance.

Rob.

 

[Gerald Higgins]

Hi, welcome to the board.

I've read your post a couple of times now and can't work out exactly what you want.

I'm guessing you want to control the available options in the data validation cell, in some kind of dynamic way.

This is probably do-able but it's difficult to be sure as I don't understand exactly what you want to do.

Please can you try and explain really clearly what you want this to do ?

Don't worry too much about explaining the real world application of your task - this is probably not that relevant to finding a solution.
Focus on your data, and describing as clearly as possible, what exactly you want to do with it.

 

[RobV1]

Hi Gerald,

Many thanks for coming back to me.

Essentially what I am trying to do is to 'overlay' a drop down on the default values.

So the table 'staff' provides the starting point. With the nested v/hlookup (=VLOOKUP($A4,'Staff list'!$A$3:$H$62,HLOOKUP(B$3,'Staff list'!$A$1:$H$2,2,FALSE),FALSE)) in the table 'leave', the values transpose across from the staff chart, below example works as present:

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]Date[/TD]
[TD]01/12/2017[/TD]
[TD]02/12/2017[/TD]
[TD]03/12/2017[/TD]
[TD]04/12/2017[/TD]
[TD]05/12/2017[/TD]
[TD]06/12/2017[/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[/TR]
[TR]
[TD]TM1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
What I'm struggling with is having obtained the correct 'default' values as above, how to retain these whilst having the ability to change the numbers using a drop down only. So for example, if TM1 normally works on Friday as above, but wants to take a short day/full days leave, I want to be able to apply a drop down option to amend the number to 0.5 or 0 (using the 'hours' chart as the data validation range).

Apologies, i'm not sure that I've clarified that overly....