WISEST.FOOL
New Member
Ok, I am new at this. To give you some background I am an infantry soldier who has been stuck in operations. I am like a fish out of water, but I am not completely clueless. I can follow basic formulas. My boss has requested a spreadsheet that displays which objectives require action in a given day/week projected out through the remainder of the fiscal year. So two quarters worth of dates. I have two options. create a spreadsheet that i have to go through and change and reverify each date over a long @$$ spreadsheet manually, or have a spreadsheet that updates itself when i add a date to it. My idea is to shade a cell on the date of the objective somewhere out in the gantt chart to the left. I have color coded each section in the first part of the spreadsheet and assigned each section a number 1-7 that i will assign to a color with conditional formatting over the gantt portion. the best formula i could think of is =IF(AND(range of cells=date cell),number correlation to section,0) I would have to have 7 formulas in each cell one for each section i want but then i can conditional format the cell color based on a number 1-7 which represents a different objective due on that date.
in summary if i enter a date on the first part of the spreadsheet i want the number of the section i added the date to to appear on the date of the gantt chart to the left in the spreadsheet. The cells in the gantt chart will be conditionally formatted so that if the value is 1 then the color is orange which corresponds to an planning objective due on that date. or yellow for 2 which represents a political objective, etc.
there are six rows for each mission the first row being the IDEAL objective due date expressed in days prior to mission start Example D-15 would be 15 days prior to mission start. the second row is the actual date. which is all based off of D-0 in the operational section. the third row is a condition (ie. Submitted, conducted) and the Fourth row is the date the condition was met. example: Soandso Brief(header), D-120(row 1), 3 mar 15(row 2), conducted(row 3), 4 mar 15(row 4).
I feel that this should be possible. I have asked my J6 section (communication/computer section) and they are clueless. I have messed with this for over 6 hours now and learned alot of interesting formulas, but not how to make this work.
If anyone can help it would be greatly appreciated. I know there are some really excellent excel spreadsheets out there. If not I have at least learned alot on these forums for other spreadsheets i am sure i will have to make.
P.S. I have had to label some things differently than what they should be to unclassify them. I just came up with stuff. Its silly I know but the actual spreadsheet has stuff I can't spread about. Also, it is labeled horse blanket, because after i print it out and tape it together i figure it will be as big as a horse blanket.
in summary if i enter a date on the first part of the spreadsheet i want the number of the section i added the date to to appear on the date of the gantt chart to the left in the spreadsheet. The cells in the gantt chart will be conditionally formatted so that if the value is 1 then the color is orange which corresponds to an planning objective due on that date. or yellow for 2 which represents a political objective, etc.
there are six rows for each mission the first row being the IDEAL objective due date expressed in days prior to mission start Example D-15 would be 15 days prior to mission start. the second row is the actual date. which is all based off of D-0 in the operational section. the third row is a condition (ie. Submitted, conducted) and the Fourth row is the date the condition was met. example: Soandso Brief(header), D-120(row 1), 3 mar 15(row 2), conducted(row 3), 4 mar 15(row 4).
I feel that this should be possible. I have asked my J6 section (communication/computer section) and they are clueless. I have messed with this for over 6 hours now and learned alot of interesting formulas, but not how to make this work.
If anyone can help it would be greatly appreciated. I know there are some really excellent excel spreadsheets out there. If not I have at least learned alot on these forums for other spreadsheets i am sure i will have to make.
P.S. I have had to label some things differently than what they should be to unclassify them. I just came up with stuff. Its silly I know but the actual spreadsheet has stuff I can't spread about. Also, it is labeled horse blanket, because after i print it out and tape it together i figure it will be as big as a horse blanket.
