Help with a Formula Please

[mbqc]

Is there a faster way to hyperlink in excel 2003 other tha
Help/Microsoft Excel Help/Create a link between cells in the sam
worksheet or workbook

I have (1) workbook with 2 sheets.

If I use column A in sheet 1 to list the following going down the lis
like this:
(A1) Apple
(A2) Boy
(A3) Charlie
(A4) Dan
(A5) Ed
(A6) Frank
(A7) Ginger

And in Sheet 2, I have the same names again, but use colums B,C,D,E,an
F for other information (address, phone, fax, email and notes)

Is there a way that I can hyperlink Charlie from Sheet 1 (A3) t
Charlie in sheet 2 (Who may now be (A41) ,*_without_going__*to Sheet
to see what cell each contact name (or Charlie) is in ?

Sheet 2 is really a Master Contact list with 3,000 names that will gro
only for 1 year.

Sheet 1 may only have 30 names total that need linked. There will be
total of about 10-15 sheets with different names, but the idea is t
link the name from any sheet to the master list of Sheet 2


Trac

 

[Trevor Shuttleworth]

Sounds like you should be looking at VLOOKUP

Something like:

=VLOOKUP(A1,Sheet2!A:F,2,FALSE)

Would use the value in cell A1 to lookup the table in Sheet2, columns A to
F, and return the matching entry from the second (2) column, column B ...
which I guess is the Address.

Regards

Trevor

 

[Guest]

Try:

=HYPERLINK("[Book1]Sheet2!A" & MATCH(A2,Sheet2!A1:A100,0))

Change "Book1" to your w/book name

HTH

 

[mbqc]

I renamed my book to "test" and then entered =HYPERLINK("[test]Sheet2!A"
& MATCH(A2,Sheet2!A1:A100,0)) into cell A1 on Sheet 1

Lost the name Apple and gained a hyperlink.
Typed back into cell A1 the word Apple and it was a hyperlink that gave
me this error "Cannot open the specified file"

Hmmm

Try:

=HYPERLINK("[Book1]Sheet2!A" & MATCH(A2,Sheet2!A1:A100,0))

Change "Book1" to your w/book name

HTH

Is there a faster way to hyperlink in excel 2003 other that
Help/Microsoft Excel Help/Create a link between cells in the same
worksheet or workbook

I have (1) workbook with 2 sheets.

If I use column A in sheet 1 to list the following going down the list
like this:
(A1) Apple
(A2) Boy
(A3) Charlie
(A4) Dan
(A5) Ed
(A6) Frank
(A7) Ginger

And in Sheet 2, I have the same names again, but use colums B,C,D,E,and
F for other information (address, phone, fax, email and notes)

Is there a way that I can hyperlink Charlie from Sheet 1 (A3) to
Charlie in sheet 2 (Who may now be (A41) ,*_without_going__*to Sheet 2
to see what cell each contact name (or Charlie) is in ?

Sheet 2 is really a Master Contact list with 3,000 names that will grow
only for 1 year.

Sheet 1 may only have 30 names total that need linked. There will be a
total of about 10-15 sheets with different names, but the idea is to
link the name from any sheet to the master list of Sheet 2


Tracy


--
mbqc
------------------------------------------------------------------------
mbqc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34207
View this thread: http://www.excelforum.com/showthread.php?threadid=574395

 

[Dave Peterson]

How about:

=HYPERLINK("#'sheet 2'!A"&MATCH(A1,'Sheet 2'!A:A,0),"Click me")

Note the apostrophes surrounding the worksheet's name. I'm not sure you'll need
them, but they won't hurt.

 

[mbqc]

When I place the string
=HYPERLINK("#'sheet 2'!A"&MATCH(A1,'Sheet 2'!A:A,0),"Click me")
Into Cell A1 on Sheet 1 it overwrites the name Apple and when I past
and hit enter I get a pop up window titled "Update Values: Sheet2"

Ideas

 

[Dave Peterson]

Put this in B1 -- not A1. The formula uses the value in A1 to create the
hyperlink.

And change 'Sheet 2' to match the name of the sheet that you want to jump to.

In one of your messages, you used "sheet 2". I thought that this was the name
of the sheet.