Well, Excel "should" be the program to do this, but "Excel 2008" is NOT,
because it doesn't have the function you need ‹ multiple-condition
formatting. The next version will have.
Conditional Formatting should do this, but it will only allow three
conditions, and you need more than that.
But we can fudge our way around that limitation using a List Box:
First, Set up a column containing all the valid names. I Used B28 to B61.
It doesn't matter where you put the names (can even be UNDER the List Box
you are about to add!) but you will need a blank column to the left of it.
Now, number the rows in your names column, 1, 2, 3, 4... Just use AutoFill
to put them in.
Now display the Forms toolbar and add a List Box. Drag it so it is long
enough to hold all the names (and maybe, wide enough to hide the column of
names).
Right-click the List Box, choose Format Control, and on the Control tab set
the Input Range to be the cell reference of your list of names (click in the
first cell and drag down to the last).
Set the Cell Link to any convenient cell. I used $B$2.
Now, if you click on any name in your list, the sequence number in the list
of the name you clicked will be entered in cell B2.
Now you need a formula to return the text of the selected name (because
Conditional Formatting can only resolve a true or false, it can't test a
value for you.
I used =VLOOKUP(B2,A28:B61,2,FALSE). I put the result in cell B3.
My numbers are in cells A28 to B61. The List Box will return the number of
the item selected in B2, and the names are in column 2 of the table A28:B61.
So VLOOKUP looks down Column A until it finds, in this case "13", looks
across to Column 2, and returns "Joseph"
Now, select all of the roster cells, and then Format>Conditional
Formatting...
Set Conditional Format 1 to be "Cell Value" "Is equal to" and "=$B$3". Set
the Format>Pattern to be green, and OK your way out.
Now, any roster cell that contains the name you select in the list box will
be highlighted in green.
OK, it's a laborious way to go about it, but it will get you close enough
for now
Cheers
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Not sure if Excel is the right program for this, but here's my question.
I have a work schedule, size is 7 columns, 30 rows. Each cell contains a
single word name. I would like to have a legend, or small side grid,
containing a vertical list of the names that would allow me to select a cell
in that legend, and have that name(value) searched in the schedule grid and a
specific fill color applied to any cell containing that name.
So a name could be selected in the legend, and any day/shift in the schedule
would then be marked with a fill color to identify easily which days are to be
worked.
Hope this is not too confusing. Any suggestions appreciated.
--
The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!
John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:
[email protected]