Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

Thanks,

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy- paste
special- formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2)
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B1:D1) 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac


"Karim" wrote:

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

Thanks,

Hi again Karim,

If you are comparing the sum of the cells to the cell in column F then the
formula in the conditional formatting will be:-

=SUM($A1:$C1) $F1

Regards,

OssieMac

"OssieMac" wrote:

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy- paste
special- formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2)
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B1:D1) 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac


"Karim" wrote:

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

Thanks,

Hi Ossie,
Thanks for your reply, but this is not quite what I am trying to do (I
didn't explain it well). Your solution would work if I know the content of
the cells before hand, but what I am trying to do is to highlight the cells
as I update the inventory (cells A to E) and the requirements cell (F)
To generalize the criteria, what I am trying to do is something like this:
If $F1 0, shade $A1 and continue, else stop
if $F1$A1+0, shade $B1 and continue, else stop
if $F1$A1+$B1, shade $C1 and continue, else stop
if $F1$A1+$B1+$C1, shade $D1 and continue else stop
if $F1$A1+$B1+$C1+$D1+$E1, shade $E1 else stop

like this, anytime I update the cells content they should change. Also, this
should shade partially used cell (if the sum of two cells won't make it and
the sum of three cells is bigger than F1)

Is this still doable with conditional formating or I have to write a
subroutine for it? and how?
Thanks alot

Karim


"OssieMac" wrote:

Hi again Karim,

If you are comparing the sum of the cells to the cell in column F then the
formula in the conditional formatting will be:-

=SUM($A1:$C1) $F1

Regards,

OssieMac

"OssieMac" wrote:

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy- paste
special- formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2)
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B1:D1) 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac


"Karim" wrote:

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

Thanks,

Karim,

I've read what you wrote below and what you wrote originally,
but I can't quite make them conform with each other. Quoted far down
below, you say you want (for Row 1) all columns before column F
highlighted if the totals in column A through that column exceed
column F.

In your most recent reply, however, you are saying to "shade"
earlier columns whenever F1 is bigger (as opposed to smaller).
These two ideas seem at odds with one another. Which do you want?
By "shade" do you mean "highlight," which you used earlier, or its
opposite?

I thought from reading your original that I understood your
request. But your further, "better explained," post makes it all
less clear to me, I'm afraid.

I will propose what I think you want based mostly on your
original request.

Format cells A1 to E1 conditionally. Select "Formula Is".
Put this in as the formula:

=AND(SUM($A$1:$E$1)$F$1,SUM($A$1:E$1)-E$1<=$F$1)

Make the fill color for that condition your preferred highlight
color.

That's it! Now all cells starting with A1 through the FIRST
one that exceeds the value in F1 are highlighted, but not the
later columns in that row.

---
dman

================================================== ===========
In , Karim
spake thusly:

Hi Ossie,
Thanks for your reply, but this is not quite what I am trying to do (I
didn't explain it well). Your solution would work if I know the content of
the cells before hand, but what I am trying to do is to highlight the cells
as I update the inventory (cells A to E) and the requirements cell (F)
To generalize the criteria, what I am trying to do is something like this:
If $F1 0, shade $A1 and continue, else stop
if $F1$A1+0, shade $B1 and continue, else stop
if $F1$A1+$B1, shade $C1 and continue, else stop
if $F1$A1+$B1+$C1, shade $D1 and continue else stop
if $F1$A1+$B1+$C1+$D1+$E1, shade $E1 else stop

like this, anytime I update the cells content they should change. Also, this
should shade partially used cell (if the sum of two cells won't make it and
the sum of three cells is bigger than F1)

Is this still doable with conditional formating or I have to write a
subroutine for it? and how?
Thanks alot

Karim


"OssieMac" wrote:

Hi again Karim,

If you are comparing the sum of the cells to the cell in column F then the
formula in the conditional formatting will be:-

=SUM($A1:$C1) $F1

Regards,

OssieMac

"OssieMac" wrote:

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy- paste
special- formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2)
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B1:D1) 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac


"Karim" wrote:

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.

I became confused also Karim. However, if Dallman's answer is basically
correct, then I suggest that you adjust the formula to only include the $
sign prefix on the columns and leave them off the rows otherwise you will
have to separately enter the conditional formatting formula for every row. If
you only make the column absolute and the row relative, then you can use:-

Copy- Paste Special- Formats to copy the formatting down to the following
rows.

Example: =AND(SUM($A1:$E1)$F1,SUM($A1:$E1)-$E1<=$F1)

Regards,

OssieMac

"Dallman Ross" wrote:

Karim,

I've read what you wrote below and what you wrote originally,
but I can't quite make them conform with each other. Quoted far down
below, you say you want (for Row 1) all columns before column F
highlighted if the totals in column A through that column exceed
column F.

In your most recent reply, however, you are saying to "shade"
earlier columns whenever F1 is bigger (as opposed to smaller).
These two ideas seem at odds with one another. Which do you want?
By "shade" do you mean "highlight," which you used earlier, or its
opposite?

I thought from reading your original that I understood your
request. But your further, "better explained," post makes it all
less clear to me, I'm afraid.

I will propose what I think you want based mostly on your
original request.

Format cells A1 to E1 conditionally. Select "Formula Is".
Put this in as the formula:

=AND(SUM($A$1:$E$1)$F$1,SUM($A$1:E$1)-E$1<=$F$1)

Make the fill color for that condition your preferred highlight
color.

That's it! Now all cells starting with A1 through the FIRST
one that exceeds the value in F1 are highlighted, but not the
later columns in that row.

---
dman

================================================== ===========
In , Karim
spake thusly:

Hi Ossie,
Thanks for your reply, but this is not quite what I am trying to do (I
didn't explain it well). Your solution would work if I know the content of
the cells before hand, but what I am trying to do is to highlight the cells
as I update the inventory (cells A to E) and the requirements cell (F)
To generalize the criteria, what I am trying to do is something like this:
If $F1 0, shade $A1 and continue, else stop
if $F1$A1+0, shade $B1 and continue, else stop
if $F1$A1+$B1, shade $C1 and continue, else stop
if $F1$A1+$B1+$C1, shade $D1 and continue else stop
if $F1$A1+$B1+$C1+$D1+$E1, shade $E1 else stop

like this, anytime I update the cells content they should change. Also, this
should shade partially used cell (if the sum of two cells won't make it and
the sum of three cells is bigger than F1)

Is this still doable with conditional formating or I have to write a
subroutine for it? and how?
Thanks alot

Karim


"OssieMac" wrote:

Hi again Karim,

If you are comparing the sum of the cells to the cell in column F then the
formula in the conditional formatting will be:-

=SUM($A1:$C1) $F1

Regards,

OssieMac

"OssieMac" wrote:

Hi Karim,

You can use the conditional formatting feature. However, when using it,
select Formula as a condition but you must make use of some Absolute
addressing in the formula or you will have problems. For example if you want
to apply conditional formatting to cells A1, B1 and C1 if the sum of those
cells is 10, then you need to select the three cells and in conditional
formatting the formula needs to be entered as follows:-

=SUM($A1:$C1) 10

Note: only the columns have absolute addressing.

The above format can then be copied down the rows using copy- paste
special- formulas. As a test, if you select cells A2:C2 and go into
conditional formatting then you can see the formula becomes =SUM($A2:$C2)
10.

Now you ask why we need absolute addressing on the column. If you select the
range A1:C1 and go into conditional formatting and only use relative
addressing. That is you enter the formula as =SUM(A1:C1) 10 and apply it
then just select cell B1 on it's own and go into conditional formatting
again, you will see that the formula is now =SUM(B1:D1) 10 which is not
correct.

Hope this helps. Get back to me if you still have problems.

Regards,

OssieMac


"Karim" wrote:

Hi,
I am trying to make an inventory sheet that will automatically hilight low
levels:
Each product has values in columns A through E (representing 5 days). Cell F
contain the requested quantities.
What I am trying to do is to change background colour of the cells that will
sum the requested quantities.
For example:
A B C D E F
5 3 3 4 2 10

A, B and C should be hilighted (5+3+3 = 11, 1110)
Is there any way I can do this with conditional formating? or in another way?
I have about 500 products (rows) that I need to do the same function to.