My formula is in a sheet named "Values"; the only other sheet is named
"Data". The workbook contains EXCEL VBA code; Automatic Calculation
is selected.

The format of the Functions I am using is: INDEX (array, row_num) and
MATCH (lookup_value, lookup_array, match_type).

In the Data Sheet, Col A and Col B are "column"-formatted as
"General". Col A has a blank row, a label row, and then 50 rows of
alphanumeric strings (one of which contains the string "CURRENT PRICE"
followed by five spaces and then a 6-digit number, this alphanumeric
sub-string could be in a number of different rows; only one per day).
Col B has a blank row, a label row, and then in Cell B3, there is an
User Defined Function (UDF): '= GetCurrentPrice (A3, "CURRENT
PRICE")'; this formula is copied in each cell down to Cell B52,
inclusive. When displaying Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

My formula is: ' = INDEX (Data! B:B, MATCH ("", Data! B:B, 1) + 1) '
The concept is for MATCH to find the row_num of the cell that is
immediately above the row containing the "CURRENT PRICE" sub-string;
then add 1 to it and then INDEX has the correct row_num. INDEX does
the rest, pulling in the CURRENT PRICE to the Values Sheet.

Sometimes my formula works and sometimes I get a value of zero.
Because the number of alphanumeric strings in the Data Sheet Col A is
a variable, I have been experimenting with the following: instead of
using 'B:B' in my formula, I have been using 'B3:B100' as an INDEX
argument and 'B3:B90' as a MATCH argument. Also, I have copied, in
the Data Sheet, my UDF in each cell (Col B) down to Cell B100,
inclusive.

A few other thoughts: I believe copying my UDF to the maximum
expected number of data rows is critical. During my experiments, I
was testing the INDEX Function by itself and the MATCH Function by
itself. It seems that, sometimes, MATCH would come up with 'a
relative position' row reference to the bottom row in the Data Sheet
(it was, apparently, ignoring the CURRENT PRICE value). << Why is
this?

OK, now my formula is working with the following argument -- 'B1:B100'
-- for both INDEX and MATCH. This surprises me: it seems that Cell
B1 being empty and Cell B2 containing a Column Label would "screw-up"
the MATCH Function. << ???

Can anyone shed light on this situation?

Thanks,
JingleRock

Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)

--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
My formula is in a sheet named "Values"; the only other sheet is named
"Data". The workbook contains EXCEL VBA code; Automatic Calculation
is selected.

The format of the Functions I am using is: INDEX (array, row_num) and
MATCH (lookup_value, lookup_array, match_type).

In the Data Sheet, Col A and Col B are "column"-formatted as
"General". Col A has a blank row, a label row, and then 50 rows of
alphanumeric strings (one of which contains the string "CURRENT PRICE"
followed by five spaces and then a 6-digit number, this alphanumeric
sub-string could be in a number of different rows; only one per day).
Col B has a blank row, a label row, and then in Cell B3, there is an
User Defined Function (UDF): '= GetCurrentPrice (A3, "CURRENT
PRICE")'; this formula is copied in each cell down to Cell B52,
inclusive. When displaying Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

My formula is: ' = INDEX (Data! B:B, MATCH ("", Data! B:B, 1) + 1) '
The concept is for MATCH to find the row_num of the cell that is
immediately above the row containing the "CURRENT PRICE" sub-string;
then add 1 to it and then INDEX has the correct row_num. INDEX does
the rest, pulling in the CURRENT PRICE to the Values Sheet.

Sometimes my formula works and sometimes I get a value of zero.
Because the number of alphanumeric strings in the Data Sheet Col A is
a variable, I have been experimenting with the following: instead of
using 'B:B' in my formula, I have been using 'B3:B100' as an INDEX
argument and 'B3:B90' as a MATCH argument. Also, I have copied, in
the Data Sheet, my UDF in each cell (Col B) down to Cell B100,
inclusive.

A few other thoughts: I believe copying my UDF to the maximum
expected number of data rows is critical. During my experiments, I
was testing the INDEX Function by itself and the MATCH Function by
itself. It seems that, sometimes, MATCH would come up with 'a
relative position' row reference to the bottom row in the Data Sheet
(it was, apparently, ignoring the CURRENT PRICE value). << Why is
this?

OK, now my formula is working with the following argument -- 'B1:B100'
-- for both INDEX and MATCH. This surprises me: it seems that Cell
B1 being empty and Cell B2 containing a Column Label would "screw-up"
the MATCH Function. << ???

Can anyone shed light on this situation?

Thanks,
JingleRock

Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)

--
Biff
Microsoft Excel MVP

Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock

Did you try the formula?

=LOOKUP(1E100,Data!B:B)

That formula will return the *last numeric value* from Data column B.

You say your UDF extracts the numeric portion from a string and places that
*one numeric value* somewhere in Data column B.

If there is only one numeric value in Data column B then it also has to be
the *last numeric value* and the above formula will find it.

If the formula didn't work (will return #N/A if no numeric values are found)
then you don't have any numeric values in Data column B. Could be your UDF
returns a TEXT number.

Try this generic version. It'll work on both text and numbers.

=LOOKUP(2,1/(Data!B3:B52<""),Data!B3:B52)

--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)

--
Biff
Microsoft Excel MVP

Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock

Come to think of it, this is much easier than we're making it out to be.

If there is only 1 *numeric value* in the range this will suffice:

=MAX(Data!B3:B52)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Did you try the formula?

=LOOKUP(1E100,Data!B:B)

That formula will return the *last numeric value* from Data column B.

You say your UDF extracts the numeric portion from a string and places
that *one numeric value* somewhere in Data column B.

If there is only one numeric value in Data column B then it also has to be
the *last numeric value* and the above formula will find it.

If the formula didn't work (will return #N/A if no numeric values are
found) then you don't have any numeric values in Data column B. Could be
your UDF returns a TEXT number.

Try this generic version. It'll work on both text and numbers.

=LOOKUP(2,1/(Data!B3:B52<""),Data!B3:B52)

--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.

If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)

--
Biff
Microsoft Excel MVP

Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock

JingleRock wrote:
Col B, Cells B3 through B52 are blank,
except one -- this displays the CURRENT PRICE.
If "current price" is a numeric value try this:

=LOOKUP(1E100,Data!B:B)

--
Biff
Microsoft Excel MVP

Biff,

Thanks for your response.

I think that I didn't explain my situation very well, so I will
elaborate.
'CURRENT PRICE' consists of 6 digits and a decimal embedded in a
string; this price will change daily. This sub-string will be in an
indeterminable row on a daily basis and will be of the form ...
" CURRENT PRICE 6.54321 ". My UDF will
extract the '6.54321' and place the result in Col B (formatted as
"General"). There will only be one such string in the Data Sheet
daily; that is why there will be only one displayed value in the range
B3:B52 (the UDF evaluates to a blank cell if there is no "CURRENT
PRICE" in that row). The trick is getting the day's CURRENT PRICE to
the Values Sheet.

JingleRock


If the description of your data above is accurate, try this array formula
(commit with CTRL+SHIFT+ENTER):

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(A1:A1000,"CURRENT PRICE",""))))

Biff and Glenn,

Thank you both for your solutions.
Biff, you are right, my UDF returns a TEXT number for CURRENT PRICE.
So, ' =LOOKUP(2,1/(Data!B3:B52<""),Data!B3:B52) ' works fine.
And the same for ' =MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!
B1:B1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!B1:B1000,"CURRENT PRICE",""))))) ' (I did have
to add a closing paren at the end.)

Do each of you have any comments as to which is preferable?

A few comments:
o "CURRENT PRICE", and the corresponding TEXT number, will
always (99.9999999% reliability) exist.
o Each of the two solutions works with the range B1:B1000 (even
though my UDF was not copied below row 52);
also, remember that B1 is totally empty ("") and B2 contains
an alphabetic string. However, neither solution
would work with the range B:B (too bad).

Each of the solutions is way over my EXCEL knowledge. If each of you
could give me a few words as to what is happening in your solution, I
would appreciate it.

Thanks again,
JingleRock

JingleRock wrote:
Biff and Glenn,

Thank you both for your solutions.
Biff, you are right, my UDF returns a TEXT number for CURRENT PRICE.
So, ' =LOOKUP(2,1/(Data!B3:B52<""),Data!B3:B52) ' works fine.
And the same for ' =MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!
B1:B1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!B1:B1000,"CURRENT PRICE",""))))) ' (I did have
to add a closing paren at the end.)

Do each of you have any comments as to which is preferable?

A few comments:
o "CURRENT PRICE", and the corresponding TEXT number, will
always (99.9999999% reliability) exist.
o Each of the two solutions works with the range B1:B1000 (even
though my UDF was not copied below row 52);
also, remember that B1 is totally empty ("") and B2 contains
an alphabetic string. However, neither solution
would work with the range B:B (too bad).

Each of the solutions is way over my EXCEL knowledge. If each of you
could give me a few words as to what is happening in your solution, I
would appreciate it.

Thanks again,
JingleRock




The array formula solution I provided was meant to replace your UDF and column B
entirely. The references to column A are intentional. You could put this
formula directly in your Values sheet. Something like this:

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!A1:A1000,"CURRENT PRICE",""))),"",
--TRIM(SUBSTITUTE(Data!A1:A1000,"CURRENT PRICE",""))))