Setting up hyperlinks automatically
 

Any help here appreciated! I have a price list, with one sheet
containing products priced on a per-unit basis. Another sheet has the
same products, but in price breaks for volume purchasing. I want to
create hyperlinks so that clicking on the product code jumps to the
appropriate product code in the volume purchase sheet. I know how to do
this manually, but I have several hundred products, hence the need to
know how to automate this a bit.

Cheers :)


--
symean
------------------------------------------------------------------------
symean's Profile: http://www.excelforum.com/member.php...o&userid=26775
View this thread: http://www.excelforum.com/showthread...hreadid=400327

Perhaps this play might help a bit ..
(Link to demo file provided below)

Assume you have this set-up

In Sheet1, cols A & B, data from row2 down
------------
Prod# UnitPrice
1111 10
1112 20
1113 30
1114 40
1115 50
etc

In Sheet2, cols A & B, data from row2 down
------------
Prod# BulkPrice
1114 1
1115 2
1113 3
1111 4
1112 5
etc

In Sheet1
---------
Put a label in C1, say: BulkPrice hyperlink

Put in C2:
=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet2'!A "&MATCH(A2,Sheet2!A:A,0)))
,A2)

Copy C2 down

This will create hyperlinks in C2, C3, etc using the product #s in col A as
the friendly names. And when you click on the hyperlinks, they'll bring you
to the correct lines in Sheet2.

Link to demo file with the implemented construct:
http://www.savefile.com/files/8246269
File: AutoHyperlink_symean_wksht.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"symean" wrote in
message ...

Any help here appreciated! I have a price list, with one sheet
containing products priced on a per-unit basis. Another sheet has the
same products, but in price breaks for volume purchasing. I want to
create hyperlinks so that clicking on the product code jumps to the
appropriate product code in the volume purchase sheet. I know how to do
this manually, but I have several hundred products, hence the need to
know how to automate this a bit.

Cheers :)


--
symean
------------------------------------------------------------------------
symean's Profile:
http://www.excelforum.com/member.php...o&userid=26775
View this thread: http://www.excelforum.com/showthread...hreadid=400327

In case it wasn't noticed <g, the product #s in Sheet2 (for BulkPrice) were
intentionally scrambled to be different from the order in Sheet1. This is
for illustration purposes when we check that clicking on the hyperlinks
created does jump to the correct cells in Sheet2.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--