Forum Discussion
Link from Drop Down List to first blank cell on a specific worksheet (same WB)
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...
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.
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! 🙂
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
