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",""))))

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

Well, of course I think my suggestion is preferable! <g

I don't know exactly what your UDF is doing other than extracting part of a
string, but, based on which string and under what conditions, so I can't
comment on Glenn's approach.

You said the string "Current Price nnnnnn" may appear many times in your
range so I don't know which instance of that string you're interested in.


--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
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",""))))

T. Valko wrote:
Do each of you have any comments as to which is preferable?

Well, of course I think my suggestion is preferable! <g

I don't know exactly what your UDF is doing other than extracting part of a
string, but, based on which string and under what conditions, so I can't
comment on Glenn's approach.

You said the string "Current Price nnnnnn" may appear many times in your
range so I don't know which instance of that string you're interested in.




Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"

"Glenn" wrote in message
...
T. Valko wrote:
Do each of you have any comments as to which is preferable?

Well, of course I think my suggestion is preferable! <g

I don't know exactly what your UDF is doing other than extracting part of
a string, but, based on which string and under what conditions, so I
can't comment on Glenn's approach.

You said the string "Current Price nnnnnn" may appear many times in your
range so I don't know which instance of that string you're interested in.




Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"

Yeah, that's the one instance their UDF has extracted. Where is this
instance of the string in the "raw data" and which one does the UDF extract?

--
Biff
Microsoft Excel MVP

Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"

Actually, the OP stated...

"There will only be one such string in the Data Sheet daily"

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work. I am posting a snippet of some of my EXCEL VBA code below.

In a regular module:
'Determine the number of records read
numRows = ws.QueryTables("BondData").ResultRange.Columns("A" ).Rows.
_
Count + 2

'Clear the scrubbing category formulas
ws.Range("B3", "B65536").ClearContents

ws.Cells(3, "B").value = "=GetCurrentPx($A3,""CURRENT PRICE"")"
ws.Range("B4", "B" & numRows) = ws.Cells(3, "B").FormulaR1C1

In another regular module:
Option Explicit

Public Function GetCurrentPx(ByVal s As String, ByVal v As String) As
String
GetCurrentPx = FindString(s, v)
End Function

Private Function FindString(ByVal s As String, ByVal v As String) As
String
If InStr(s, v) 0 Then
FindString = Trim(Replace(Right(s, Len(s) - InStr(s, v) + 1), v,
vbNullString))
Else
FindString = vbNullString
End If
End Function

Any comments?
JingleRock

Actually, the OP stated...
"There will only be one such string in the Data Sheet daily"

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work. I am posting a snippet of some of my EXCEL VBA code
below.

In a regular module:
'Determine the number of records read
numRows = ws.QueryTables("BondData").ResultRange.Columns("A" ).Rows.
_
Count + 2

'Clear the scrubbing category formulas
ws.Range("B3", "B65536").ClearContents

ws.Cells(3, "B").value = "=GetCurrentPx($A3,""CURRENT PRICE"")"
ws.Range("B4", "B" & numRows) = ws.Cells(3, "B").FormulaR1C1

In another regular module:
Option Explicit
Public Function GetCurrentPx(ByVal s As String, ByVal v As String) As
String
GetCurrentPx = FindString(s, v)
End Function

Private Function FindString(ByVal s As String, ByVal v As String) As
String
If InStr(s, v) 0 Then
FindString = Trim(Replace(Right(s, Len(s) - InStr(s, v) + 1), v,
vbNullString))
Else
FindString = vbNullString
End If
End Function

Any comments?
JingleRock

Ok, if there's only 1 instance of "CURRENT PRICE" in column A, where is it
in relation to the other data in column A? It sounds like it may be the
*last* entry in the column. Can you give us several representative samples
of what the "CURRENT PRICE" string looks like.

This is probably a lot easier than we're making it out to be.

--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
Actually, the OP stated...
"There will only be one such string in the Data Sheet daily"

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work. I am posting a snippet of some of my EXCEL VBA code
below.

In a regular module:
'Determine the number of records read
numRows = ws.QueryTables("BondData").ResultRange.Columns("A" ).Rows.
_
Count + 2

'Clear the scrubbing category formulas
ws.Range("B3", "B65536").ClearContents

ws.Cells(3, "B").value = "=GetCurrentPx($A3,""CURRENT PRICE"")"
ws.Range("B4", "B" & numRows) = ws.Cells(3, "B").FormulaR1C1

In another regular module:
Option Explicit
Public Function GetCurrentPx(ByVal s As String, ByVal v As String) As
String
GetCurrentPx = FindString(s, v)
End Function

Private Function FindString(ByVal s As String, ByVal v As String) As
String
If InStr(s, v) 0 Then
FindString = Trim(Replace(Right(s, Len(s) - InStr(s, v) + 1), v,
vbNullString))
Else
FindString = vbNullString
End If
End Function

Any comments?
JingleRock

JingleRock wrote:
Actually, the OP stated...
"There will only be one such string in the Data Sheet daily"

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work.


Did you try entering it with the references to column A on sheet Data and did it
give you the correct answer? If not, then I would need to see a sample of your
data, not of your code, because I don't do much programming (and I don't think
it is necessary to get the results you desire).

Glenn wrote:
JingleRock wrote:
Actually, the OP stated...
"There will only be one such string in the Data Sheet daily"

Glenn, you are correct.
Col A of the Data Sheet contains a list of securities with a brief
description and a price for each, and then a total price labelled
"CURRENT PRICE" (not a fixed # of rows from the top row or from the
bottom row).

Glenn, I do not know if your idea of eliminating Col B in Data Sheet
will work.


Did you try entering it with the references to column A on sheet Data
and did it give you the correct answer? If not, then I would need to
see a sample of your data, not of your code, because I don't do much
programming (and I don't think it is necessary to get the results you
desire).



A modification in case there are any cells in column A that have only numbers in
them:

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

Again, this is an array formula that should be committed with CTRL+SHIFT+ENTER.

A modification in case there are any cells in column A that have only numbers in
them:

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

Again, this is an array formula that should be committed with CTRL+SHIFT+ENTER.- Hide quoted text -

Glenn, the above formula evaluates to zero (I added the reference to
Data!); it also evaluates to zero when referring to Col B in Data
Sheet. However, the preceding version you sent does extract the
correct CURRENT PRICE when referring to Col B. I think I will stick
with that. Thanks very much for your help. And Biff, a big thank you
to you as well.

JingleRock

P.S.: What does the --TRIM Function do? I know about the TRIM
Function.

P.S.: *What does the --TRIM Function do? *I know about the TRIM Function.

The following works as well.

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!B1:B1000,"",""))),"",--TRIM
(SUBSTITUTE(Data!B1:B1000,"",""))))

JingleRock

The following works as well.

=MAX(IF(ISERROR(--TRIM(SUBSTITUTE(Data!B1:B1000,"",""))),"",--TRIM
(SUBSTITUTE(Data!B1:B1000,"",""))))

JingleRock

The following works as well.
=MAX(IF(ISERROR(--TRIM(Data!B1:B1000)),"",--TRIM(Data!B1:B1000)))

JingleRock

JingleRock wrote:
A modification in case there are any cells in column A that have only numbers in
them:

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

Again, this is an array formula that should be committed with CTRL+SHIFT+ENTER.- Hide quoted text -

Glenn, the above formula evaluates to zero (I added the reference to
Data!); it also evaluates to zero when referring to Col B in Data
Sheet. However, the preceding version you sent does extract the
correct CURRENT PRICE when referring to Col B. I think I will stick
with that. Thanks very much for your help. And Biff, a big thank you
to you as well.

JingleRock

P.S.: What does the --TRIM Function do? I know about the TRIM
Function.


There is no point in using this formula if you are still using your UDF and
column B. If you would like me to take a look at your workbook (or sample
data), save it to www.savefile.com and post the link here. I will take a look
and see if I can figure out why it's not working as expected.

Good luck!

--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
A modification in case there are any cells in column A that have only
numbers in
them:

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

Again, this is an array formula that should be committed with
CTRL+SHIFT+ENTER.- Hide quoted text -

Glenn, the above formula evaluates to zero (I added the reference to
Data!); it also evaluates to zero when referring to Col B in Data
Sheet. However, the preceding version you sent does extract the
correct CURRENT PRICE when referring to Col B. I think I will stick
with that. Thanks very much for your help. And Biff, a big thank you
to you as well.

JingleRock

P.S.: What does the --TRIM Function do? I know about the TRIM
Function.

Glenn,

I will have to send my sample data from my home computer (when I click
on the link here, I get a "Websense" pop-up; it is our Network
Security System at my office).

I will be sending you a workbook including the two Sheets we have been
talking about plus the VBA Code (I disabled the 'ThisWorkbook' module
which contains code that automatically updates the Data Sheet).

Also, you will see that the Data Sheet is a slightly more complicated
situation:
- instead of just CURRENT PRICE, I also want to extract PREVIOUS
PRICE; and
- for CURRENT PRICE, there is an intervening string of ' 7.52 '
that needs to be ignored; the UDFs in the 'modUtils'
module handle this situation as well as the simpler situation
for PREVIOUS PRICE.

I am looking forward to your comments.

JingleRock

On Mar 12, 2:06*pm, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
The double unary minus -- is being used to coerce a text string to a number.
If A1 contains a text string 42 (either by being entered as '42, or as
="42", or as the result of a formula returning a text string), =-A1 would
return -42, but =--A1 would return 42.

Thanks David,
The formula makes much more sense to me now.
JingleRock

Glenn,

This is the link to my sample data: http://www.savefile.com/files/2038013
Also, refer to my comments about this file that I posted this
afternoon.

JingleRock

JingleRock wrote:
Glenn,

This is the link to my sample data: http://www.savefile.com/files/2038013
Also, refer to my comments about this file that I posted this
afternoon.

JingleRock



Put this array formula (commit with CTRL+SHIFT+ENTER) in Values!B4:

=MAX(IF(ISERROR(SEARCH(A4,Data!$A$1:$A$1000)),"",
--RIGHT(TRIM(Data!$A$1:$A$1000),LEN(TRIM(Data!$A$1:$ A$1000))-
SEARCH("/|\",SUBSTITUTE(TRIM(Data!$A$1:$A$1000)," ","/|\",
LEN(TRIM(Data!$A$1:$A$1000))-LEN(SUBSTITUTE(TRIM(Data!$A$1:$A$1000),
" ","")))))))

This will search Data!A1:A1000 for the value in cell Value!A4 ("Current Price")
and return the last "word" in that cell, converted to a number.

Copy this formula down to Values!B5 and you should get your "Previous Price".

For what it's worth, a COMPLETE description of your data makes this a lot
easier. The "slightly more complicated" data, specifically the leading text
before "Current Price" and the intervening rounded value before the target value
in the "Current Price" row, forced a slightly different path to the answer.

If there are more variances in your data, this solution could still fail...

Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock

JingleRock wrote:
Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock

Just a string of characters not expected to found in your data. Could have been
almost anything.

JingleRock wrote:
Glenn,

You are truly an EXCEL Function wizard!
As you know, both of the extracts work just fine.
I have learned a lot from you; thanks very much.
One last lesson: what's the story on '/|\'?

Thanks again,

JingleRock


Glad I could help. Funny how neither INDEX or MATCH ended up in the final
solution...

Try this:

=--TRIM(RIGHT(SUBSTITUTE(TRIM(LOOKUP(
2,1/SEARCH(A4,Data!A4:A52),Data!A4:A52))
," ",REPT(" ",255)),255))


--
Biff
Microsoft Excel MVP


"JingleRock" wrote in message
...
Glenn,

This is the link to my sample data: http://www.savefile.com/files/2038013
Also, refer to my comments about this file that I posted this
afternoon.

JingleRock