SOLVED

Link from Drop Down List to first blank cell on a specific worksheet (same WB)

Brass Contributor

Hello! I'm have a workbook with many data entry tables, each in a separate worksheet in the workbook. I need to set up a directory with a list of each of the 'Tabs' that will function as a hyperlink which sends the user to the last open cell in the table of the specified worksheet.

 

I've tried one suggestion that I couldn't get to work correctly. Namely, by creating a 'dummy' named range pointing to a cell on order to get the hyperlink established, and then using the following formula to return the correct worksheet. I've tried using the INDIRECT(ADDRESS(XMATCH formulas without any success. Any assistance would be helpful. Thank you! 

8 Replies
I want to add that I'm using office 365 Online and I'm not able to use macros due to user restrictions. So I need a formula solution if possible. I only have 5 tabs right now, but will be adding more than 50 eventually. Way too many for users to have to scroll through. Landing on the next blank cell is not as critical as sending them to the correct tab. :)

Re:Landing on the next blank cell is not as critical as sending them to the correct tab. :)

I am not sure your requirements.
As to me,I would like create a form especially for append new entry.
Just like MS FORM.


@peiyezhu 

Sorry. I'm not very practiced at asking questions in the forum.
I have a "Directory" tab where a user can find the office location for a particular Staff person. The other tabs are labeled with each office that exists, and there are many. I created a list of the offices. First, I created a 'dummy' reference (no formula) to a random cell so I could create a named range. Then I used this formula to point the hyperlink to the named range and go to the corresponding Tab.

 

=INDIRECT(ADDRESS(1,1,,,INDIRECT("cell where dropdown is")))

 

This works great to jump to the correct tab. Now, I'm trying to figure out a way to have the user land on the first blank cell in the first column for data entry. I've been trying to use a nested XMATCH function in place of the first (1) after the ADDRESS function. I bolded it in the formula above. Apparently, I'm not doing it right. Please let me know if I should start a new discussion for this or what I can best do to get an answer. Thank you! :)

@Marcus_Booth 

 

if in sheet1,column A:

 

data1
data2

 

=COUNTA(Sheet1!A:A)

 

You can get 2 which Counts the number of arguments that are not empty.

 

https://support.microsoft.com/article/4c35510e-bdce-4135-a20e-23c9698a22ea

best response confirmed by Marcus_Booth (Brass Contributor)
Solution

@Marcus_Booth Use the HYPERLINK function to create a clickable link to the last used cell in the specified worksheet. For example, with the target sheet name in cell A2:

 

=HYPERLINK("#"&ADDRESS(COUNTA(INDIRECT("'"&A2&"'!A:A"))+1,1,,,A2))

 

See attached example file...

Thank you all for your assistance with this. The response that I ultimately used is not visible here any longer. I marked it as BEST RESPONSE, but apparently it didn't register. It was provided by Sergei Baklan. in this thread, so I'm not sure what I did wrong. Is there a way to mark it as completed?

@Marcus_Booth To avoid confusion, it's best to pursue the answer to a single question in a single thread by providing feedback and clarity as needed. Sergei's response (similar to mine) was posted to your secondary thread found here:

I see. Thank you for the tip! I will take more care next time. I can see that it's important to keep things organized. I went ahead chose the best response on this one. It was similar and spot on. Thank you again for your assistance, with everything. :)
1 best response

Accepted Solutions
best response confirmed by Marcus_Booth (Brass Contributor)
Solution

@Marcus_Booth Use the HYPERLINK function to create a clickable link to the last used cell in the specified worksheet. For example, with the target sheet name in cell A2:

 

=HYPERLINK("#"&ADDRESS(COUNTA(INDIRECT("'"&A2&"'!A:A"))+1,1,,,A2))

 

See attached example file...

View solution in original post