Help with a Formula Please

[mbqc]

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

[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


"mbqc" <[email protected]> wrote in message
news:[email protected]...
>
> 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...o&userid=34207
> View this thread: http://www.excelforum.com/showthread...hreadid=574395
>

[Toppers]

Try:

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

Change "Book1" to your w/book name

HTH

"mbqc" wrote:

>
> 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...o&userid=34207
> View this thread: http://www.excelforum.com/showthread...hreadid=574395
>
>

[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

"mbqc" wrote:

>
> 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...o&userid=34207
> View this thread: http://www.excelforum.com/showthread...hreadid=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 wrote:
>
> 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...o&userid=34207
> View this thread: http://www.excelforum.com/showthread...hreadid=574395

--

Dave Peterson

[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 paste 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.



mbqc wrote:
>
> 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 paste
> and hit enter I get a pop up window titled "Update Values: Sheet2"
>
> Ideas?
>
> --
> mbqc
> ------------------------------------------------------------------------
> mbqc's Profile: http://www.excelforum.com/member.php...o&userid=34207
> View this thread: http://www.excelforum.com/showthread...hreadid=574395

--

Dave Peterson

[mbqc]

Maybe this will help instead of my explaining it here in a message.
http://www.mystops.com/test.xls

[Dav]

What Dave says works for me, but your sheet is sheet2 not sheet 2 so try

=HYPERLINK("#'sheet2'!A"&MATCH(A1,Sheet2!A:A,0),"Click me")

entered in cell b1

or =HYPERLINK("#'sheet2'!A"&MATCH(A1,Sheet2!A:A,0),a1)

if you wanted it to say the name of the day rather than click me

Regards

Dav

[mbqc]

Thanks to all for the help.
It now works.....