... for a complete column of paired
numbers rather than just a single Row.
The paired numbers are housed on a summary sheet
in Columns A and B starting from Row 2.

Was afraid you'd say that <g Ok, we could try this revised set-up which
uses 3, 2 variable data tables to compute the last row number, the 2nd last
row number and the difference between the last and 2nd last row number for
the paired values in Sheet2, cols A and B, in A2:B2 down

There's no change to the set-up in Sheet1 with the formula in K20:K480

In Sheet2
---------
Put in C2 (revised slightly):

=IF(OR($A2="",$B2="",$A2=$B2),"",IF(ISERROR(LARGE( Sheet1!$K$2:$K$480,COLUMNS
($A$1:A1))),"",LARGE(Sheet1!$K$2:$K$480,COLUMNS($A $1:A1))))

Copy C2 across to D2

Put in E2: =IF(OR(C2="",D2=""),"",(C2-D2)-1)
(no change)

The above 3 formulas in C2:E2 will be utilized in setting-up 3, 2 variable
data tables, the set-ups of which are described below, The 3 data tables are
identical in structure, except for the link formula in the top left corner
cell which will point to C2, D2 and E2. I chose to use the numbers 50-81
which appear to be the range of numbers within the source table in Sheet1
for listing the horizontal "x" and the vertical "y" values in the 3 data
tables (Adapt the set up accordingly to suit your actual case)

Data Table #1
-------------
Put in G1: =C2

Number across in H1:AM1, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G2:G33, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G1:AM33
Click Data Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H2:AM33 will compute the last row numbers at the x and y
intersections

Data Table #2
-------------
Put in G35: =D2

Number across in H35:AM35, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G36:G67, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G35:AM67
Click Data Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H36:AM67 will compute the 2nd last row numbers at the x and y
intersections

Data Table #3
-------------
Put in G69: =E2

Number across in H69:AM69, the numbers: 50, 51, 52, 53 ... 81 (horiz x
values)
Number down in G70:G101, the numbers: 50, 51, 52, 53 ... 81 (vertical y
values)

Select G69:AM101
Click Data Table
Enter in the boxes
For Row input cell: A2
For Col input cell: B2
Click OK

The grid H70:AM101 will compute the difference between the last and the 2nd
last row numbers at the x and y intersections

And with the 3 data tables above in place, to wrap up, we'll just need to

Put in C3:
=OFFSET($G$1,MATCH(A3,$G$2:$G$33,0),MATCH(B3,$H$1: $AM$1,0))

Put in D3:
=OFFSET($G$35,MATCH(A3,$G$36:$G$67,0),MATCH(B3,$H$ 35:$AM$35,0))

Put in E3:
=OFFSET($G$69,MATCH(A3,$G$70:$G$101,0),MATCH(B3,$H $69:$AM$69,0))

Then select C3:E3, and fill down as needed

Cols C to E will return (if found) the corresponding values of the last row
number, the 2nd last row number and the difference between the last and 2nd
last row numbers for the paired values entered in cols A and B

Adapt to suit ..

Note: You might want to set the calc mode to "Automatic except tables"
Click Tools Options Calculation tab Check "Automatic except tables"
OK
(Remember to click F9 to recalc the data tables if ncess., e.g., if you
redo/change the x and y values, etc)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sam via OfficeKB.com" wrote in message
...
Hi Max,

Thanks for your assitance. I actually need the formula to find the
information your formula returns but for a complete column of paired
numbers rather than just a single Row.

The paired numbers are housed on a summary sheet in Columns A and B
starting from Row 2.

Is it possible to provide such a Formula that uses the original Dynamic
Range "Numbers" rather than referencing the source data using the A1
reference style. The numbers to find will be referenced as A2 and B2, A3
and B3, A4 and B4 etc., down the two columns.

Regards,
Sam

--
Message posted via http://www.officekb.com