Linking cells between worksheets in same workbook

[Chris]

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 are:

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]

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]

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 ***