Nested IF Formula Evaluation Pls

[Big M]

Hi everyone,

I may have discovered a way to solve my own question in a previous post
(http://www.excelforum.com/showthread.php?t=528208). I'm attempting to
use a nested IF formula as shown below:

=IF(B9=shifts!A1:A17,"8",IF(B9=shifts!A19:A35,"6","OFF"))

B9 is a dropmenu of listed shifts (not available shifts) from another
worksheet within the same workbook, titled "shifts". From the
"shifts" worksheet, cells A1 through A17 are 8 hour shifts, while A19
through A35 are 6 hour shifts. I'm attempting to create a formula in
D9 (the hours column), which will automatically fill in 8, 6, or OFF,
depending upon the selection from the dropdown menu in B9.

I *almost* succeeded with a normal IF formula (where *8 was true &
everything else false). Then I realized nesting would probably be
better, since I didn't want any hours sitting in D during an employee's
day off Any help would be *VERY* much appreciated. Thanx in
advance...

Big M

 

[Andrew Taylor]

I'm not sure I completely understand your requirement, but try

=IF(OR(B9=shifts!A1:A17),"8",IF(OR(B9=shifts!A19:A35),"6","OFF"))

entered as an Array Formula (Ctrl-Shift-Enter)

hth
Andrew

 

[Big M]

Hi Andrew, thanx for replying.

For some reason, I'm not having much luck . I only thought
of the nested IF idea after checking out examples elsewhere on the net.
I've also taken a look at the vlookup idea, but I'm not certain if that
may help or not. Although I'm not entirely unfamiliar with Excel, this
area just seems to be eluding me... If you happen to have any other
ideas which may work, I'm open to any suggestions...

Again, thanx for the input, it's much appreciated

Big M