Drop Down to Fill Cells
 

I have built a scheduling worksheet/shared workbook for my office, There is a
drop down that lists from 1-9 available slots, with cells below coordinating
with those spots. I want to know if i can set the drop down to either block
out the extra cells if say a person were to set us at 2 possible for the day,
then the other 7 cells would be a different color, or blocked off, or
something. Can anyone help with this?
--
NealMed

Drop Down to Fill Cells
 

Assuming the dropdown lists are in cell A1:G1 --

Select cell A2:G10 (cell A2 is the active cell)
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =A$1<ROW()-1
Click the Format button, and choose a colour for the cells that can't be
used, e.g. dark grey
Click OK, click OK

Choose DataValidation
For Allow, choose Custom
In the Formula box, type: =A$1=ROW()-1
Click OK

NealMed wrote:
I have built a scheduling worksheet/shared workbook for my office, There is a
drop down that lists from 1-9 available slots, with cells below coordinating
with those spots. I want to know if i can set the drop down to either block
out the extra cells if say a person were to set us at 2 possible for the day,
then the other 7 cells would be a different color, or blocked off, or
something. Can anyone help with this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

Thanks it partly worked, the problem i'm having now, is that it won't
acctually follow the numbes, if i say select 7, it will only clear the red
color from the top two cells, but not the others, if you click on them to
type you can, but you have to click on them to get them to remove the shaded
color.
--
NealMed


"Debra Dalgleish" wrote:

Assuming the dropdown lists are in cell A1:G1 --

Select cell A2:G10 (cell A2 is the active cell)
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =A$1<ROW()-1
Click the Format button, and choose a colour for the cells that can't be
used, e.g. dark grey
Click OK, click OK

Choose DataValidation
For Allow, choose Custom
In the Formula box, type: =A$1=ROW()-1
Click OK

NealMed wrote:
I have built a scheduling worksheet/shared workbook for my office, There is a
drop down that lists from 1-9 available slots, with cells below coordinating
with those spots. I want to know if i can set the drop down to either block
out the extra cells if say a person were to set us at 2 possible for the day,
then the other 7 cells would be a different color, or blocked off, or
something. Can anyone help with this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

What range of cells did you format? What formula did you use for
conditional formatting, and what formula for Data Validation?

NealMed wrote:
Thanks it partly worked, the problem i'm having now, is that it won't
acctually follow the numbes, if i say select 7, it will only clear the red
color from the top two cells, but not the others, if you click on them to
type you can, but you have to click on them to get them to remove the shaded
color.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

To make sure that i had the correct idea, i started with a new worksheet, and
used the exact cells and formula that you prescribed in the first answer. It
did block them the color i wanted, i changed the number from 1 to 2 and it
worked, then when i went to 3, it didn't change. If i double clicked on the
cell for the 3rd one, it would clear and i could use it, but i could not use
the 4th, so i set it to the 4th, and it didn't change, but i could double
click the 4th cell and the same occured, and i could use it, and so on.
--
NealMed


"Debra Dalgleish" wrote:

What range of cells did you format? What formula did you use for
conditional formatting, and what formula for Data Validation?

NealMed wrote:
Thanks it partly worked, the problem i'm having now, is that it won't
acctually follow the numbes, if i say select 7, it will only clear the red
color from the top two cells, but not the others, if you click on them to
type you can, but you have to click on them to get them to remove the shaded
color.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

I'm not sure which cells you're double-clicking on, or what's in those
cells when you double-click on them. Can you describe what you're doing?

Is the worksheet clear when you start, excepting for the formatting, or
are there values in any of the cells?

NealMed wrote:
To make sure that i had the correct idea, i started with a new worksheet, and
used the exact cells and formula that you prescribed in the first answer. It
did block them the color i wanted, i changed the number from 1 to 2 and it
worked, then when i went to 3, it didn't change. If i double clicked on the
cell for the 3rd one, it would clear and i could use it, but i could not use
the 4th, so i set it to the 4th, and it didn't change, but i could double
click the 4th cell and the same occured, and i could use it, and so on.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

Starting with a new worksheet document. Using the cells and Formula exactly
as you decribe, is what i did. I wanted to just see it in action before
placing it into my worksheet. This is what happens. If i click on the drop
down and choose either 1 or 2, then 1 or 2 of the cells turn white and are
usable, (i chose red as my block off color). when i choose 3 - 9 from the
drop down menu, it will not clearthe 3rd - 9th cells to white. If i select
say 4 from the drop down menu, then 2 will clear, leaving everything else
still red. Where the 3rd and 4th cells are that should have turned white, if
i double click them they will turn to white and let me place text there, and
the 5th, 6th, 7th, etc cells will not clear to white with the double click.
They remain blocked off as they should. That is the problem that i'm having
with this one. Thanks for being so stead fast in helping me. :)
--
NealMed


"Debra Dalgleish" wrote:

I'm not sure which cells you're double-clicking on, or what's in those
cells when you double-click on them. Can you describe what you're doing?

Is the worksheet clear when you start, excepting for the formatting, or
are there values in any of the cells?

NealMed wrote:
To make sure that i had the correct idea, i started with a new worksheet, and
used the exact cells and formula that you prescribed in the first answer. It
did block them the color i wanted, i changed the number from 1 to 2 and it
worked, then when i went to 3, it didn't change. If i double clicked on the
cell for the 3rd one, it would clear and i could use it, but i could not use
the 4th, so i set it to the 4th, and it didn't change, but i could double
click the 4th cell and the same occured, and i could use it, and so on.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Drop Down to Fill Cells
 

If you'd like to email me your sample workbook, I could take a look at it.
Remove the XSPAM from my email address:

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


"NealMed" wrote:

Starting with a new worksheet document. Using the cells and Formula exactly
as you decribe, is what i did. I wanted to just see it in action before
placing it into my worksheet. This is what happens. If i click on the drop
down and choose either 1 or 2, then 1 or 2 of the cells turn white and are
usable, (i chose red as my block off color). when i choose 3 - 9 from the
drop down menu, it will not clearthe 3rd - 9th cells to white. If i select
say 4 from the drop down menu, then 2 will clear, leaving everything else
still red. Where the 3rd and 4th cells are that should have turned white, if
i double click them they will turn to white and let me place text there, and
the 5th, 6th, 7th, etc cells will not clear to white with the double click.
They remain blocked off as they should. That is the problem that i'm having
with this one. Thanks for being so stead fast in helping me. :)
--
NealMed


"Debra Dalgleish" wrote:

I'm not sure which cells you're double-clicking on, or what's in those
cells when you double-click on them. Can you describe what you're doing?

Is the worksheet clear when you start, excepting for the formatting, or
are there values in any of the cells?

NealMed wrote:
To make sure that i had the correct idea, i started with a new worksheet, and
used the exact cells and formula that you prescribed in the first answer. It
did block them the color i wanted, i changed the number from 1 to 2 and it
worked, then when i went to 3, it didn't change. If i double clicked on the
cell for the 3rd one, it would clear and i could use it, but i could not use
the 4th, so i set it to the 4th, and it didn't change, but i could double
click the 4th cell and the same occured, and i could use it, and so on.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html