Any question answered...

Speedos.. baby oil... I'm in..

+1

I have an arseload of of old wood in the alley behind our house.

I might come just to see how much wood BRM can actually fit in his arse.

a log or two, easy

Wood yew try for tree at a push?

Big Red, would you like custodianship of my Weber barbie?

New place we move into this weekend has no garden.

You can use it to burn your wood in- when fully stoked it is visible from space and jets have to navigate around the column of hellish heat.

Condition is that you have to invite us all round next summer to use it.

Yes please.

I will be up at the garage Saturday, if you are around.

Moving house on Saturday- feel free to come round and see the new place and pick up your new BBQ.

Excel question

've got a row of data
C3:CG3

At the moment I apply the format via a macro with the following
"=AND(COUNTBLANK(C3:CG3)>0"
to cell B3
so that when a blank cell is in the row, cell B3 goes RED.

However. I've twigged that if AU3="Method C" then AT3 can be blank. and that if the only blank cell is AT3 AND AU3 ="Method C" then B3 should not be coloured.

In my head it seems to be something like
"=AND(COUNTBLANK(C3:CG3)>0) BUT NOT IF ((AT3=blank) AND (AU3 = "Method C"))"

what do I need to do to make it correct?
Is it laid out in the correct logic? and what is the correct excel format?

Will it fit in the b?

It's the old version of this one

You could tow it home?

Fuck, that's massive.

Might go in with the top down.

@ Tiswas
fuck.
I should probably go on a proper Excel course rather than trying to piece together things as I go.

It's a bit like learning English. And I'm not very good at that.

@BRM:
I bet you say that to all the boys.

How does this work, and is it really worth a spoke to open a beer?

http://www.pinkbike.com/video/162218/

Knobber.

[code]OR(AND(U3 = "Method C",COUNTBLANK(C3:AS3,AU3:CG3)>0),COUNTBLANK(C3:CG3)>0)[/code]

I don't think that does the right thing: if only AT3 is blank then COUNTBLANK(C3:CG3)>0 will be true, and so will OR(anything,COUNTBLANK(C3:CG3)>0), i.e. regardless of whether AU3 = "Method C".

You could always leave AT3 out of the main countblank range and handle it seperately:

[code] NOT(AND(COUNTBLANK(C3:AS3,AU3:CG3)=0,OR(AU3="Method C",COUNTBLANK(AT3)=0))) [/code]

Or you could make Tiswas'es approach work with an IF:

[code] IF(AU3="Method C", COUNTBLANK(C3:AS3,AU3:CG3)>0, COUNTBLANK(C3:CG3)>0) [/code]

Or
[code]OR(COUNTBLANK(C3:AS3,AU3:CG3)>0,AND(NOT(AU3="Method C"),COUNTBLANK(AT3)>0))[/code] if you prefer

Thanks for all of these.

I think the last one fits in with my thinking. Will try on a quick mock up at home, properly tomorrow am.

OUT.
I'm in fucking Leeds and then Huddersfield that weekend. I was hoping for Kosmiche Folk Rock with Arran jumpers and belting peaty whisky.

YEah shite weekend to choose - I'll be in wales.

Pffft, you tie yourselves up in knots just to be weedy quitters.

Tiswas: Your method works. Thanks very much.

mmm. actually. None of those appear to be working properly.

Excel question

've got a row of data
C3:CG3

At the moment I apply the format via a macro with the following
"=AND(COUNTBLANK(C3:CG3)>0"
to cell B3
so that when a blank cell is in the row, cell B3 goes RED.

However. I've twigged that if AU3="Method C" then AT3 can be blank. and that if the only blank cell is AT3 AND AU3 ="Method C" then B3 should not be coloured.

In my head it seems to be something like
"=AND(COUNTBLANK(C3:CG3)>0) BUT NOT IF ((AT3=blank) AND (AU3 = "Method C"))"

what do I need to do to make it correct?
Is it laid out in the correct logic? and what is the correct excel format?

[code]
if,countblank(C3:CG3) = 0) [then] no fill [else] red[/code]

should work

ie. the cell is only not-red when either
(a) there is exactly 1 empty cell in your array c3:cg3, that blank cell is at3, and au3 = "Method C"
or (b) there are no empty cells in array c3:cg3

^
so is that:
Formula1:=
"=if(or(and(countblank(C3:CG3)=1,AS3 = ""Method C"",AT3 = ""),countblank(C3:CG3) = 0)"
Selection.FormatConditions(2).Interior.ColorIndex = 3

I'm aware this is kiddy code. I'm planning on sitting down and redoing a lot of this, this month.

I'm confused. You're dropping that formula into the cell? Why?

I'm confused.
Where should I be dropping it?

I have a clunky macro with loads of little functions in it to colour code cells based.

there's other bits, but the bit I'm playing with is this:

Range("b3:B" & LastRow - 1).Select
Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(AND(ISBLANK(CH3)),(CQ3>=CF3+365))"
Selection.FormatConditions(1).Interior.ColorIndex = 3

(so if CH3(a follow up) is blank and CQ3(date I extracted data) is > than CF3(when last seen) +365(a year ago) the cell goes red.
and
then

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

'"=if(or(and(countblank(C3:CG3)=1,AS3 = ""Method C"",AT3 = ""),countblank(C3:CG3) = 0)"

Selection.FormatConditions(2).Interior.ColorIndex = 3

if anything within the range C3:CG3 is blank (apart from AT3 when AS3 = "Method C") it also goes red. And the people I need to see this can see "oh shit, I need to look at those red records".

In a macro to apply a conditional format.
Yes, I am teaching myself english by watching pink panther films.