Nesting Issue on If statement

[mmeier363]

I 24 Team members that I have to place into a schedule. I have 182 cells that this has to process thorough.
in the schedule I want to place a #1 through 24 in cells and when I do it turns another cell to state TM 1 through TM 24
I ran into a nesting issue when getting past 10 TM
here is what formula I was entering but got the nesting error:
=IF(O16=1,"TM 1",IF(O16=2,"TM 2",IF(O16=3,"TM 3",IF(O16=4,"TM 4",IF(O16=5,"TM 5",IF(O16=6,"TM 6",...IF(O16=24,"TM 24","SHARE"))))))
I put in the ... for simplicity-

 

[AhoyNC]

Instead of all those nested IF's you could set up a table (could be on another sheet) and then use VLOOKUP.

Excel Workbook
	A	B	C	D	O
1	Table	*	*	*	*
2	1	TM 1	*	TM 4	*
3	2	TM 2	*	*	*
4	3	TM 3	*	*	*
5	4	TM 4	*	*	*
6	5	TM 5	*	*	*
7	6	TM 6	*	*	*
8	7	TM 7	*	*	*
9	8	TM 8	*	*	*
10	9	TM 9	*	*	*
11	*	*	*	*	*
12	*	*	*	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	*	*	*	*	*
16	*	*	*	*	4
Sheet

 

[mmeier363]

Instead of all those nested IF's you could set up a table (could be on another sheet) and then use VLOOKUP.

*	A	B	C	D	O
1	Table	*	*	*	*
2	1	TM 1	*	TM 4	*
3	2	TM 2	*	*	*
4	3	TM 3	*	*	*
5	4	TM 4	*	*	*
6	5	TM 5	*	*	*
7	6	TM 6	*	*	*
8	7	TM 7	*	*	*
9	8	TM 8	*	*	*
10	9	TM 9	*	*	*
11	*	*	*	*	*
12	*	*	*	*	*
13	*	*	*	*	*
14	*	*	*	*	*
15	*	*	*	*	*
16	*	*	*	*	4

<colgroup><col style="font-weight: bold;"><col><col><col><col><col></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =VLOOKUP($O$16,$A$2:$B$10,2,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

I like this a lot -I was trying to think of how to write a formula or a macro but I have done this before and should have thought of it- this is why many heads are better than one - Thanks for the Idea-

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.