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

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

Not open for further replies.

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.

File size:
12.8 KB
Views:
25
2. ### etafModerator

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:

• ###### Trigger_conform.xlsx
File size:
16.2 KB
Views:
27

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

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.

File size:
13.1 KB
Views:
36
As Seen On