Retrieving data from one spreadsheet to another.

[Guest]

I have a spreadsheet (A) with a table with nothing but data. I have another
spreadsheet (B) that uses the data. Can I put in a cell on (B) a column
heading from (A), in another cell on (B) a row heading from (A) and where the
column and row intersects on (A), have that data entered in a third cell on
(B)?

 

[Myrna Larson]

On sheet B, you can enter formulas as below

in B1 =Sheet1!B1
in A2 =Sheet1!A2
in B2 =Sheet1!B2

Otherwise, if you want to enter just the text from the column and row headers
in, say, A1 and B1, and you want the data value in C1

=INDEX(Sheet1!$A$1:$K$100,MATCH(B1,Sheet1!$A:$A,0),MATCH(A1,Sheet1!$1:$1,0))

where B1 contains the row header to find on sheet 1 and A1 contains the column
header to find on sheet 1

 

[Gord Dibben]

Danny

Create names from your row and column headings on Workbook A.

First hit CRTL + A to select cells.

Insert>Name>Create....check "Left Column" and "Top Row"

Now on Workbook B use the intersection operation to capture data.

Select a cell in WkBk B and enter = sign then Insert>Name>Paste.

Paste a name from column names. Enter a <sapce> then insert a name from row
names.

You will see something like =row3_name columnE_name

Where the names are you headings.

Hit <ENTER>

Gord Dibben Excel MVP