Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column and Row mapping?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column and Row mapping?
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)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column and Row mapping?
Hi Luke
Tried the formula but doesnt seem to work. Does it need to reference the second sheet at any point?? Thanks "Luke M" wrote: 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)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column and Row mapping?
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,'Sh eet1'!$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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: Hi Luke Tried the formula but doesnt seem to work. Does it need to reference the second sheet at any point?? Thanks "Luke M" wrote: 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)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Column and Row mapping?
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.......... "Luke M" wrote: 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,'Sh eet1'!$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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: Hi Luke Tried the formula but doesnt seem to work. Does it need to reference the second sheet at any point?? Thanks "Luke M" wrote: 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)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DaveKid" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XML Mapping | Excel Discussion (Misc queries) | |||
Setting column widths when mapping out a field trial. | Excel Worksheet Functions | |||
mapping add-ins | Excel Discussion (Misc queries) | |||
mapping add-ins | Charts and Charting in Excel | |||
column mapping from on sheet to the next | Excel Discussion (Misc queries) |