I've read the previous postings regarding the use of more than 7 if then
statements, and tried the following:
CELL D2 =IF(OR(B4=4,B4=5,B4=6,B4=14,B4=16),"Mike
Pelosi",IF(OR(B4=1,B4=2,B4=3,B4=13,B4=24),"Nick
Re",IF(OR(B4=7,B4=8,B4=22,B4=35),"Nicole Hughes",IF(OR(B4=20,B4=25,B4=9),"Kim
O'Connor", "D3"))))
CELL D3 =IF(OR(B4=19,B4=21,B4=26,B4=15,B4=18),"Justin
Eldredge",IF(OR(B4=23,B4=30,B4=34,B4=36),"Janet Ward",IF(OR(B4=11,B4=12),"Jen
Ferrarraccio")))
CELL D4 =IF(B4=D2),D2,D3
I am attempting to make the formula for column D (fromD4 down) assign a
"Person" to the cell based on the number located in column B. However, this
doesn't seem to be working. I would greatly appreciate some help!
Thanks,
Mike

Hi!

You can shorten those formulas by doing this:

=IF(OR(B4={1,2,3,4}..........

In the formula in cell D2 remove the quotes from around "D3" if "D3" is
really a reference to cell D3

The formula in cell D3 is missing a final Value_If_False argument. It's not
required but if the criteria in the formula aren't met it will default to
FALSE.

You might be better off using VLOOKUP.

Biff

"IF I only had a brain for IF statements" <IF I only had a brain for IF
wrote in message
...
I've read the previous postings regarding the use of more than 7 if then
statements, and tried the following:
CELL D2 =IF(OR(B4=4,B4=5,B4=6,B4=14,B4=16),"Mike
Pelosi",IF(OR(B4=1,B4=2,B4=3,B4=13,B4=24),"Nick
Re",IF(OR(B4=7,B4=8,B4=22,B4=35),"Nicole
Hughes",IF(OR(B4=20,B4=25,B4=9),"Kim
O'Connor", "D3"))))
CELL D3 =IF(OR(B4=19,B4=21,B4=26,B4=15,B4=18),"Justin
Eldredge",IF(OR(B4=23,B4=30,B4=34,B4=36),"Janet
Ward",IF(OR(B4=11,B4=12),"Jen
Ferrarraccio")))
CELL D4 =IF(B4=D2),D2,D3
I am attempting to make the formula for column D (fromD4 down) assign a
"Person" to the cell based on the number located in column B. However,
this
doesn't seem to be working. I would greatly appreciate some help!
Thanks,
Mike

I'd switch to using VLOOKUP
to greatly simplify things and for ease of maintenance

In a new sheet, say, Sheet2, in A1:B36,
create the lookup reference table below
(Numbers in col A, Names in col B):

1 Nick Re
2 Nick Re
3 Nick Re
4 Mike Pelosi
5 Mike Pelosi
6 Mike Pelosi
7 Nicole Hughes
8 Nicole Hughes
9 Kim O'Connor
10
11 Jen Ferrarraccio
12 Jen Ferrarraccio
13 Nick Re
14 Mike Pelosi
15 Justin Eldredge
16 Mike Pelosi
17
18 Justin Eldredge
19 Justin Eldredge
20 Kim O'Connor
21 Justin Eldredge
22 Nicole Hughes
23 Janet Ward
24 Nick Re
25 Kim O'Connor
26
27
28
29
30 Janet Ward
31
32
33
34 Janet Ward
35 Nicole Hughes
36 Janet Ward
(etc)

Then in say, Sheet1, we could simply use in say, D4, either:

=IF(B4="","",VLOOKUP(B4,Sheet2!A:B,2,0))

where a zero return would mean there's no name assigned as yet for the
number input in B4

or, perhaps use a better but slightly longer:

=IF(B4="","",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unas signed
#",VLOOKUP(B4,Sheet2!A:B,2,0)))

which returns: Unassigned #
instead of zeros for unassigned numbers input in B4

Just copy D4 down to return correspondingly for other inputs in B5, B6 ...
etc

Note that the 2nd formula does not trap for "non-existing"/ invalid numbers
which may be input in B4, for example: 40 or 0 or -2 (say). Such cases will
still return: #N/A

And if you want to trap for these as well,
then put in D4:

=IF(B4="","",IF(ISNA(VLOOKUP(B4,Sheet2!A:B,2,0))," Invalid
#",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unassigned
#",VLOOKUP(B4,Sheet2!A:B,2,0))))

Invalid input numbers in B4 will return the phrase
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"IF I only had a brain for IF statements" <IF I only had a brain for IF
wrote in message
...
I've read the previous postings regarding the use of more than 7 if then
statements, and tried the following:
CELL D2 =IF(OR(B4=4,B4=5,B4=6,B4=14,B4=16),"Mike
Pelosi",IF(OR(B4=1,B4=2,B4=3,B4=13,B4=24),"Nick
Re",IF(OR(B4=7,B4=8,B4=22,B4=35),"Nicole
Hughes",IF(OR(B4=20,B4=25,B4=9),"Kim
O'Connor", "D3"))))
CELL D3 =IF(OR(B4=19,B4=21,B4=26,B4=15,B4=18),"Justin
Eldredge",IF(OR(B4=23,B4=30,B4=34,B4=36),"Janet
Ward",IF(OR(B4=11,B4=12),"Jen
Ferrarraccio")))
CELL D4 =IF(B4=D2),D2,D3
I am attempting to make the formula for column D (fromD4 down) assign a
"Person" to the cell based on the number located in column B. However,
this
doesn't seem to be working. I would greatly appreciate some help!
Thanks,
Mike

"Biff" wrote:

=IF(OR(B4={1,2,3,4}..........
... a little bird told me Biff might be suggesting this <bg

You might be better off using VLOOKUP ..
... and which is exactly the response detailed in the other post <g

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

similarly to the first post I was trying to nest more than 7 statements,
which isn't working.

I have a column of dates, and I'm trying to calculate the date using
the Month function. However that function only returns a number and I
want to display the month written out.

Here's the way I was going:

=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August" ,IF((MONTH(A19))=9,"September",IF((MONTH(A23))=10, "October",IF((MONTH(A21))=11,"November",IF((MONTH( A26))=12,"December",IF((MONTH(A11))=1,"January","
No Month")))))))

I was going to try the vlookup function, however this spreadsheet is
setup as a "list" and vlookup says the table must be sorted in
ascending order... which might not be the case.

There has got to be an easier way to do this.

Thank you

J


--
Shakespeare
------------------------------------------------------------------------
Shakespeare's Profile: http://www.excelforum.com/member.php...o&userid=21552
View this thread: http://www.excelforum.com/showthread...hreadid=392766

"Shakespeare" wrote:
....

=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August" ,IF((MONTH(A19))=9,"Septem
ber",IF((MONTH(A23))=10,"October",IF((MONTH(A21))= 11,"November",IF((MONTH(A2
6))=12,"December",IF((MONTH(A11))=1,"January","
No Month")))))))
.....
There has got to be an easier way to do this.

Think we could try just something like:
=TEXT(A1,"mmmm")
where A1 contains a date

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

In addition to Max's post which gives you the easy solution, you need to
know that VLOOKUP doesn't have to have a list of sorted data as you suggest.
If you use the 4th argument with VLOOKUP either FALSE or 0, then it will
work with unsorted lists.
=VLOOKUP(your_value,your_table,2,FALSE)

--
Regards

Roger Govier


"Shakespeare"
wrote in message
...

similarly to the first post I was trying to nest more than 7 statements,
which isn't working.

I have a column of dates, and I'm trying to calculate the date using
the Month function. However that function only returns a number and I
want to display the month written out.

Here's the way I was going:

=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August" ,IF((MONTH(A19))=9,"September",IF((MONTH(A23))=10, "October",IF((MONTH(A21))=11,"November",IF((MONTH( A26))=12,"December",IF((MONTH(A11))=1,"January","
No Month")))))))

I was going to try the vlookup function, however this spreadsheet is
setup as a "list" and vlookup says the table must be sorted in
ascending order... which might not be the case.

There has got to be an easier way to do this.

Thank you

J


--
Shakespeare
------------------------------------------------------------------------
Shakespeare's Profile:
http://www.excelforum.com/member.php...o&userid=21552
View this thread: http://www.excelforum.com/showthread...hreadid=392766

If you have a "true", XL recognized date in a cell, and you wish to display
the month (or day), you can just format *that* cell, or any other cell to
display what you wish.

With
8/4/2005
in A1
You can format A1 to
mmmm
OR, in say B1, enter
=A1
And format B1 to
mmmm
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Shakespeare"
wrote in message
...

similarly to the first post I was trying to nest more than 7 statements,
which isn't working.

I have a column of dates, and I'm trying to calculate the date using
the Month function. However that function only returns a number and I
want to display the month written out.

Here's the way I was going:


=IF((MONTH(A3))=7,"July",IF((MONTH(A4))=8,"August" ,IF((MONTH(A19))=9,"Septem
ber",IF((MONTH(A23))=10,"October",IF((MONTH(A21))= 11,"November",IF((MONTH(A2
6))=12,"December",IF((MONTH(A11))=1,"January","
No Month")))))))

I was going to try the vlookup function, however this spreadsheet is
setup as a "list" and vlookup says the table must be sorted in
ascending order... which might not be the case.

There has got to be an easier way to do this.

Thank you

J


--
Shakespeare
------------------------------------------------------------------------
Shakespeare's Profile:
http://www.excelforum.com/member.php...o&userid=21552
View this thread: http://www.excelforum.com/showthread...hreadid=392766

Thanks Max, Biff, Roger and Shakespeare, for all of your help. Everything is
in working perfectly. Have a great day!
~Mike

"Max" wrote:

I'd switch to using VLOOKUP
to greatly simplify things and for ease of maintenance

In a new sheet, say, Sheet2, in A1:B36,
create the lookup reference table below
(Numbers in col A, Names in col B):

1 Nick Re
2 Nick Re
3 Nick Re
4 Mike Pelosi
5 Mike Pelosi
6 Mike Pelosi
7 Nicole Hughes
8 Nicole Hughes
9 Kim O'Connor
10
11 Jen Ferrarraccio
12 Jen Ferrarraccio
13 Nick Re
14 Mike Pelosi
15 Justin Eldredge
16 Mike Pelosi
17
18 Justin Eldredge
19 Justin Eldredge
20 Kim O'Connor
21 Justin Eldredge
22 Nicole Hughes
23 Janet Ward
24 Nick Re
25 Kim O'Connor
26
27
28
29
30 Janet Ward
31
32
33
34 Janet Ward
35 Nicole Hughes
36 Janet Ward
(etc)

Then in say, Sheet1, we could simply use in say, D4, either:

=IF(B4="","",VLOOKUP(B4,Sheet2!A:B,2,0))

where a zero return would mean there's no name assigned as yet for the
number input in B4

or, perhaps use a better but slightly longer:

=IF(B4="","",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unas signed
#",VLOOKUP(B4,Sheet2!A:B,2,0)))

which returns: Unassigned #
instead of zeros for unassigned numbers input in B4

Just copy D4 down to return correspondingly for other inputs in B5, B6 ...
etc

Note that the 2nd formula does not trap for "non-existing"/ invalid numbers
which may be input in B4, for example: 40 or 0 or -2 (say). Such cases will
still return: #N/A

And if you want to trap for these as well,
then put in D4:

=IF(B4="","",IF(ISNA(VLOOKUP(B4,Sheet2!A:B,2,0))," Invalid
#",IF(VLOOKUP(B4,Sheet2!A:B,2,0)=0,"Unassigned
#",VLOOKUP(B4,Sheet2!A:B,2,0))))

Invalid input numbers in B4 will return the phrase
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"IF I only had a brain for IF statements" <IF I only had a brain for IF
wrote in message
...
I've read the previous postings regarding the use of more than 7 if then
statements, and tried the following:
CELL D2 =IF(OR(B4=4,B4=5,B4=6,B4=14,B4=16),"Mike
Pelosi",IF(OR(B4=1,B4=2,B4=3,B4=13,B4=24),"Nick
Re",IF(OR(B4=7,B4=8,B4=22,B4=35),"Nicole
Hughes",IF(OR(B4=20,B4=25,B4=9),"Kim
O'Connor", "D3"))))
CELL D3 =IF(OR(B4=19,B4=21,B4=26,B4=15,B4=18),"Justin
Eldredge",IF(OR(B4=23,B4=30,B4=34,B4=36),"Janet
Ward",IF(OR(B4=11,B4=12),"Jen
Ferrarraccio")))
CELL D4 =IF(B4=D2),D2,D3
I am attempting to make the formula for column D (fromD4 down) assign a
"Person" to the cell based on the number located in column B. However,
this
doesn't seem to be working. I would greatly appreciate some help!
Thanks,
Mike

You're welcome, Mike !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"IF I only had a brain for IF statements"
soft.com wrote in message
...
Thanks Max, Biff, Roger and Shakespeare, for all of your help. Everything
is
in working perfectly. Have a great day!
~Mike

I knew there was a function, but just couldn't remember what it was. The
text function works perfectly for my list.
Many Thanks,
JoAnne


--
Shakespeare
------------------------------------------------------------------------
Shakespeare's Profile: http://www.excelforum.com/member.php...o&userid=21552
View this thread: http://www.excelforum.com/showthread...hreadid=392766

You're welcome, JoAnne !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Shakespeare"
wrote in message
...

I knew there was a function, but just couldn't remember what it was. The
text function works perfectly for my list.
Many Thanks,
JoAnne