Stump the Panel: SharePoint Q&A

Hello:
I have developed a colored calendar based on info found on the pathtosharepoint.com website. All works great. I have a calendar for vacation time, have over 7 employees that I want to assign their own color to all on one calendar.
Sharepoint has a limitation of not being able to nest over 7 "if" statements.

Anyway of achieving what I am trying to do?
I am a novice at sharepoint and just starting to learn about calculated fields.

Thank you
Jeff

Jeff,
Use multiple calculated columns to handle the formula - using this approach you can get past the limits.

Example usage would be something like:
(This could be in the form of a "CHOOSE()" formula in a separate calculated column, or based on a "User" field - this is just example data)

User1 color = Red
User2 color = Blue
User3 color = Yellow
User4 color = Green
User5 color = Orange
User6 color = Tan
User7 color = Purple
User8 color = Gray

Create 2 calculated columns:

First column named "Color Calc 1-4" - enter in formula:

=IF([User Column]=User1,[Color]="Red",IF([User Column]=User2,[Color]="Blue",IF([User Column]=User3,[Color]="Yellow",IF([User Column]=User4,[Color]="Green",[Color Calc 5-8]))))

Second column named "Color Calc 5-8" - enter in formula:

=IF([User Column]=User5,[Color]="Orange",IF([User Column]=User6,[Color]="Tan",IF([User Column]=User7,[Color]="Purple",IF([User Column]=User8,[Color]="Gray",FALSE))))

When you use the above, you dont need to ever reference the "Color Calc 5-8" column because in the first calculated column, if its "FALSE" for each of the first 4 checks (User1-4), it will call the second column on its own to process the rest of the checks (User5-8). Once it finds a condition that evaluates to true, it will then take its "TRUE" value and pass it back to whatever originally called it (i.e. your initial calculation that is trying to determine what color to use).

Hope this helps,

- Dessie

Thanks Dessie.
1. I created a column called User Column and selected the radio button Choice. I entered all the Users 1-8 on separate lines.
2. When I attempt to create a calculated column named Color Calc 1-4 or Color Calc 5-8 and enter the respective code you provided, I get the SP error column cannot be found.

Any ideas?

Thank you
Eric

Ok - is it Jeff or Eric? :)

The code I provided, was really just a guideline of the logic...its probably best to create columns with simpler names (no spaces or special chars).
But, using literally what I provided should work as well.

Couple things to check:
When you create the calculated column, do you see the "User Column" listed under the available columns to choose from (on the right of the formula window)?
In your formula, are you entering in the column names with brackets [column name]? If you have a space in the column name you'll have to make sure and enter it in this way.

Other than that, the best thing to do would be to post back with each step you're doing so I can help you troubleshoot it by trying to duplicate what you're seeing.

- Dessie

My middle name is Eric and I use that sometimes.
BTW I am in SP 2003 with wss.

1. Created new calendar.
2. Create a column called UserColumn, pick "choice" radio button, entered User1, User2, User3...User8 for the choices, hit ok
3. Create a column called colorcalc1, pasted in formula you gave below Color Calc 1-4, changed all the [User Column] to [UserColumn]. I can see the UserColumn over to the right of the area you paste the formula into.

Still getting error "

The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column."

Dont see a way for me to paste an attachment with screen shots.

Jeff

Ok. As an FYI, its better if you state what version of SharePoint you're using initially so we have a idea of how better to assist...most of us primarily work with MOSS2007 / WSS 3.0 and there are siginificant differences in what will work between the different platforms.

I do have an old instance of SPS2003 (I think I'm one of the few moderators that still have it running) that I'll have to bring up and look to see if the multiple calculated column trick will work in, but I dont think I'll be able to get to it today.

For posting your steps, you can just do a text version of what you're doing.

I'll see if I can try building out something in SPS2003 on Monday and post back with what I find.

- Dessie

Sounds good.
I just confirmed the company I work for is on WSS = Windows Sharepoint Services 2003 (WSS 3.0).

What did you mean by posting text version? Not sure what that is or how to do that?

Have a good weekend.

Jeff

By text, I meant just detail the steps you take to create the lists and columns step by step (i.e. i click here, type in this here, paste in this here...etc)...not a biggie.

I started thinking about it and realized what I had done in my initial formula that is messing you up, so I decided to go ahead and try creating the list to test things.

Here's what I have:

Create a new "choice" column called "UserColumn" and enter in the choices:
User1
User2
User3
User4
User5
User6
User7
User8

Create a new calculated column and enter in the following formula:

=IF(UserColumn="User1","Color=Red",IF(UserColumn="User2","Color=Blue",IF(UserColumn="User3","Color=Yellow",IF(UserColumn="User4","Color=Green","Colorcalc2"))))

Note the "Colorcalc2" in quotes at the end - this is important to enter it in like this since we havent created the second column yet and it will throw an error if its not in quotes.

Create another new calculated column called "Colorcalc2" and enter in the following formula:

=IF(UserColumn="User5","Color=Orange",IF(UserColumn="User6","Color=Tan",IF(UserColumn="User7","Color=Purple",IF(UserColumn="User8","Color=Gray","FALSE"))))

After creating it, go back to the first calculated column and remove the quotes around the "Colorcalc2" reference at the end of the formula so it will now see the second column.

In each of the two formulas, you'll see that I've put the "User#" inside quotes. Since our values in the text column are text values (strings), you need to specify it that way so it recognizes the values. Also, think of the "Color=###" sections as placeholders that you'll use for your actual formula for generating the backgroud colors on the calendar - just replace each of those with the html that will be rendered from Christophe's scripts (those from his site at pathtosharepoint.com).

Now, when you create a new item on the list, when you choose "User1 - User4", it will get the color from the first calculated column, and when you choose "User5 - User8", each of the "IF" statements in the first column will return "FALSE", and since the last "FALSE" is a reference to the second calculated column, it will continue just as if you all of the IF's together in a single formula by continuing on in the the logic detailed in the second column. Once it finds an "IF" that evaulates to "TRUE", it takes the "TRUE" result (one of the colors), and passes it back to the first column where it will then be displayed.

I was thinking more psuedocode-like when I originally posted my formula, so apologize for not being clearer (my bad).

Hopefully this clears things up some,

- Dessie

er... Jeff, wss 3.0 is actually SharePoint 2007, I'd recommend that you double check which version you're using.
I hope Dessie's detailed answer is working for you. On my side, I'll soon publish another form that accepts more than 7 choices.

Dessie:
Thanks. Did exactly as you said. Good so far. Now when I go to Christophe's site, he says to create a calculate column called Color, load your "choices" in, and paste the code into the color column formula area.
So here is the code I got =IF([Color]="","Black",IF([Color]="User1","Red",IF([Color]="User2","Gold",IF([Color]="User3","Green",IF([Color]="User4","DarkBlue",IF([Color]="User5","DarkCyan",IF([Color]="User6","DarkRed",IF([Color]="User7","Gray",""))))))))

How do I get the User8 or beyond into this code?

Christophe: When will your greater than 7 code be available?

Thank you both.
Jeff