copying conditon formats with $

[Mark]

My 2 cell conditional format works perfect,

But I can't copy a 4 cel copy and Paste special down the column , I think
because the $ signs are in th conditionals.

I'm not sure why they are there, but I know if I remove the $ sign for the
copy paste special or past, My conditionals Split colors or remain.


Formula in O7 is:
=IF(COUNT(D7:N7)>0,SUM(D7:N7),"")


Conditional formulas in O6,O7

=AND(O$7<>"",O$7<=0) Green
=AND(O$7<>"",O$7>=1) Red

I wanted cells until data appeared.

All works fine, cept.

I can not copy it down some 800 lines thru O871
I have text every 4th line.

IS there a way to copy this down and have he cell numbers change with it, an
keep the integrity of the Formula and the conditionals?

Thanks.

 

[Rasoul Khoshravan]

My 2 cell conditional format works perfect,

But I can't copy a 4 cel copy and Paste special down the column , I think
because the $ signs are in th conditionals.

I'm not sure why they are there, but I know if I remove the $ sign for the
copy paste special or past, My conditionals Split colors or remain.

Formula in O7 is:
=IF(COUNT(D7:N7)>0,SUM(D7:N7),"")

 Conditional formulas in O6,O7

=AND(O$7<>"",O$7<=0) Green
=AND(O$7<>"",O$7>=1) Red

I wanted cells until data appeared.

All works fine, cept.

I can not copy it down some 800 lines thru O871
I have text every 4th line.

IS there a way to copy this down and have he cell numbers change with it,an
keep the integrity of the Formula and the conditionals?

Thanks.

As you mentioned yourself, if you remove the $ sign, the problem will
be solved. That $ signs, keep the row number to be a fixed number upon
copying to other rows. bring the cursor to the dollar sign and press
F4 key, you will see four possible combination and choose the one with
no $ sign.

 

[Khoshravan]

As you mentioned yourself, if you remove the $ sign, the problem will be
solved. That $ signs, keep the row number to be a fixed number upon copying
to other rows. bring the cursor to the dollar sign and press F4 key, you will
see four possible combination and choose the one with no $ sign.

 

[Mark]

Completely changes the acton on the work sheet. Is there n work arounf or a
formula to get the exact same results without trmoving the $

I needed to keep cells blank until data hit a minimum of 1 cell.
Then The conditionals. If yu remove the $'s, when you delete data that it
pertains to. The last color standing remains.

Could you look at my Formula in O7, then look ay the conditionals, and find
a way for them to act the same,

AND BE ABLE TO COPY DOWN THE PAGE>

 

[Mark]

Completely changes the acton on the work sheet. Is there n work arounf or a
formula to get the exact same results without trmoving the $

I needed to keep cells blank until data hit a minimum of 1 cell.
Then The conditionals. If yu remove the $'s, when you delete data that it
pertains to. The last color standing remains.

Could you look at my Formula in O7, then look ay the conditionals, and find
a way for them to act the same,

AND BE ABLE TO COPY DOWN THE PAGE>

 

[Max]

The complexity lies in the intended propagation of the CF formula

Tinker with it like this, which applies it at one go into the entire range.
Lightly tested here, seems to function just right

Select O6:O871 (with O6 active),
apply the CF using Formula Is:

Condition 1:
=AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<>"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<=0)
Format > Green

Condition 2:
=AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<>"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)>0)
Format > Red

Ok out, then test it ..

I took the liberty of changing slightly the 2nd condition (">0" used instead
of ">=1") so that there are no gaps with the 1st condition

If the above helps in any/some way, press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

 

[Mark]

I certainly can remove the $ in the ways you describe. But, then the cells
remain at the last color they were at if a date in O5 is removed.

That makes for a lot of color.

I will try what Alojz oferred, barring learning how to do the test!


Mark
Thanks David.

 

[Pete_UK]

Mark,

I amended your file and sent it back to you - did you receive it, and
did it do what you wanted?

Pete

 

[Max]

Reading and inferring between the lines from the later responses since
posted, it looks like my interp of your issue was quite off. Save the
suggestion as a way for when you meant to propagate it as described in the
response, ie when you want to CF a pair of 2 consecutive columnar cells,
where the conditions are affixed to the value in the 2nd cell in each pair
down the range. Bottoms up!
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[Mark]

Yes of course, I thought maybe, I had become heavy. was gone most of the day.
Your formulas were Spot on, and The sheet is great.

I need to include in the conditionals, for P5,6,7(ship Date)

If D6:N6 are blank the conditionals don't kick in.

I need the exact same condtionals you wrote but I need (P7) to calulate the
difference between P5 and O5 and have the conditionals still work (EVEN if)
D6:N6 are BLANK

Reason. Sometimes D6 row will not get a date. and
There will still be an entry in P5.

All About P7

This is what you wrote:

=AND(P7<>"",P7<=0) Green
=AND(P7<>"",P7>=1) Red

Make sense.

Your an MVP Pete.!!

 

[Mark]

I din't see this till late.

That is some incredible formula. I will try it on a test sheet, before I
torpedo 871 lines. Which I've done more than once now.

Thanks you MAX.

Pete has been great with me also, he has quite the handle on what really is
a simple tracking sheet.

It's me that doesn't know much at all is the problem, and I got in oer my
head..

Mark

 

[Mark]

Over my head!!! Period!

Thanks.

Mark

 

[Max]

No prob, Mark. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik