Any MS Excel users? - Aquarium Advice

**ccCapt** · 10-04-2008, 10:52 AM

I think the answer is easier than I'm making it out to be...lol. But I can't get it right anyway.
Say I have 10 columns, A1 to J1.
K1 is the sum of A1 to J1
L1 is a set number imported from another database.
M1 is the difference between K1 and L1.
If the value of M1 is a negative number, how do I shade row A1 to M1?

dohhhhh...lol nevermind
just had to get the conditional format right

**neilanh** · 10-04-2008, 01:06 PM

good job

Kurt_Nelson · 10-05-2008, 12:56 AM

Glad we could help!

(Don't you hate it when you go and ask someone a question, and the very second it leaves your lips you realize the answer... no matter how long you thought about it before your asked the question out loud?! Go figure.)

**ccCapt** · 10-05-2008, 10:22 AM

No kidding Kurt...lol. I worked on that stupid formula like an hr before I posted...then 15 mins later I figure it out.

OldMan · 10-09-2008, 07:35 PM

Another option is to go to the file options and set up a format for negative numbers in the file. That way you don't need to conditional format the individual cell.

**ccCapt** · 10-09-2008, 08:59 PM

Actually what I did was highlighted the entire row and did the conditional format. How I set the condition was: Formula is and =$M1<0 then I picked what I wanted it too look like.

But....hehehe.
Maybe you, or someone else can suggest something here.

We know K1 is the sum of A1 to J1 and that M1 can be a positive or negative number.
If M1 is negative, following the conditions above, the entire row will change.
Let say A1 to J1 are all 10, so K1 is 100. If M1 is -60, instead of changing the entire row, how can I set it so it will add, from right to left until it reaches -60. So in other words, only cells K1 to E1 change. E1 being the cell where to total hits -60. Or if it's -20, only K1 to H1 change.

Is that confusing enough?

OldMan · 10-09-2008, 09:59 PM

You could set up cells that do not normally show, maybe in hidden columns, that measure the subtotals and then use your conditional formatting with the invisible columns as the condtion. For example if AL1 was the sum of A1 and B1, then you could format B1 based on the subtotal stored in AL1. The format of C1 would be based on the subtotal stored in AM1 which would contain the sum of A1 through C1, etc. Be sure you use sums rather than + signs in your formula so that null cells, the ones with no numbers in them, won't throw off the calculation and give you error messages. By the way AL1 is way off the screen to the right if you have a reasonable number of columns on your screen.
Is this confusing enough?

**ccCapt** · 10-10-2008, 01:04 AM

I follow what you are saying and that would probably work, but I'm thinking in a different direction...and maybe it's the wrong way lol.
But I think it can be done using IF and AND IF in the fornula...somehow. Like the condition would be met IF SUM(J1:A1)<M1 AND IF SUM(J1:B1)<M1 etc. Going this route tho seems more like vb, and I don't know vb good enough or even Excel good enough to figure how to get it working.
Dunno..maybe it can be done that way, maybe not. Seems to be getting too complicated tho for a spare time project...lol

OldMan · 10-10-2008, 08:51 AM

I think you have a valid approach but I am also not that well versed in VB. It is why I look for the slightly more indirect method that I can understand. Sometimes 2 steps are easier than 1 in Excel or Access query programming. I write Access extensively to support people at work and find that if I break it down so I can examine it later, it makes adjusting and adapting to new uses much easier. Lets's face it, you can never remember every detail of how you built something for another user so it's best to leave a trail unless you are a programmer for a living. In that case you could leave your trail in your personal developer's notebook instead.