Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking cells between worksheets in same workbook
Hello, I am trying to link telephone contact details on one worksheet
(Named: Contact Details) to Desk Positions on another worksheet (Named: Floor Plan) in the same Excel 2003 workbook. The worksheet (Named: Contact Details) is a simple list with column headings in row 1. The column headings a Column A: Title Column B: First Name Column C: Last Name Column D: Full Name (using concatenation of B and C) Column E: Phone (Work) Column F: Location (work) Column G: Position Column H: Sub Section Column I: Directorate I am trying to link in the following order: (Position, Title, Full Name and Phone) to worksheet (Named: Floor Plan). For example: in worksheet (Named: Floor Plan), there is a desk at cell: H2. Cell H3 ='Contact Details'!G127 ---(Position) Cell H3 ='Contact Details'!A127 ---(Title) Cell H3 ='Contact Details'!D127 ---(Full Name) Cell H3 ='Contact Details'!E127 ---(Phone) When I either add, delete or sort the worksheet (Named: Contact Details), then the Position, Title, Full Name and Phone details change in the worksheet (Named: Floor Plan). I tried to use both absolute and relative cell references, but this did not help. Is there a way in which I can link both the worksheets (as described above) and still be able to add, delete or sort worksheet (Named: Contact Details)? Any assistance in this matter would be greatly appreciated. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking cells between worksheets in same workbook
I would try to find a unique id for each contact. Place that in the first
column, then use =vlookup() to return the data. If column D is a unique list (and it may not be!), then you could use =index(match()) to return the values. Debra Dalgleish has lots of notes: http://www.contextures.com/xlFunctions02.html (for =vlookup()) and http://www.contextures.com/xlFunctions03.html (for =index(match())) and http://contextures.com/xlFunctions02.html#Trouble Chris wrote: Hello, I am trying to link telephone contact details on one worksheet (Named: Contact Details) to Desk Positions on another worksheet (Named: Floor Plan) in the same Excel 2003 workbook. The worksheet (Named: Contact Details) is a simple list with column headings in row 1. The column headings a Column A: Title Column B: First Name Column C: Last Name Column D: Full Name (using concatenation of B and C) Column E: Phone (Work) Column F: Location (work) Column G: Position Column H: Sub Section Column I: Directorate I am trying to link in the following order: (Position, Title, Full Name and Phone) to worksheet (Named: Floor Plan). For example: in worksheet (Named: Floor Plan), there is a desk at cell: H2. Cell H3 ='Contact Details'!G127 ---(Position) Cell H3 ='Contact Details'!A127 ---(Title) Cell H3 ='Contact Details'!D127 ---(Full Name) Cell H3 ='Contact Details'!E127 ---(Phone) When I either add, delete or sort the worksheet (Named: Contact Details), then the Position, Title, Full Name and Phone details change in the worksheet (Named: Floor Plan). I tried to use both absolute and relative cell references, but this did not help. Is there a way in which I can link both the worksheets (as described above) and still be able to add, delete or sort worksheet (Named: Contact Details)? Any assistance in this matter would be greatly appreciated. Kind regards, Chris. *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking cells between worksheets in same workbook
Thanks Dave, I downloaded a sample workbook that has an example of a
=Index(match()) and am having lots of difficulties understanding how the function works due to it relating to arrays - which i know nothing about. I'll keep plugging away at it and see how I go. Cheers, Chris. *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking two worksheets in the same workbook | Excel Worksheet Functions | |||
Linking worksheets in the same workbook by names not cells | Excel Discussion (Misc queries) | |||
Help creating workbook with worksheets "linking" cells | Excel Worksheet Functions | |||
linking several worksheets to one workbook | Excel Discussion (Misc queries) | |||
Linking cells in a worksheet to other worksheets in a workbook | Excel Discussion (Misc queries) |