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!

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.

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.

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

DOLA

All hypothetical of course. Again not really familiar with the function, and on my phone, so haven't tested any of this out.

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.

Thanks, guys. I didn't know hyperlink was a function. I had already done them all manually, but this will help for next time!