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.

"Conditional Formating" for cell values in columns(Need help asap!)

Discussion in 'Business Applications' started by mattmurdock, Aug 8, 2012.

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

    mattmurdock Thread Starter

    Joined:
    Jun 18, 2012
    Messages:
    23
    Sorry for the need help asap but I need this for work tomorrow and am pretty stuck any help would be great.
    I'll try to explain clearly. In my worksheet there are 4 tables of portfolios. Each column is a month. The rows in the tables are "triggers" and "volumes". The triggers are prices to buy the volumes. For each month, there are 7 triggers (7 prices where you could buy a volume in a month). So lets say in Jan 12, Ill make a trigger (Trigger 1) in Portfolio B for $200.

    My goal would be that the rest of the Triggers in that Month will now be "XXX" (or even highlighted red or something). In the previous example, Trigger 1 in Portfolio A,C and D is now "XXX".

    I have attached a sample book.

    I'm having trouble because I keep getting a circular reference. Like I said, this is kinda urgent so any help (even if its after tomorrow) would really be great- thank you in advance.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,879
    First Name:
    Wayne
    So the way I have approached this conditional format - would require a lot of different formulas for each trigger

    for Trigger 1
    Two conditional formats
    1) =(B3<>"") - set to STOP IF TRUE - this keeps the cell where you enter the price with NOFILL
    2) =(OR(B3<>"",B25<>"",B47<>"",B69<>"")) - I use this and format the fill colour to be RED

    ALL i'm doing with the OR is testing the 4 sections A,B,C and D to see if they have a value in , if they do then turn red

    So that set of conditional formats - applies to trigger 1 in section A, B,C,D

    So you can copy this Paste special > format - for section A l apply to trigger 1 - Type A, B,C,D and can be copied across the months

    now for section B you need the same formula and cant just copy down - as it will move all the cells down , and if you use $ it will need to have all the $ written into the section A for the different triggers
    so i had to copy in sections and add/remove the $ - took about 20mins to setup on the sheet

    i suspect a macro is needed really

    Any way , I have completed all the cells for Sheet After and for the Year

    have a look at the conditional format and you should see what I mean about the copying bit
    and you can see the way it works across all the different triggers and months

    as i say a macro maybe easier - just depends how you are applying it
     

    Attached Files:

  3. mattmurdock

    mattmurdock Thread Starter

    Joined:
    Jun 18, 2012
    Messages:
    23
    Thanks for helping me out- This does work but I'll have to see if its practical to make all that effort to put in.

    Thanks
     
  4. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Done.
    You need to expand the range of lookup when your project increases.
    Due to high resource usage, I'm not able to apply to 1mil rows.
     

    Attached Files:

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 - Conditional Formating cell
  1. Couriant
    Replies:
    5
    Views:
    329
Thread Status:
Not open for further replies.

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

  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