Find first value in column with formulas
 

Columns D & H contain an IF formula.

Column D Formula =IF(C2="Diners",B2,"")
Column H Formula =IF(C2="Westpac",B2,"")

The first row containing a dollar value in either of columns D & H could be
two or ten rows down.

How do I program to locate the first cell that contains a dollar value
skipping over "empty" cells containing the formula only.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP SP3 Office 2007 on Mini Mac using VMware.

Find first value in column with formulas
 

Bob

Your formula appears to count the "empty" Cells down too, but not including
the first dollar value.

I'm after the first dollar value in the column.
It should of returned a $169 value but showed the number 11 which is the
same number of "empty" rows before the $169 value.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.


"Bob Phillips" wrote:

Try this array formula

=MATCH(1,--(LEN(D2:D20)0),0)

--
__________________________________
HTH

Bob

"Aussie Bob C" wrote in message
...
Columns D & H contain an IF formula.

Column D Formula =IF(C2="Diners",B2,"")
Column H Formula =IF(C2="Westpac",B2,"")

The first row containing a dollar value in either of columns D & H could
be
two or ten rows down.

How do I program to locate the first cell that contains a dollar value
skipping over "empty" cells containing the formula only.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP SP3 Office 2007 on Mini Mac using VMware.

Find first value in column with formulas
 

Try this array-entered** formula then...

=INDEX(D1:D20,MATCH(1,--(LEN(D1:D20)0),0))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself

--
Rick (MVP - Excel)


"Aussie Bob C" wrote in message
...
Bob

Your formula appears to count the "empty" Cells down too, but not
including
the first dollar value.

I'm after the first dollar value in the column.
It should of returned a $169 value but showed the number 11 which is the
same number of "empty" rows before the $169 value.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.


"Bob Phillips" wrote:

Try this array formula

=MATCH(1,--(LEN(D2:D20)0),0)

--
__________________________________
HTH

Bob

"Aussie Bob C" wrote in message
...
Columns D & H contain an IF formula.

Column D Formula =IF(C2="Diners",B2,"")
Column H Formula =IF(C2="Westpac",B2,"")

The first row containing a dollar value in either of columns D & H
could
be
two or ten rows down.

How do I program to locate the first cell that contains a dollar value
skipping over "empty" cells containing the formula only.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP SP3 Office 2007 on Mini Mac using VMware.

Find first value in column with formulas
 

That did the trick.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.


"Rick Rothstein" wrote:

Try this array-entered** formula then...

=INDEX(D1:D20,MATCH(1,--(LEN(D1:D20)0),0))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself

--
Rick (MVP - Excel)


"Aussie Bob C" wrote in message
...
Bob

Your formula appears to count the "empty" Cells down too, but not
including
the first dollar value.

I'm after the first dollar value in the column.
It should of returned a $169 value but showed the number 11 which is the
same number of "empty" rows before the $169 value.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.


"Bob Phillips" wrote:

Try this array formula

=MATCH(1,--(LEN(D2:D20)0),0)

--
__________________________________
HTH

Bob

"Aussie Bob C" wrote in message
...
Columns D & H contain an IF formula.

Column D Formula =IF(C2="Diners",B2,"")
Column H Formula =IF(C2="Westpac",B2,"")

The first row containing a dollar value in either of columns D & H
could
be
two or ten rows down.

How do I program to locate the first cell that contains a dollar value
skipping over "empty" cells containing the formula only.

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP SP3 Office 2007 on Mini Mac using VMware.