Choose rather than If Statement

[iainmunro]

Morning

I have the following If Statement which works:

=IF([Set Personnel]="TBA","Red",IF([Set Personnel]="Kevin Badduke","Coral",IF([Set Personnel]="Bryce Carnahan","PaleGreen",IF([Set Personnel]="Paul Farquhar","LightSkyBlue",IF([Set Personnel]="Jordan McNeil","DarkGoldenRod",IF([Set Personnel]="Norm Fischer","HotPink",IF([Set Personnel]="Neal Harker","MediumSlateBlue",IF([Set Personnel]="Vic Wall","GreenYellow",""))))))))

However I have more people to add and I am at the Max in terms of nested If Statements. I have tried using the & to add more, but that does not seem to work.

How would I use the Choose Statement in this scenario ?

Iain

 

[Joe4]

I would recommend creating a lookup table and using the VLOOKUP function instead. It is much easier to maintain if you need to add/change/delete things to it in the future. You would just need to update your data table, and not all the formulas.

Take a look at Excel's built-in help files on the VLOOKUP function for details and examples.

 

[delaneyjm]

Have you considered a lookup table?

You should be able to do something like =VLOOKUP(Person_Name,Lookup_Table,2,FALSE)

Where Person_Name is the name of the person/cell reference you are looking up, the lookup table would have the list of names and colors, and you would return the color.

 

[iainmunro]

Hi There

I am using the formulas in Sharepoint, so cannot use Lookup - if or Choose.

Iain

 

[Joe4]

I am using the formulas in Sharepoint

Ah yes, a critical detail you left out of the original question!

Have a look here at this solution I found via Google:
http://social.technet.microsoft.com...l/thread/3cf0cdb4-921d-46b9-be6a-9d9c50e45af8

 

[iainmunro]

Hi There

THanks for the link - this does not work.

The lookup function in Sharepoint is just that, when you pick something, you can lookup the information in another list.

Using the If statement, creates the colours I need automatically.

This is just an Excel question as the Excel functions in most cases work within Sharepoint.

Have you ever used the Choose Function ?

Iain

 

[Joe4]

Did you read all the way down to the second to last response in that link I provided you? That solution does not use LOOKUP.

No, I never really use the CHOOSE function. Did you take a look at Excel's built-in help files for details and examples? They usually do a good job on explaining their functions.

Are you trying to do Conditional Formatting or something?
Are you really trying to return the names of those colors, or are you trying to highlight things in those colors?

 

[iainmunro]

Hi There

Yes I looked at all of the responses in the link.

Basically what I am trying todo is change the field Set Personnel to colour indicated in the statement.

Iain

 

[Joe4]

Did you look at Excel's help files on the CHOOSE function?

You will also be able to find a lot more examples and instruction on how to use this by Googling "Excel Choose function".