J
JingleRock
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
"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