Conditional Formatting colums

[Jack Deuce]

I have 12 columns that contain month-end totals for various food
items. Currently the end of month is entered on the last day of the
month which requires I leave those 12 cells unprotected requiring the
managers to enter them manually. I would like to enter the month-end
dates into the 12 cells and have that date cell and all month-end
items for that month automatically display when that month-end has
been reached. Is this possible with CF? Data range is D3:O108 with
d3, e3, f3, ... the month end dates and d4:O108 the food item totals.

Thanks to all..

 

[Gord Dibben]

Quoted from your post.................

" I would like to enter the month-end
dates into the 12 cells and have that date cell and all month-end
items for that month automatically display when that month-end has
been reached"

What do you mean by "display" and what does CF have to do with it?

Did you mean to say...............when all fields have been filled for the month
I would like the cells to display a particular color?


Gord Dibben MS Excel MVP

 

[joeu2004]

I have 12 columns that contain month-end totals for
various food items. [....]
 I would like to enter the month-end dates into the 12
cells and have that date cell and all month-end items
for that month automatically display when that month-end
has been reached.  Is this possible with CF?  Data range
is D3:O108 with d3, e3, f3, ... the month end dates and
d4:O108 the food item totals.

Well, you could use Conditional Formatting. Basically, you would set
the Font Color to be the same as the background (Pattern) color under
some condition. One problem: if you change the background color, you
would need to change the CF Font Color manually.

But that is the hard way.

I presume that the formula in D3, E3, etc is (or should be) of the
form:

=SUM(INDEX($D$4:$O$108,0,COLUMN()-COLUMN($D$2)))

One simple solution: use the Custom format 0;-0; (note: the last
semicolon is part of the format). If your sum is not an integer, you
can use 0.00;-0.00; as well as some other Custom formats.

Thus, if the sum is zero, the cell appears blank.

I suspect that will do the trick for you. I assume that the sum is
zero only when there are no data in the corresponding column.

But if the sum of the populated column could be zero, the following
might work for you:

=IF(COUNT(INDEX($D$4:$O$108,0,COLUMN()-COLUMN($D$2))),
SUM(INDEX($D$4:$O$108,0,COLUMN()-COLUMN($D$2))),"")

Thus, the sum (even zero) is displayed as long as, and only if, here
is at least one number in the range.

PS: The use of INDEX in this manner is preferred over OFFSET or
INDIRECT because the latter two are volatile functions, whereas INDEX
is not.

 

[Jack Deuce]

The month-end dates are entered in d3...o3 which when entered will
cause a VLOOKUP for all the food items to be "displayed" on that sheet
(=IF(K$3>1,VLOOKUP(K$3,lookup_apple_slices,4,1),"")) where $K$3 is
that month-ends date, 02/28/11. There are 104 or so of these food
items that are contained in other worksheets within the workbook that
keep track of the food item, eg, apple slices, Raisins, Salsa, ...

If I could enter the month-end dates for the current year in these 12
cells, D2 thru O3, and have CF show these totals when the current date
is NOT GREATER than those dates in D3-O3, I could protect these cells
eliminating any chance of an incorrect date being entered.

Hope this helps...

Thanks.