Can someone explain how this works?

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
a numeric value in cell B2 which changes from week to week. I would like to
be able to record the numeric value in cell B2 from the spreadsheet to a new
spreadsheet that has exactly the same row and column names, however, the row
and column location may change in the original file i.e. John Smith may move
into cell A3 and Balance may move into cell C1. How do I record this
information into the new spreadsheet without physically going into the file
and extracting it?

Assuming that the table you want to fill starts in sheet 2 in the same cell
ref as your original table, (so A2 for the names and B1 for balance etc), and
your original table is in sheet one. Paste this formula into cell B2 and
copy it across to the other cells.

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!A2;Sheet1!$A$ 2:$A$6;0);MATCH(Sheet2!B1;Sheet1!$B$1:$D$1;0))

Its only set to read a table that is between B2 to D6 in sheet 1, adjust
these refs to suit whatever size your table is.

"DaveKid" wrote:

Can someone explain how this works?

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
a numeric value in cell B2 which changes from week to week. I would like to
be able to record the numeric value in cell B2 from the spreadsheet to a new
spreadsheet that has exactly the same row and column names, however, the row
and column location may change in the original file i.e. John Smith may move
into cell A3 and Balance may move into cell C1. How do I record this
information into the new spreadsheet without physically going into the file
and extracting it?

Sorry fixed the wrong bit. Use this instead

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!$A2;Sheet1!$A $2:$A$6;0);MATCH(Sheet2!B$1;Sheet1!$B$1:$D$1;0))

Dont hit enter, its an array formula so u need to enter it with
shift+ctrl+enter

If it doesn't work change all the ; in the above formula to ,

I use a foreign computer so it takes diff signs, depends on what ur using.

"LiAD" wrote:

Assuming that the table you want to fill starts in sheet 2 in the same cell
ref as your original table, (so A2 for the names and B1 for balance etc), and
your original table is in sheet one. Paste this formula into cell B2 and
copy it across to the other cells.

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!A2;Sheet1!$A$ 2:$A$6;0);MATCH(Sheet2!B1;Sheet1!$B$1:$D$1;0))

Its only set to read a table that is between B2 to D6 in sheet 1, adjust
these refs to suit whatever size your table is.

"DaveKid" wrote:

Can someone explain how this works?

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
a numeric value in cell B2 which changes from week to week. I would like to
be able to record the numeric value in cell B2 from the spreadsheet to a new
spreadsheet that has exactly the same row and column names, however, the row
and column location may change in the original file i.e. John Smith may move
into cell A3 and Balance may move into cell C1. How do I record this
information into the new spreadsheet without physically going into the file
and extracting it?

This has partially worked. Its identified the correct row but picks the
column next to the one I want???

"LiAD" wrote:

Sorry fixed the wrong bit. Use this instead

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!$A2;Sheet1!$A $2:$A$6;0);MATCH(Sheet2!B$1;Sheet1!$B$1:$D$1;0))

Dont hit enter, its an array formula so u need to enter it with
shift+ctrl+enter

If it doesn't work change all the ; in the above formula to ,

I use a foreign computer so it takes diff signs, depends on what ur using.

"LiAD" wrote:

Assuming that the table you want to fill starts in sheet 2 in the same cell
ref as your original table, (so A2 for the names and B1 for balance etc), and
your original table is in sheet one. Paste this formula into cell B2 and
copy it across to the other cells.

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!A2;Sheet1!$A$ 2:$A$6;0);MATCH(Sheet2!B1;Sheet1!$B$1:$D$1;0))

Its only set to read a table that is between B2 to D6 in sheet 1, adjust
these refs to suit whatever size your table is.

"DaveKid" wrote:

Can someone explain how this works?

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
a numeric value in cell B2 which changes from week to week. I would like to
be able to record the numeric value in cell B2 from the spreadsheet to a new
spreadsheet that has exactly the same row and column names, however, the row
and column location may change in the original file i.e. John Smith may move
into cell A3 and Balance may move into cell C1. How do I record this
information into the new spreadsheet without physically going into the file
and extracting it?

Can u write the exact input table location and titles you have and the exact
output table you would like? I guess the references are different.

The text must be EXACTLY the same in terms of letters, capitals and spaces
otherwise it cannot match it.

"DaveKid" wrote:

This has partially worked. Its identified the correct row but picks the
column next to the one I want???

"LiAD" wrote:

Sorry fixed the wrong bit. Use this instead

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!$A2;Sheet1!$A $2:$A$6;0);MATCH(Sheet2!B$1;Sheet1!$B$1:$D$1;0))

Dont hit enter, its an array formula so u need to enter it with
shift+ctrl+enter

If it doesn't work change all the ; in the above formula to ,

I use a foreign computer so it takes diff signs, depends on what ur using.

"LiAD" wrote:

Assuming that the table you want to fill starts in sheet 2 in the same cell
ref as your original table, (so A2 for the names and B1 for balance etc), and
your original table is in sheet one. Paste this formula into cell B2 and
copy it across to the other cells.

=INDEX(Sheet1!$B$2:$D$6;MATCH(Sheet2!A2;Sheet1!$A$ 2:$A$6;0);MATCH(Sheet2!B1;Sheet1!$B$1:$D$1;0))

Its only set to read a table that is between B2 to D6 in sheet 1, adjust
these refs to suit whatever size your table is.

"DaveKid" wrote:

Can someone explain how this works?

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
a numeric value in cell B2 which changes from week to week. I would like to
be able to record the numeric value in cell B2 from the spreadsheet to a new
spreadsheet that has exactly the same row and column names, however, the row
and column location may change in the original file i.e. John Smith may move
into cell A3 and Balance may move into cell C1. How do I record this
information into the new spreadsheet without physically going into the file
and extracting it?