Why do I loose color in cells with this string?
 

The below formula is used to subtract inventory required from on hand
inventory to show an inventory balance...

=D5-S5-W5-AA5-AE5-AI5-AM5-AQ5-AU5-AY5-BC5-BG5-BK5-BS5-BW5-CE5-CI5-CM5-CQ5-CU5-CY5-DC5-DG5-DK5-DO5-DS5-DW5-EA5-EE5-EI5-EM5-EQ5

When clicking on the formula bar, all associated cells are color coded.
However, when adding a cell to the formula, for example adding BO5 in between
BK5 and BS5, I loose the color in each cell. The formula still works, but
color in cells is lost. How can I get it back?

Thank you in advance

Why do I loose color in cells with this string?
 

There is a limit to the number of ranges Excel will highlight. I don't know
off-hand just what that limit is but apparently you've exceeded that limit.

--
Biff
Microsoft Excel MVP


"Dale Polichetti" <Dale wrote in
message ...
The below formula is used to subtract inventory required from on hand
inventory to show an inventory balance...

=D5-S5-W5-AA5-AE5-AI5-AM5-AQ5-AU5-AY5-BC5-BG5-BK5-BS5-BW5-CE5-CI5-CM5-CQ5-CU5-CY5-DC5-DG5-DK5-DO5-DS5-DW5-EA5-EE5-EI5-EM5-EQ5

When clicking on the formula bar, all associated cells are color coded.
However, when adding a cell to the formula, for example adding BO5 in
between
BK5 and BS5, I loose the color in each cell. The formula still works, but
color in cells is lost. How can I get it back?

Thank you in advance

Why do I loose color in cells with this string?
 

You *might* be able to use a more compact formula. I didn't look at every
cell you're referencing but the first several follow a pattern, every 5th
cell.

You might be able to use this:

=D5-SUMPRODUCT(--(MOD(COLUMN(S5:EQ5)+1,4)=0),S5:EQ5)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
There is a limit to the number of ranges Excel will highlight. I don't
know off-hand just what that limit is but apparently you've exceeded that
limit.

--
Biff
Microsoft Excel MVP


"Dale Polichetti" <Dale wrote in
message ...
The below formula is used to subtract inventory required from on hand
inventory to show an inventory balance...

=D5-S5-W5-AA5-AE5-AI5-AM5-AQ5-AU5-AY5-BC5-BG5-BK5-BS5-BW5-CE5-CI5-CM5-CQ5-CU5-CY5-DC5-DG5-DK5-DO5-DS5-DW5-EA5-EE5-EI5-EM5-EQ5

When clicking on the formula bar, all associated cells are color coded.
However, when adding a cell to the formula, for example adding BO5 in
between
BK5 and BS5, I loose the color in each cell. The formula still works, but
color in cells is lost. How can I get it back?

Thank you in advance