Using row function in indirect

Hello,

I have been trying to create a hyperlink that changes which sheet it goes to, according to which row the formula is in. For example, I want to click on cell A10 (which contains ABC), which will take me to cell A1 on sheet ABC. However I also want to be able to copy and paste this hyperlink one or more cells down and have it look at cell A11, A12, etc.

I know that if I do this:
#INDIRECT("'"&A10&"'!"&"A1")
it will work but when I copy and paste it in another cell, the A10 stays the same.

I tried playing around with this:
#INDIRECT("'"&"A"&ROW()&"'!"&"A1")
which doesn't seem to work.

On its own, "A"&ROW() returns the correct cell, but when it is inside the indirect function, I can't seem to get it to work.

Any help would be greatly appreciated,

Chris

How do you get: #INDIRECT("'"&A10&"'!"&"A1") to hyperlink?

I would try: =HYPERLINK("#'"&A10&"'!"&"A1",A10)

copied down

That's interesting. I have never done that before... i.e using a formula in the Insert|Hyperlink feature....

My suggestion was putting my formula cells in adjacent columns.

Can you explain exactly how your are accomplishing the task? Are you putting the link in adjacent cells? If so, when I copy down, the actual link remains to the first one I added the link to... i.e. it always links to same sheet.

How are you copy/pasting? If I copy down it links always to the sheet named in cell A1.

I am not sure it has to do anything with the formula.

If I go into any random cell in Sheet1, and then right-click and select Hyperlink.

Click Place in this Document from the left Link to pane

Then select say STU from the large white pane with the option tree and click OK. The cell now has a hyperlink to sheet STU, cell A1.

Yes I know you could right click, press hyperlink and select a sheet from the option tree, but if you copy and pasted this in another row, it would still be referencing STU and not whatever is in the new cell. This is not what I wanted to happen. I believe that if you tried copying and pasting the cells I had already created, they would link to the new sheets.

However, I don't know how to enter the hyperlink formula for more cells in Windows as it just gives you an option tree and not much more. On Office 2011 for Mac, there is a hyperlink box where you can type a formula. I used a formula containing the row function so no matter where the cell is located, it will look at a certain column (A in my example) and the row in which the cell is (can be 1,2,3,4 etc). Using the indirect function, Excel will look at whatever is inside that cell and create a link to a sheet with the same name.

I know this isn't a perfect formula for every cell in a sheet, but it works for my needs.

Chris

Can you paste a .jpg of that hyperlink box out of curiousity?

Thanks.

I think it may be that "anchor" field that PC Excel doesn't have.... Maybe Excel 2010 has it, I don't have that version as of yet... so it may remain a mystery for now...

but thanks for trying to clarify for me. Appreciated.