A simple way to get where you want to go, is to create a series of CHOOSE functions, each with 12 arguments and one input. With Function AutoComplete, you can quickly write the proper formula syntax. The input for all the CHOOSE functions is the MONTH number. The argument list for each CHOOSE is the values of the 12 months to which that CHOOSE refers. From easily detecting the functions that you want to use to getting help completing the formula arguments, you will be able to get formulas right the first time and every time. You'll have a CHOOSE for each of your spending categories, and another CHOOSE for each of the same categories in the BUDGET table.For that you need to create a table structure with columns like Activity, start and end day, day 1, 2,3, etc. After that whenever a day falls between start and end day for a corresponding activity, you need to highlight that row. For that you need to identify whether a day falls between start and end. We can do that with the below formulas,=IF(AND(F$8>=$D9, F$8<=$E9),"1",""). Which means, whenever, the day number represented on the top row is between start and end we will in 1 in the corresponding cell. After that whenever the cell value is 1, you will just need to fill the cell with a favorite color and change the font to same color, so that you don't see anything but a highlighted cell, better still, whenever you change the start or end dates, the color will change automatically. That's it and your job is done.
Bookmarks