Hello,
Basically I have used this function to account for a date
=WORKDAY(B20,2,Holidays!$A$48:$A$101)
I have three different people taking different holiday times. So If I have three separate holiday lists
Is there a way of saying
If A1=Peter, WORKDAY(B20,2,Peter's holiday list) If A1=Mike, WORKDAY(B20,2,Mike's holiday list), IF A1=Ian, WORKDAY(B20,2,Ian's holiday list)
Easy?
Thank you
Marie-Louise
create a named range for each and use that in conjunction with indirect
=WORKDAY(B20,20,INDIRECT(A1))
see attached
alternatively use index with the attached workbook
=WORKDAY($B$20,20,INDEX($K$2:$M$19,,MATCH(A1,$K$1:$M$1,0)))
Last edited by martindwilson; 01-30-2014 at 08:01 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
What is the output?
if text separate your three if statements with & if data with +
or
Please Login or Register to view this content.
You will need to create the three named ranges. PeterLisr, MikeList, IanListPlease Login or Register to view this content.
Spending all day at this spreadsheet but you've definitely helped crack it.
I went for the formula below as the output was a date.
If(A1="Peter", WORKDAY(B20,2,PeterList,0) ) + If(A1="Mike", WORKDAY(B20,2,MikeList), 0) + IF(A1="Ian", WORKDAY(B20,2,IanLst),0)
Thanks!
Seems. Convoluted way to me but if it works
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks