Column and Row mapping?

[DaveKid]

I have a spreadsheet with John Smith in row A2 and Balance in column B1 and
the figure 35 in cell B2. I would like to be able to record the numeric data
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. How do I do this?

 

[Luke M]

Could do it using combination of MATCH and INDEX

Putting this formula in cell B2 on Sheet2 will let you index any value on
Sheet1 (array B2:Z100) based on your row and column names. You can then copy
and paste this cell as needed
=INDEX(Sheet1!$B$2:$Z$100,MATCH($A2,Sheet1!$A$2:$A$100,0),MATCH(B$1,Sheet1!$B$1:$Z$1,0))

 

[DaveKid]

Hi Luke

Tried the formula but doesnt seem to work. Does it need to reference the
second sheet at any point??

Thanks

 

[Luke M]

Hmm. The single cell references should be referring to whatever sheet you put
the formula in. but could be written as:

=INDEX('Sheet1'!$B$2:$Z$100,MATCH('Sheet2'!$A2,'Sheet1'!$A$2:$A$100,0),MATCH('Sheet2'!B$1,'Sheet1'!$B$1:$Z$1,0))
for added clarity.

I assumed you headings were in column A and row 1? The other idea is that I
am using XL 2003, so you may have to write the formula yourself,
unfortunately.

Luckily, the XL help file is pretty easy to follow regarding the MATCH and
INDEX functions, if you care to take a crack at writing it yourself.

 

[DaveKid]

Hi Luke

Sorry. This still doesnt work although I have tried it with just index and
it works although I am unable to reference the cells in sheet 2 therefore if
the cells move in sheet 1 it doesnt work.

OHHH..........