Hyperlink Formula Cell reference change Automatically

[disepyon]

Hello,

So this is my formula I have for my worksheet:

=IF(I13>0,HYPERLINK("#'"&A13&"'!E13",$G$11),"")

I would like to get excel to automatically change the cell reference in the part of the formula "'!E13" to change when I copy the formula to the next cell down to correspond to E14 and then when copied again down to E15 and so on.

As of now I have to manually go into each cell and change that part of the formula.

the idea is when I make a table on another worksheet, click on the hyperlink, it brings me to that cell specified rowed cell, rather than all hyperlinks to bring me to the same cell every time.

thanks,
Brent

[BadlySpelledBuoy]

You can use ROW() in the formula to do this.

=IF(I13>0,HYPERLINK("#'"&A13&"'!E"&ROW(),$G$11),"")

Depending on which row number the formula is in you may need to add or subtract to suit.
The above example would work if in row 13.

BSB

[disepyon]

Hey Thanks a lot BSB.

I almost bout got it I think.

so I have the below formula now

=IF(I13>0,HYPERLINK("#'"&A13&"'!E"&ROW(E1)+12,$G$11),"")

I have this typed into the example tab, but when I paste it into the sheet tab it replaces the ROW(E1) with ROW(#REF!) and doesnt work, I have to manually type in E1 on the sheet tab for the hyper link formula to work.

see below my file

[disepyon]

if you curious why I am doing it like this is because, this will be a part of my macro on my work template. I have tabs where data is entered in similar to example tab at bottom. when I run my filtering macro, Id like for a hyperlink to be available so I can click on it and it brings me to the sheet and specific cell.

[disepyon]

tried this formula but still not being referenced on the sheet tab, when I copy from example tab and paste on sheet tab

=IF(J13>0,HYPERLINK("#'"&A13&"'!F"&ROWS(F$1:F1)+12,"CLICK HERE"),"")

Really annoying, any Help?

[Pete_UK]

You can use this in B2 of Sheet1:

=IF(J2>0,HYPERLINK("#'"&A2&"'!F"&ROWS($1:1)+12,"CLICK HERE"),"")

and then copy down. If you try to copy the formula directly from row 13 of the other sheet, then the cell references which are not anchored by the $ symbol (i.e. J13, A13 and F1) will be adjusted by Excel. The J13 and A13 terms will be okay, as they will change to J2 and A2, but the F1 term cannot become negative, which is why you get the #REF error.

Hope this helps.

Pete

[disepyon]

if anything else, I may just have to edit the formula in VBA after its been pasted on the filtering data table I have, so when the macro is finished, the links will work.

Edit:
Never mind that wont work either, dont think any of this will work, due to the fact that when I filter, the filtered table gets sorted which will throw off the hyperlink formula.

[disepyon]

Hey Thanks Pete,

So is there a way to reformulate that allows me to copy the formula from the example sheet and paste to Sheet1 so it will work and not throw the #REF! error?

Its just that my work template workbook, the data filtering table I made a macro for, will copy the formula and then paste it on the filtered table, while all the other data that is copied will be pasted as values only. So when I am looking at the filtered table and I need to look at a owner in more detail, I can click on the hyperlink to bring me to the sheet and owner name on the specified row within the sheet.

[disepyon]

got it figured out.

Did a bunch of researching on the web and modified the formula I found so it can suit any sheet name.

=HYPERLINK("#'"&B13&"'!G"&MATCH(VLOOKUP(A13,INDIRECT("'"&B13&"'!"&"$A$13:$K$17"),7,FALSE),INDIRECT("'"&B13&"'!"&"G:G"),0),"GO TO OWNER")

Thanks for all the help will rep both of you for showing me the right path.

[disepyon]

Thought Id post up my final revision of the formula as it previously kept going to the same owner name when the hyperlink was clicked on in the Sheet1 tab.
Now it knows to go to the owner name with the correct data corresponding with it. Works by looking up all the vlookup/match values in the ref column and moves the cursor to the name in the corresponding column.

=IF(K2>0,HYPERLINK("#'"&B2&"'!G"&MATCH(VLOOKUP(A2,INDIRECT("'"&B2&"'!"&"$A$13:$K$24"),1,FALSE),INDIRECT("'"&B2&"'!"&"A:A"),0),"GO TO OWNER"),"")

for those wanting to use something like this, it is setup to use what ever sheet name you have. Like it my situation, on my work template, I made a vba macro to pull data from numerous sheets and filter and sort on criteria. when looking at the filtered table, if i want to look at a owner in more detail,I can click on the hyperlinked like in the example here and it will take me directly to that owner name in what ever sheet corresponding to. only the hyperlink formula is copied as PasteAll in the filter table to be able to use the hyperlink, the rest of the data is pasted as values only.

All right, thats all i have for this.
Later

[AliGW]

Thank you for the useful update.