Cell value from another worksheet

[Excelplate]

Hi All

I have one worksheet called "Main" which contains instrument details
and recalibration due dates.
Individual instrument details are called up to include location code of
where they are in the country, from a combo box and displayed in 6
cells.

The second worksheet called "Depfind", Containes all the addreses and
Telephone numbers of the relevent location codes, eg ab,cd,123. etc.
Again a combo box is used to bring up the contact details on a location
code typed in the box.

What I am trying to do is instead of typing the location code every
time as there are over 500.
Have the location cell on the main worksheet linked to a cell on the
depfind that will call up and display the contact information each
time. There are about 3000 records in the main worksheet with many
types of insruments at the same location.

I have tryed all sorts, INDEX,VLOOKUP,Etc Cant seem to get anything to
work.Access does this perfectly by using columns from the combo box.

Anyone have any ideas. Thanks in advance
Exelplate

 

[Max]

Have the location cell on the main worksheet linked to a cell

on the depfind that will call up and display the contact
information each time.

Some thoughts .. perhaps we could use hyperlinks ..

A sample construct is available at:
http://www.savefile.com/files/3059257
AutoHyperlink_Excelplate_wks.xls

Assume you have this set-up

In Main, cols A & B, data from row2 down
------------
Instr# Recalib_DD
1111 Date1
1112 Date2
1113 Date3
1114 Date4
1115 Date5
etc

In Depfind
--------
Instr# are listed in col A &
Location codes are in col D (col D is the lookup col),
with data from say, row4 down:

Instr# Field1 Field2 Location Code
1114 Data1 Data11 1
1115 Data2 Data12 2
1113 Data3 Data13 3
1111 Data4 Data14 4
1112 Data5 Data15 5
etc

Note that the Instr#s above (in Depfind) are intentionally scrambled to be
different from the order in Main. This is for illustration purposes when we
check that clicking on the hyperlinks we're going to create in Main does
jump to the correct cells in Depfind.

In Main
---------
Put a label in C1: Location Code

Put in C2:
=HYPERLINK("#"&CELL("address",
INDIRECT("'Depfind'!D"&MATCH(A2,Depfind!A:A,0))),INDIRECT("'Depfind'!D"&MATC
H(A2,Depfind!A:A,0)))
Copy C2 down

This will create hyperlinks in C2, C3, etc which extract/display the
location codes (matching the Instr#) from Depfind as the friendly names in
the cells. And when you click on the hyperlinks, they'll bring you to the
correct cells in col D in Depfind (Location code col).

 

[Excelplate]

Hi Max

And from Singapore WOW!
I am from the UK
Thank you so much for your input. I will try your recomendation
tonight.
In the mean time I put =t9 to call up the depot code and tried to work
out how to reference that cells content eg "AB" IN the cell below to
call up the address details below from worksheet depfind.

One thing I did discover, because I think trying so many different
combinations in cell t9 VLOOKUP, FIND etc, etc. I type in =t9 press
enter and it returns =t9 the next cell to it returns the correct value
strange Huh have you ever had that one?

I will keep you posted through the group

Best Regards

Excelplate

 

[Max]

You're welcome, Excelplate !

.. I type in =t9 press enter and it returns =t9
the next cell to it returns the correct value strange ..

Probably the cell was inadvertently formatted as TEXT before the formula was
entered ? One way to restore is to re-format the cell as General/Number
(via Format > Cells), then re-enter the formula.