• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink cell reference

C

comp764

Member
Hello,

I have some hyperlinks setup that take me deferent worksheets in a workbook. I was wondering if there's a formula that can be enter into the cell reference of the hyperlink to take me the next empty cell in column A in a worksheet.


Thanks
 
How about something like this?

=HYPERLINK("[Book1.xls]Sheet1!A"&COUNTA(Sheet1!A:A)+1,"Next Empty")
 
Hi Narayan,


I had not see the # trick before, but you are correct, that works the same way, and is shorter. I'm assuming the # only refers to same sheet as the formula is on, however?
 
Hi Luke ,


The following appears to be the syntax :


=HYPERLINK("#Sheet2!A"&COUNTA(Sheet1!A:A)+1,"Next Empty")


Narayan
 
Cool! That will certainly save a lot of aggravation that I normally have due to file/sheet names changing. =)
 
Hi,

I used this formula =HYPERLINK("#Sheet2!A"&COUNTA(Sheet1!A:A)+1,"Next Empty") but returned a reference of valid error.

This does go into hyperlink cell reference?

Not sure why it's referring to two different sheets in the formula.

I'm riunning excell 2007 don't know if that matters.

Thanks
 
Hi Dan ,


Sorry for having confused you ; that was just a communication between Luke and me !


If your formula is to be entered in a cell like say D15 on sheet Sheet1 , then entering the following formula in D15 :


=HYPERLINK("#A"&COUNTA(Sheet1!A:A)+1,"Next Empty")


and clicking on this hyperlink , will take you to the next empty cell in column A. Of course , this assumes that all the cells in column A from A1 till a blank cell are having data i.e. there are no blank cells in between A1 and the last filled cell in column A.


Narayan
 
The formula just goes into a regular cell. Don't use the Insert - Hyperlink menu.

Our apologies, both sheet references should be to the sheet that you are wanting to go to.
 
Got the formula working. Thanks, you guys awsome!

Thanks again for alll your help it is greatly appreciated.

Dan
 
You must log in or register to reply here.
Back
Top