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
*	*	*	*
*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*

<colgroup><col style="font-weight: bold;"><col><col><col><col><col></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Table[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]TM 1[/TD]

[TD="bgcolor: #FFFF00"]TM 4[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]TM 2[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]TM 3[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]TM 4[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]TM 5[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]TM 6[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]TM 7[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]TM 8[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]TM 9[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]

[TD="bgcolor: #00CCFF, align: center"]4[/TD]

</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.