Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a drop down list that will fill in mutilple cells.... | Excel Discussion (Misc queries) | |||
HOW DO I SET UP DROP DOWN CELLS TO AUTO FILL AS I START TYPING IN. | Excel Discussion (Misc queries) | |||
Auto Fill Cells, When Choosing From Drop-Down List... | Excel Worksheet Functions | |||
Creating an auto fill or drop down | Excel Discussion (Misc queries) | |||
create a fill in template to tab to fill in cells | Excel Discussion (Misc queries) |