Hyperlinking rows labels with column labels between 2 sheets in same workbook

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

I am trying to figure out the best way to do this. I have 2 sheets in
one workbook that are identical except that they are transposed. They
are fairly large and I want to be able to "mass" hyperlink the column
labels of sheet 1 to the row labels of sheet 2. I have tried
accomplishing this by combining the HYPERLINK and OFFSET worksheet
functions and have had no luck whatsoever. Is there a way to do this
without individually creating hundreds of hyperlinks?


Example: Sheet 1 Sheet 2
A B C D E F...IV 1 2 3 4 5 6 ...256
1 A
2 B
3 C
4 D
5 E
6 F

If anyone knows the answer to this please help!
 
Hi Daniel,

The HYPERLINK function asks for a *text* value containing the workbook name
enclosed in square brackets and the cell reference. So, it is expecting

"[test.xls]Sheet1!$A$1"

as the first argument. This has to be constructed dynamically for your
purposes. The following formula will work, but the workbook MUST have been
saved at least once before it can be used:

=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),
FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&
ADDRESS(COLUMN(),1,1,1,"Sheet1"),INDEX(Sheet1!$A:$A,COLUMN()))


Steve D.
 
Thank you very much! It worked perfectly. I only had to change the
INDEX array, so that the array was the entire spreadsheet. This may
not have been necessary but for $A:$A the same text would come up for
each link.

One follow up question:
Is there any way to make it so that the links do not get changed if a
row or column is inserted?

Thank you much for your help.

-Daniel
 
Glad to help

Daniel said:
Thank you very much! It worked perfectly. I only had to change the
INDEX array, so that the array was the entire spreadsheet. This may
not have been necessary but for $A:$A the same text would come up for
each link.
Click to expand...


I was under the impression that you were only interested in the labels, not
the whole sheet. The INDEX(Sheet1!$A:$A,COLUMN()) construct ensures that
the column label text changes to the label from the equivalent row in the
first sheet.

One follow up question:
Is there any way to make it so that the links do not get changed if a
row or column is inserted?
Click to expand...


From the way you described the sheets, I thought that you would insert an
equivalent row in sheet1 for each column inserted in sheet2, which would
solve that problem.

So, you wanted a way to set them up dynamically, but then have them fixed in
place? Don't ask for much do you! :) Seriously though, what you needed in
the first place was a macro to do the work for you. However, you can still
fix the formulae as given, by going through each, selecting

MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-F
IND("[",CELL("filename"))+1)&ADDRESS(COLUMN(),1,1,1,"Sheet1"

and pressing F9.

HTH
Steve D.
 
Back
Top