1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Conditional Formatting & Formula Help please

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

Thread Status:
Not open for further replies.
Advertisement
  1. Olivaw

    Olivaw Thread Starter

    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 :D

    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

    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
     
  3. Olivaw

    Olivaw Thread Starter

    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

    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.
     
  5. Olivaw

    Olivaw Thread Starter

    Joined:
    Nov 5, 2007
    Messages:
    115
    Hew,

    I appreciate your patience.
    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.:confused:
     
  6. Yorkshire Guy

    Yorkshire Guy

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

    post your spreadsheet again please.

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

    lol
    Hew
     
  7. Olivaw

    Olivaw Thread Starter

    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

    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!


    post your spreadsheet please
     
  9. Olivaw

    Olivaw Thread Starter

    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.
     

    Attached Files:

  10. Yorkshire Guy

    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:

  11. Olivaw

    Olivaw Thread Starter

    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 (y)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Conditional Formatting
  1. Squashman
    Replies:
    5
    Views:
    371
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/748254

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice