in this workbook
you can select a number from this drop-down list and that number of rows
appears below
and then from each blue cell you can select a color
and that color appears as a sample
in the adjacent cell. This doesn't require any macros
it's done with conditional formatting and data validation
I'm going to show you how you can set it up. So here's another workbook
and I just set up the headings and then on another sheet I've got lists. So I've
got a list of 8 numbers
and I'm using three colors for this demo
We're going to create named ranges based on these lists.
So I will select all the numbers, click
in the Name box, and type a one-word name
I'll call this NumList, press Enter
so that range is named. And then I'll call this
ColorList, so again click in the Name box
ColorList, press Enter
now we have two names and you can see them if you click the arrow in that
name box.
Going back to this demo sheet, this is where we're going to set up those lists
and in this box we're going to use data validation
to create a drop-down with those numbers so on the Ribbon I'll go to the Data tab
click data validation
I want a list in that cell so I'll select list
and in the source box I have to tell it which list to use
the quickest way to get the list is to press the f3 key
that opens up paste name. I want a number list
called NumList. Click OK, click OK
Now we have a drop-down list in that cell. To make it apparent that you should do
something in that cell
I'm going to color the cell with blue fill color
just a light blue color here. We're going to let people
select up to 8 colors. I'm going to select
eight cells here, and up in the Name box you can see the number of rows that I have
selected, so right now I've got 8 rows
by one column, and for those cells
I'm going to use data validation again
so data validation. Again, we'll allow
a list and click in the source box
Press f3 and this time we want the color list.
So select that, click OK, and click OK
If I click on this cell, you can see that it has
the three colors available in the drop-down. I'm going to just type the
numbers 1 through 8 here
so I'll start with 1 and 2, and then select those
and drag down, until I can see that 8
is going to be the last number so we have the options
available. What I want to happen is when someone selects a number of options
those numbers appear over here
with a box beside them with blue fill color
and a border, so people know they should select a color. When the sheet is first
opened
if they haven't selected a number of options, I want nothing to appear
down here,
and for these numbers to disappear
I'm just going to make them a white font. So I'll go to the Home tab
and for the font color I'll pick white, and the fill color
I'll also make that white. Next I'll use conditional formatting
in these cells, so that they get a border, and the font changes to black
whenever the number here is less than or equal to this number
So, if you select 4, numbers 1 through 4 should appear
On the Home tab, click
Conditional Formatting, New Rule
We're going to create a formula, so click Use a Formula
and in this box I'm going to type my formula.
Start with an equal sign. I'll check the worksheet to see which is the active
cell
and I can see that B5 has its name in the Name box
and it's white where the others are gray, so that's the
active cell and I want to compare the active cell
to this number where we selected the number of options
So, =B5
=B5<=
Then I'll click on C2 and it gets $ signs. It's an absolute reference. I want
every cell to compare itself with that cell.
but I want B5 in this case but B6 here and so on, so this
doesn't get dollar signs, and this does.
So if it's less than or equal to that,
I'm going to format it, and it should get a border
an outline border and the font
instead of automatic, I'll make it black, then click OK
click OK, and now we can see those cells
are working. If I change this to a
3, we go down to three cells visible here.
Next we're going to do conditional formatting on this color column
So I'll select the eight cells here
and I'm going to again go to the Home tab
Conditional Formatting, New Rule
for these cells again we're going to use a formula
and this time we can see that c5 is the active cell
And in this column, we're going to check and see
if the number to the left is less than or equal to
the number in cell C2.
=B5<=$C$2
So it's really the same formula that we used
in the option column and in this case
we're not checking the cell that we're in, we're checking the cell to the left
we want to see
if you're in this color cell what option number is over here, and is it less than
the number of options we have selected. Again we'll click Format
and we want a border, an outline border.
This time we don't want to change the font, we're going to change the Fill
And I'll use a light blue
and click OK and OK.
So now we've got 3 options selected, so 3 of these cells
are ready for you to enter some data
I'll click here and select yellow.
So I'll just fill in the other 2 here, just so we can see all 3 colors
that we're going to be using, and then we're going to fill in conditional
formatting in this sample
column. I'm going to select the 8 cells
and in this cell we want it to show the selected color.
I'll go up to the Home tab, Conditional Formatting
New Rule. We'll use a formula
and in this cell I'm going to check to see if Red was selected.
So, = and we're going to check cell C5
and see if it's equal to Red, and I'll put that in double quotes.
=C5="red"
it doesn't matter if it's upper or lower case
and for that I'm going to click Format. We'll put a border
outline, and the Fill for this should be red
so click OK and we now have our first rule
I'll click OK again, and it's working there
I'm going back and creating a rule for yellow
conditional formatting new rule
formula, and in here we're going to check and see if cell C5
is equal to yellow, because D5 is still our active cell
=C5="yellow"
and click Format, choose the yellow fill color
and put on a border, click OK
and OK. We can see our yellow is working
and the final one will be blue. Conditional Formatting,
New Rule, Formula
=C5="blue"
Click Format. Put on an
outline border, and choose a Fill color that's blue
click OK, and OK. So now
we can see that each of these works if I change this
to red, it shows red here. I'm going to change the number of options and
show a couple more. So if I change this to 5,
two more of these color cells have turned blue
I can choose yellow, and it fills in that with yellow
Blue here, fills that in with blue. So with conditional
formatting and data validation, we can make numbers appear, we can make
cells change color, and we can
create drop-down lists, so you can select items
For more Excel tips and tutorials, and to download the sample file for this video
please visit my Contextures website at www.contextures.com