Hyperlink to Specific Cell in Same Sheet

[Oakwoodbespoke]

Hi

I have a long list that i have to keep looking through and entering new data into the next blank cell.
I'm trying to have a hyperlink at the top of the sheet that i can click to go to the next blank cell in a specific column.

I have successfully worked out how to find the next blank cell in the appropiate column.

=MATCH(TRUE,ISBLANK(Table1[Cost Inc Vat]),0)

although the table starts on row 4 so I need to add 3 to the above result.

So I have a cell that does that with a result of 413 (410 +3)

I have a made a hyper link to take me to a cell

=HYPERLINK(CONCATENATE("#",I2,"!j1"),"Go To Cell")

I2 is the name of the worksheet (Purchases)

But rather than going to J1 I need it to go to Column J but row 413 (as referenced in cell J2)

so kind of like =@INDIRECT("Purchases!J"&$J$2)

But how do I incorporate indirect into hyperlink ?

Any help much appreciated

 

[SanjayGMusafir]

Referring to one of the videos by Excel Campus Jon (I don't know I can share the video link here or not, due to forum rules)

Best way is to use named Ranges and then hyperlink to them

I explain -

Let X45 be the cell you want to hyperlink
Simply name that cell (using define names in formula) - say "Trial1"

Now hyperlink to this named range

Best part is no matter where the cell moves, cut-pasted the hyperlink to a named range works

I have more than 100s of such links working

Moreover you can use such named ranges for using their values in formulae.

 

[Joe4]

Referring to one of the videos by Excel Campus Jon (I don't know I can share the video link here or not, due to forum rules)

You are allowed to post link to existing videos (especially if they are not their own).

What we do not like is when people make a video to answer a specific new question, post it to their own site, and post a link to that video.
That kind of falls under the category of using the forum to try to drive traffic to your site.

It sounds like that certainly does not fall into that category, so you are welcome to post the link to that video.

 

[Fluff]

You can try

=HYPERLINK(CONCATENATE("#",I2,"!j",MATCH(TRUE,ISBLANK(Table1[Cost Inc Vat]),0))+3,"Go To Cell")

 

[Oakwoodbespoke]

=HYPERLINK(CONCATENATE("#",I2,"!j",MATCH(TRUE,ISBLANK(Table1[Cost Inc Vat]),0))+3,"Go To Cell")

Not working im afraid, gives #VALUE! in cell (cell with hyper link)

 

[Fluff]

I got a bracket in the wrong place it should be

=HYPERLINK(CONCATENATE("#",I2,"!j",MATCH(TRUE,ISBLANK(Table1[Cost Inc Vat]),0)+3),"Go To Cell")

 

[Oakwoodbespoke]

=HYPERLINK(CONCATENATE("#",I2,"!j",MATCH(TRUE,ISBLANK(Table1[Cost Inc Vat]),0)+3),"Go To Cell")

Absolutely fantastic many thanks much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.