Pulling data from multiple spreadsheet
 

4 tables: the top two tables are noted as Full, 1 is graded Platinum, the 2nd
Premier+, % runs across the top row and FICO numbers down left column, the
next two tables are noted as Stated, 1 is graded Platinum, the 2nd Premier+.
By entering 4 criteria Full or stated,grade(Platinum or premier+), % and
FICO, can I get the cross referenced Interest rate. ex: Full, Platinum,
680,70%=7.

Full 65% 70% 75% 80%
Platinum 680 7 7 7 7.1256
Platinum 660 7 7.125 7.125 7.25
Platinum 640 7.125 7.125 7.25 7.375
Platinum 620 7.375 7.5 7.5 7.625
Full 65% 70% 75% 80%
Premier+ 680 7.25 7.25 7.25 7.375
Premier+ 660 7.25 7.375 7.375 7.5
Premier+ 640 7.375 7.375 7.5 7.625
Premier+ 620 7.625 7.75 7.75 7.875


Stated 65% 70% 75% 80%
Platinum 680 7.75 8 8 8.125
Platinum 660 7.75 8.125 8.125 8.25
Platinum 640 7.875 8.125 8.25 8.375
Platinum 620 8.125 8.5 8.5 8.75
Stated 65% 70% 75% 80%
Premier+ 680 8 8.25 8.25 8.375
Premier+ 660 8 8.375 8.375 8.5
Premier+ 640 8.125 8.375 8.5 8.625
Premier+ 620 8.375 8.75 8.75 9

Pulling data from multiple spreadsheet
 

One way which could deliver this ..

A sample construct is available at:
http://www.savefile.com/files/520405
Pull data from amongst multiple tables.xls

The 4 source tables are assumed in cols A to F in Sheet1

Create 4 named cells (via InsertNameDefine)
FullPlatinum =Sheet1!$A$1
FullPremier =Sheet1!$A$7
StatedPlatinum =Sheet1!$A$13
StatedPremier =Sheet1!$A$19
(the above are the top left intersection cells of the 4 source tables)

Assuming the source input "components" will be entered (or selected via DV
lists) in H2:K2, where H2 = Full or Stated, I2 = Platinum or Premier, J2 =
65%, 70%, etc, K2 = 680, 660, etc

Then in L2:
=OFFSET(INDIRECT(H2&I2),MATCH(J2,OFFSET(A:A,,1),0)-1,MATCH(K2,$1:$1,0)-1)
will return the required cross-referenced value from the correct table. Copy
L2 down as required to return correspondingly for other input sets in H3:K3,
H4:K4, etc.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mark C" wrote:
4 tables: the top two tables are noted as Full, 1 is graded Platinum, the 2nd
Premier+, % runs across the top row and FICO numbers down left column, the
next two tables are noted as Stated, 1 is graded Platinum, the 2nd Premier+.
By entering 4 criteria Full or stated,grade(Platinum or premier+), % and
FICO, can I get the cross referenced Interest rate. ex: Full, Platinum,
680,70%=7.

Full 65% 70% 75% 80%
Platinum 680 7 7 7 7.1256
Platinum 660 7 7.125 7.125 7.25
Platinum 640 7.125 7.125 7.25 7.375
Platinum 620 7.375 7.5 7.5 7.625
Full 65% 70% 75% 80%
Premier+ 680 7.25 7.25 7.25 7.375
Premier+ 660 7.25 7.375 7.375 7.5
Premier+ 640 7.375 7.375 7.5 7.625
Premier+ 620 7.625 7.75 7.75 7.875


Stated 65% 70% 75% 80%
Platinum 680 7.75 8 8 8.125
Platinum 660 7.75 8.125 8.125 8.25
Platinum 640 7.875 8.125 8.25 8.375
Platinum 620 8.125 8.5 8.5 8.75
Stated 65% 70% 75% 80%
Premier+ 680 8 8.25 8.25 8.375
Premier+ 660 8 8.375 8.375 8.5
Premier+ 640 8.125 8.375 8.5 8.625
Premier+ 620 8.375 8.75 8.75 9

Pulling data from multiple spreadsheet
 

The earlier suggestion in L2:
=OFFSET(INDIRECT(H2&I2),MATCH(J2,OFFSET(A:A,,1),0)-1,MATCH(K2,$1:$1,0)-1)

uses/relies on the identical structure of the 4 tables wrt the values of the
horiz & vertical axes, viz the 65% ... 80%, and the 680, ... 620 parts are
all the same for the 4 tables

Should there be the possibility that the values of the horiz & vert axes in
the 4 tables might be different from one table to the other, use this more
robust formula instead in L2:
=OFFSET(INDIRECT(H2&I2),MATCH(J2,OFFSET(INDIRECT(H 2&I2),,1,5),0)-1,MATCH(K2,OFFSET(INDIRECT(H2&I2),,,,6),0)-1)
Copy down as before
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---