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 wrote:
> 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
>
>
> --
> Big M
> ------------------------------------------------------------------------
> Big M's Profile: http://www.excelforum.com/member.php...o&userid=32994
> View this thread: http://www.excelforum.com/showthread...hreadid=528401

[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