Hello all -
I can't seem to get the right combination of formulas, validation,formatting to work in my spreadsheet.
Here's what I'm trying to do, in text, and I'll provide a sample of the file as well.
My resources work on project sometimes in the field, sometimes remote, or are on training or vacation.
On our current tracker, we list the project, the engineer assigned to it, start and end dates. The rest of the tracker contains columns corresponding to days of the year. As we determine what are the dates for the project, we not only fill in start and end date, but then enter a code into the appropriate date column with an F, R, V, T (Field, Remote, Vacation,Training) and I have conditional formatting set up to automatically color the cell as appropriate to the code (Field is red, Remote is blue, Vacation is purple, Training is brown). This works great and give a great visual representation of how our projects align/overlap and who's where when, but is not elegant/efficient with 20 resources, and multiple projects.
The work in progress worksheet i'm playing with now combines date validation against start and end dates and automatically fills in the cells. The problem is that I lose the detail/conditional color coding formatting of the work code (F,R,V,T).
My working thought is to add a new column to the sheet which is titled "Work Type". In this column, the cells use data validation/drop down list for Field, Remote, Vacation, Training), and then use the conditional formatting in the data columns to color the cells appropriately. It _sort of_ works, but uhhh...not really the way I want.
Apologies for the long email. I wanted to give as much detal as possible, and I wasn't sure how to ask an "easy" question. Can one of you ninjas help me out, please? I'm open to either further help on the solutions I'm working on OR a brand new solution which fixes my problem. Thank you so much in advance!! Melissa
I can't seem to get the right combination of formulas, validation,formatting to work in my spreadsheet.
Here's what I'm trying to do, in text, and I'll provide a sample of the file as well.
My resources work on project sometimes in the field, sometimes remote, or are on training or vacation.
On our current tracker, we list the project, the engineer assigned to it, start and end dates. The rest of the tracker contains columns corresponding to days of the year. As we determine what are the dates for the project, we not only fill in start and end date, but then enter a code into the appropriate date column with an F, R, V, T (Field, Remote, Vacation,Training) and I have conditional formatting set up to automatically color the cell as appropriate to the code (Field is red, Remote is blue, Vacation is purple, Training is brown). This works great and give a great visual representation of how our projects align/overlap and who's where when, but is not elegant/efficient with 20 resources, and multiple projects.
The work in progress worksheet i'm playing with now combines date validation against start and end dates and automatically fills in the cells. The problem is that I lose the detail/conditional color coding formatting of the work code (F,R,V,T).
My working thought is to add a new column to the sheet which is titled "Work Type". In this column, the cells use data validation/drop down list for Field, Remote, Vacation, Training), and then use the conditional formatting in the data columns to color the cells appropriately. It _sort of_ works, but uhhh...not really the way I want.
Apologies for the long email. I wanted to give as much detal as possible, and I wasn't sure how to ask an "easy" question. Can one of you ninjas help me out, please? I'm open to either further help on the solutions I'm working on OR a brand new solution which fixes my problem. Thank you so much in advance!! Melissa
