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