Setting up hyperlinks automatically

S

symean

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

Cheers :
 
M

Max

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
 
M

Max

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.
 
Top