How can I do this? Is it possible with Excel?

D

dodsonmd

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

John McGhie

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]
 
D

dodsonmd

Thanks a bunch, John. I'll give it a try tomorrow. If you don't mind, I'll sent you what I come up with. I'll use the email listed in your response.
John
 
D

dodsonmd

John,
I couldn't wait to try. It works great!! Absolutely brilliant!
One more question. How can I return to the virgin roster without any conditional formatting applied to any cell? Is there some way to assign a function or formatting instruction to a button to clear any conditional formatting?
Once again, many thanks,
John
 
D

dodsonmd

To reset the form so that there is no visible conditional formatting I added a blank cell to the list, and an additional criteria of 'no format' to the conditional formatting of the roster range. That uses only two criteria and works great. Thanks so much.
John
 
J

John McGhie

That would work :)

I added a "Blank" entry at the beginning of the list of numbered names.
That causes the VREF to return "N/A" which the conditional format doesn't
match.

Glad it worked for you :) Now go to bed!

Cheers


To reset the form so that there is no visible conditional formatting I added a
blank cell to the list, and an additional criteria of 'no format' to the
conditional formatting of the roster range. That uses only two criteria and
works great. Thanks so much.
John

--

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]
 
D

dodsonmd

Bed? Sleep is really over-rated. This Excel stuff is just tooo much fun.

It all works great, but do you know of any way to anchor the list box? I have the properties set to don't move and don't resize, but the box seems to shift anyway. If I move the sheet into another workbook, or duplicate it to save an original version, I'm constantly moving the list box back over the range of names. Not a big deal, but irritating.
Thanks, John
 
D

dodsonmd

You know.....sometimes late at night my little mind just gets going too fast.
I wonder if I could do something like this, with the button or text-triggered conditional formatting in a table in an Acrobat/pdf file?
Oops, sorry, this is a Microsoft forum. I'll head over to Adobe.
/J
 
J

John McGhie

Hi Mark:

Yeah, well, this is a little fiddly. Basically, if you "Lock" an object,
and then set its properties to "Move but don't resize" with cells, the
object will move with the cell that contains its top left corner.

Lotsa good stuff here:
http://office.microsoft.com/en-us/excel/HP052034511033.aspx?pid=CH0625280910
33

Cheers


Bed? Sleep is really over-rated. This Excel stuff is just tooo much fun.

It all works great, but do you know of any way to anchor the list box? I have
the properties set to don't move and don't resize, but the box seems to shift
anyway. If I move the sheet into another workbook, or duplicate it to save an
original version, I'm constantly moving the list box back over the range of
names. Not a big deal, but irritating.
Thanks, John

--

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]
 
D

dodsonmd

Thanks, John, that helps.

I've noticed as a work on this file, an issue with name definitions has come up that's driving me crazy.

Here's the situation. I have sheet A with my roster, calcs, etc which works great with the name definitions for that sheet. I then want to duplicate that sheet, making sheet B, by option-click and drag. I rename the sheet, but the calcs don't work as their definitions refer to Sheet A.

If I open the the name definition dialog, select the name, select the entire range in the 'refers to' box, and then click the sheet B title tab, it changes the reference to the ranges in sheet B (which are exactly the same numerically as in sheet A, just in a different worksheet). Is there an easier to change the references in all the names without having to change each one individually? Maybe some way to make the original definitions (in sheet A) in some fashion that when I duplicate the sheet the names will travel with the sheet?

Hope that's not too confusing. I really appreciate your expertise with Excel, you've already helped me tremendously. And I really do enjoy working with Excel, as geeky as that may sound.

John
 
J

John McGhie

Hi John:

Well, the "best" way of working with data names is "Keep the *^*@%$ing
things unique across the workbook!" :)

Study Chip Pearson's technique for creating "Sheet-scoped Defined Names"
here:
http://www.cpearson.com/excel/DefinedNames.aspx

Then just DON'T do it :) The potential for getting yourself into just
about all the trouble there is, is insignificantly different from "infinity"
:)

Cheers


Thanks, John, that helps.

I've noticed as a work on this file, an issue with name definitions has come
up that's driving me crazy.

Here's the situation. I have sheet A with my roster, calcs, etc which works
great with the name definitions for that sheet. I then want to duplicate that
sheet, making sheet B, by option-click and drag. I rename the sheet, but the
calcs don't work as their definitions refer to Sheet A.

If I open the the name definition dialog, select the name, select the entire
range in the 'refers to' box, and then click the sheet B title tab, it changes
the reference to the ranges in sheet B (which are exactly the same numerically
as in sheet A, just in a different worksheet). Is there an easier to change
the references in all the names without having to change each one
individually? Maybe some way to make the original definitions (in sheet A) in
some fashion that when I duplicate the sheet the names will travel with the
sheet?

Hope that's not too confusing. I really appreciate your expertise with Excel,
you've already helped me tremendously. And I really do enjoy working with
Excel, as geeky as that may sound.

John

--

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top