12-19-2012, 09:23 AM | #1 | ||
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
Another Excel Question
So I'm creating some hyperlinks in a file I'm working on. Cell A1 has a hyperlink that points to cell M1 in another tab. Cell A2 has one that points to cell N1 in the other tab, and so on. I see where to create the hyperlinks and tell it where to point to, but I've got 120 of these -- is there a way to use the power of excel to do this in one shot?
Thanks! |
||
12-19-2012, 09:31 AM | #2 |
Pro Starter
Join Date: Jan 2002
Location: Pittsburgh
|
I'm not very familiar with it, but sounds like the INDIRECT function would work for you.
INDIRECT Function Thinking you should be able to list your cells you'll be linking to in a (hidden) column, and look to those in your hyperlink, then be able to drag down.
__________________
"Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!" |
12-19-2012, 09:43 AM | #3 |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
But looking at the hyperlink menu, I don't see how to enter in the cell I want to link to, other than by typing it in.
|
12-19-2012, 11:39 AM | #4 |
Pro Starter
Join Date: Jan 2002
Location: Pittsburgh
|
Ah...are you using the hyperlink button from the insert menu?
Using the formula instead would be =HYPERLINK([sheetname]![cell reference],"your text here") So thinking- =HYPERLINK(Datatab!N1,"LINK") becomes =HYPERLINK(Datatab!(INDIRECT(Z1)),"LINK") where Z1 contained the reference of "N1". Then dragging this down, Z1 would change to Z2, etc
__________________
"Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!" |
12-19-2012, 11:43 AM | #5 |
Pro Starter
Join Date: Jan 2002
Location: Pittsburgh
|
DOLA
All hypothetical of course. Again not really familiar with the function, and on my phone, so haven't tested any of this out.
__________________
"Do you guys play fast tempos with odd time signatures?" "Yeah" "Cool!!" |
12-19-2012, 06:55 PM | #6 |
Coordinator
Join Date: Oct 2000
Location: Maassluis, Zuid-Holland, Netherlands
|
Using some of what Suburban Rhytm suggests:
Create the source first (M2:N2). Then make your hyperlinks in M1:N1. (As M1 contains =hyperlink(M2), etc. You can simply copy paste the formula to the adjacent cells. Next use replace on cells M1:N1 twice: once to replace ( with ($ and the second time to relace 2) with $2). Finally copy paste M1:N1 to A2 with the special transpose option and finish by getting rid of the entire row 1.
__________________
* 2005 Golden Scribe winner for best FOF Dynasty about IHOF's Maassluis Merchantmen * Former GM of GEFL's Houston Oilers and WOOF's Curacao Cocktail Last edited by MIJB#19 : 12-19-2012 at 06:56 PM. |
12-20-2012, 07:03 AM | #7 |
Coordinator
Join Date: Oct 2000
Location: Big Ten Country
|
Thanks, guys. I didn't know hyperlink was a function. I had already done them all manually, but this will help for next time!
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
Thread Tools | |
|
|