Afternoon all
Our Company has 3 levels of holiday 20 days for Staff, 25 days for Supervisors and 30 Days for Senior Managers and Directors, this never increases unless people are promoted. In the first year of employment the holiday is scaled according to the start date from 20 - 0, 25 - 0 and 30 - 0. To cope with this I have made 3 ranges TwentyDays, TwentyFiveDays and ThirtyDays. Working with a single holiday type is fine and I have 3 formulas that do this depending on what is selected in as the entitlement (Validation 20,25,30) so
=VLOOKUP(S7,TwentyDays,2,FALSE)
=VLOOKUP(S10,TwentyFiveDays,2,FALSE)
=VLOOKUP(S13,ThirtyDays,2,FALSE)
But rather than use 3 validated cells I would like to choose the leave amount from one validated cel so I have tried to combine the 3 formulas but without any success
=IF(W7,20,Vlookup(S7,TwentyDays,2,False,IF(W7,25,Vlookup,TwentyFiveDays,2,False,IF(W7,30,Vlookup,ThirtyDays,2,False)))
It is actually querying the second range name TwentyFiveDays but that is the correct name so I am wondering if it is perhaps because you cannot use more than one range name in a formula
Combining a formula into one
-
steveh
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Combining a formula into one
You do not have the required permissions to view the files attached to this post.
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
HansV
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining a formula into one
See the attached version; I'm using a single lookup range and vary the lookup column based on the yearly allotment.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
steveh
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Combining a formula into one
Hans, that is fantastic,HansV wrote:See the attached version; I'm using a single lookup range and vary the lookup column based on the yearly allotment.
thank you, but how does that work? as you can see from my attempts I had fuddled around and got the Vlookup with one cell to check against but in your formula
=VLOOKUP(S7,LookupRange,W7/5-2,FALSE)
you have substituted the comma with a divide and why 5-2, why not -3 (5-2), sorry but my maths, like my Excel is pretty low grade but when I put this formula into my 'keeper' workbook I would like to add an explanation
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
HansV
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining a formula into one
W7 can contain either 20, 25 or 30.
W7 / 5 divides that by 5, yielding 4, 5 or 6.
W7 / 5 - 2 subtracts 2 from that, resulting in 2, 3 or 4.
This is used as the 3rd argument to VLOOKUP, the column index.
So if W7 contains 20 we look up the 2nd column, if W7 contains 25 we look up the 3rd column and if W7 equals 30 we look up the 4th column.
W7 / 5 divides that by 5, yielding 4, 5 or 6.
W7 / 5 - 2 subtracts 2 from that, resulting in 2, 3 or 4.
This is used as the 3rd argument to VLOOKUP, the column index.
So if W7 contains 20 we look up the 2nd column, if W7 contains 25 we look up the 3rd column and if W7 equals 30 we look up the 4th column.
Best wishes,
Hans
Hans