two inputs for one cell

kotting · Mar 19th, 2002, 01:50 AM

I am working on a spreadsheet for scheduling. For the "OFF" days I have validated the enteries using a list of initials for people. I want also to add an option for people to color code the cell to indicate: vacation, sick, personal, etc. I am stumped. Any help will be greatly appreciated. Thanks

Dave Hawley · Mar 19th, 2002, 02:06 AM

If you only have 3 Conditions you can use Conditional formatting, otherwise you will need some VBA code.

kotting · Mar 19th, 2002, 02:21 AM

I've tried using the conditional formatting. Already I have a list of validated entries for the cells. After the entry into the cell, I want a button to recognize the last cell selected and color it to a specific color. I will have 3 or 4 buttons each set to a distinct color.
I've tried using "last selected cell" and "Target.Interior.ColorIndex=3". Sorry for being such a novice but I am really interested in learning how this works.

Dave Hawley · Mar 19th, 2002, 02:38 AM

Do you mean:

ActiveCell.Interior.ColorIndex=3

Have your button run this code.

kotting · Mar 19th, 2002, 09:17 AM

Thank you. It worked beautifully.
Although I've run into another (I think much harder) problem. I arranged shifts into three categories: day, afternoon and night. There are three required people on each shift per day. BUT we have in place a shift rota that requires that an entry into one cell (at random) be copied to specific cells to fill the rota correctly.
I've looked a simple solution such as A1=C3,C4,C5 but that means for each cell I have to enter a different formula. I was thinking of something along the lines of: IF(A1=text,"1","0") Then using the 1 to fill the cells assigned - then i lose the specific text. (In this case initials in a list defined by data validation.) In this way the wrong text cannot be entered so I think it would be easy to copy to the other cells in the list. But the solution eludes me still. I hope I'm not asking too simple of questions. Thanks again

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 01:50 AM	#1
kotting New Member Join Date: Mar 2002 Posts: 17	I am working on a spreadsheet for scheduling. For the "OFF" days I have validated the enteries using a list of initials for people. I want also to add an option for people to color code the cell to indicate: vacation, sick, personal, etc. I am stumped. Any help will be greatly appreciated. Thanks

Mar 19th, 2002, 02:06 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	If you only have 3 Conditions you can use Conditional formatting, otherwise you will need some VBA code.

Mar 19th, 2002, 02:21 AM	#3
kotting New Member Join Date: Mar 2002 Posts: 17	I've tried using the conditional formatting. Already I have a list of validated entries for the cells. After the entry into the cell, I want a button to recognize the last cell selected and color it to a specific color. I will have 3 or 4 buttons each set to a distinct color. I've tried using "last selected cell" and "Target.Interior.ColorIndex=3". Sorry for being such a novice but I am really interested in learning how this works.

Mar 19th, 2002, 02:38 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Do you mean: ActiveCell.Interior.ColorIndex=3 Have your button run this code.

Mar 19th, 2002, 09:17 AM	#5
kotting New Member Join Date: Mar 2002 Posts: 17	Thank you. It worked beautifully. Although I've run into another (I think much harder) problem. I arranged shifts into three categories: day, afternoon and night. There are three required people on each shift per day. BUT we have in place a shift rota that requires that an entry into one cell (at random) be copied to specific cells to fill the rota correctly. I've looked a simple solution such as A1=C3,C4,C5 but that means for each cell I have to enter a different formula. I was thinking of something along the lines of: IF(A1=text,"1","0") Then using the 1 to fill the cells assigned - then i lose the specific text. (In this case initials in a list defined by data validation.) In this way the wrong text cannot be entered so I think it would be easy to copy to the other cells in the list. But the solution eludes me still. I hope I'm not asking too simple of questions. Thanks again