# Solved: Conditional Formatting &amp; Formula Help please

Discussion in 'Business Applications' started by Olivaw, Sep 8, 2008.

Not open for further replies.

Joined:
Nov 5, 2007
Messages:
115
A few months ago, I was very happy to get some good formula advice here, so I am back for more

For my spreadsheet and stock projections, I would like to achieve the following:

one column shows me the projected stock level based on previous sales -e.g. =AA776-(AS776*\$Z\$1)

This provides me with a future stock figure. Now I don't need this field to be populated in every row as some items are discontinued and in this case the negative outcome is just providing "noise".

So in addition to the above calculation I wonder if I can use conditional formatting based on text shown in another cell, e.g. if cell I776 shows text, then highlight cell Y776 (or even better delete contents of Y776). Is this possible?

2. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
Hi Olivaw,

If I understand what you want, then yes you can do either / both.

you examples are for specific cells, so you will need to adjust these references if you want to apply to all rows.

1. if cell I776 shows text, then highlight cell Y776
By 'shows text', I'll assume that you mean that cell is not empty:-
Apply CONDITIONAL FORMATTING to Y776, use FORMULA IS for your condition type and it would be =I776<>"" then set a font colour or pattern for the Format.

2. if cell I776 shows text, delete contents of Y776:-
Whatever is in Y776, change it to
=if(I776<>"",whatever formula is in Y776 now,"")

By 'whatever formula is in Y776 now' I mean if cell Y776 contains =A1+B1, then that's what you put within the If statement.
You are not 'deleting' what is in Y776, you are just not showing anything depending on I776.

HTH
Hew

Joined:
Nov 5, 2007
Messages:
115
Hew,

excellent and thanks a lot.

I can use formula 1 which is already a big help. Adapting this to formatting the font to white, I get the result intended by scenario 2 though in a different way.

I somehow did not manage your formula for scenario 2, somehow I must be entering it incorrectly. I tried =if(I776<>"",=AB776-(AT776*\$AA\$1,"") but that gets me an error message. I tried dropping the second = but this does not seem to do anything.

4. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
hi Olivaw again,

Yes the 2nd = is wrong and you were correct to remove it, that was my bad posting.

However, it's your brackets that are causing the new error, you need an extra one after the AT776*\$AA\$S1.

Every ( needs a corresponding ).

Use this :

=if(I776<>"",AB776-(AT776*\$AA\$1),"")

lol
Hew

PS Using Formula 2 means you don't need Formula1 in the way you have formatted it, i.e. if formula 2 creates an empty cell then you don't need to format it's Text to white. But if instead you formatted say a Red Pattern, then you'd see it highlighted.

Joined:
Nov 5, 2007
Messages:
115
Hew,

Seems there is still something wrong with the formula as it does not change anything in my spreadsheet. It's too bad that you cannot cut & paste when using formulas, making it more difficult to compare though I was very careful when following your sample.

As for my variation to your first formula, I realise that this will do without the need for the second formula. By using white on white, the field will appear empty as intended. So in a way this is the easier and for this purpose more elegant solution, though I still would like to understand the other method and see where I am going wrong.

6. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
hi Olivaw,

you can copy & paste from this thread to your spreadsheet,; paste to the formula bar though, not cell copy / paste.

lol
Hew

Joined:
Nov 5, 2007
Messages:
115
Sorry, now I am confused. I assumed the formula has to be entered into the cell appearing when clicking "conditional formatting". I don't know how to open this cell or modify it via the tool bar? Clicking on the = sign on the bar allows me to paste but only for the original calculation not the conditional formatting formula?

8. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
The formua =if(I776<>"",AB776-(AT776*\$AA\$1),"") was to go into Y776, not a Conditional box!

Joined:
Nov 5, 2007
Messages:
115
Hew, thanks again for your patience.

Here is a slighly modified version of the spreadsheet. The relevant columns are:

Z: this shows me the future stock based on various calculations and changes in the other cells

K: this is the column I will use to enter some info - if this cell has any value, my preference would be for the corresponding cell in Z to be blank

To explain: at present Z2 shows a negative value of 214, meaning at a given time in future based on historical data I will have a shortage of 214 units, so I better order new stock
However, my actual spreadsheet has 1500 rows, not all items are active and relevant.
So for those items that I know are not relevant, e.g. Widget 2 because it's a discontinued item, I would like to put a value into column K resulting in the corresponding cell in Z to be blank. That way only the negative cells relevant for ordering show up. If you stare at a spreadsheet with 1500 rows and dozens of columns, anything to reduce "noise" will help with focussing on the important stuff.

Thanks to your help I can now do that by conditional formatting using the solution 1 and white font on white. However, as I am keen to learn more, the other formula may have other applications in future, so it would be great to know this too.

File size:
24 KB
Views:
76
10. ### Yorkshire Guy

Joined:
Dec 9, 2003
Messages:
563
Hi,

I have changed cell Z3 for you, you will need to apply this to the whole column but I wanted you to see the difference from say Z2 first.
Note: Z2 had a different formula in it.
Z2 is =AB2-(AW2*\$AA\$1)
Z3 was =AB3-(AT3*\$AA\$1)
ie. one is using AW the other AT.
You can see the affect on Z3 by deleting the contents of K3, and back again...

I have also added a conditional format column Z for you (excluding the Title).
If you want to change this, or don't want it, select the column and then Format / Conditional Formatting.

lol
Hew

#### Attached Files:

• ###### Formatting2.xls
File size:
25.5 KB
Views:
76

Joined:
Nov 5, 2007
Messages:
115
Hew,

excellent - this is just what I was hoping for - giving me different options to work with my spreadsheet.

Thanks again - you are a legend

As Seen On