first event identified

[Ian Murdoch]

How do I identify the first time a positive value occurs in a ro
without the need for visual inspection of each row ?

Ian Murdoc

 

[Bob Phillips]

Hi Ian,

Use Conditional Formatting.

Select your target row (I use 9 in this example)
Go to Format>Conditional Formatting
Change Condition 1 to Formula Is
Enter this formula
=A9=INDIRECT(CHAR(MIN(IF(9:9>0,COLUMN(9:9)))+64)&ROW($A$9))
Click the Format button
Select the pattern tab
Choose a colour
Exit out

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JMay]

Bob:
Would you mind "walking me through" this formula?
I can't seem to piece it together. I see where if row 9 > 0 then
column(9:9)
creates an array 1,2,3,4...254,255,256
after that I'm not sure what's going on. The Char + 64 seems against a 1
to create the letter "A". ??#$%#%
any help appreciated..
JMay

Hi Ian,

Use Conditional Formatting.

Select your target row (I use 9 in this example)
Go to Format>Conditional Formatting
Change Condition 1 to Formula Is
Enter this formula
=A9=INDIRECT(CHAR(MIN(IF(9:9>0,COLUMN(9:9)))+64)&ROW($A$9))
Click the Format button
Select the pattern tab
Choose a colour
Exit out

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Okay, we'll give it a try.

First IF(9:9>0,COLUMN(9:9))
This checks each column in row 9 and test for it being positive, and builds
an array of the column number if it is positive, FALSE otherwise

MIN(IF(9:9>0,COLUMN(9:9))) simply gets the minimum value from this array
(remember, it will either be a number or FALSE, FALSE will be passed on)

CHAR(MIN(IF(9:9>0,COLUMN(9:9)))+64) gets that value as a column letter

ROW(9:9) simply returns the row n umber of our target row

The column letter and the row number is then passed to the INDIRECT
functyion to get the value in that cell.

Take a smaller sample

INDIRECT(CHAR(MIN(IF(A9:F9>0,COLUMN(A9:F9)))+64)&ROW($A$9))

with values of -1, -2,0,3,4,5 in A9-F9

The array will return FALSE,FALSE,FALSE,4,5,6, of which the MIN is 4. Add 64
and get the letter, returns D. Pass that letter and the row number of 9 to
INDIRECT, and we eseentially getr
=INDIRECT("D9"), and a value of 4.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob:
Would you mind "walking me through" this formula?
I can't seem to piece it together. I see where if row 9 > 0 then
column(9:9)
creates an array 1,2,3,4...254,255,256
after that I'm not sure what's going on. The Char + 64 seems against a 1
to create the letter "A". ??#$%#%
any help appreciated..
JMay

 

[Bob Phillips]

Slight error in example, the MIN column is 5 not 4, giving a letter of E, so
the formula boils down to

=INDIRECT("E9").

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JMay]

Wow.............
I've got to print this out and spend the rest of the day on it (including
the addendum).
This is fabulous, thanks - I'm about to straighten-out the learning-curve,
thanks to your generous help.
We all appreciate you in this Group Bob;
JMay

 

[Bob Phillips]

Glad it is of help, and thanks for those kind words.

Best Regards

Bob