Forum Discussion
Help Needed: Nest XMATCH in h-linked drop-down to land on first blank record in the destination Tab
I'm sorry to post again. My last post was not clear enough. Lots of views, but no answers. I'm not very practiced at asking questions in the forum. Please bear with me and don't hesitate to offer posting advice. 
Anyway, 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 (bolded it in the formula above). Apparently, I'm not doing it right. Please let me know if the correct syntax or if there is a better way. Thank you! 🙂
Please stay on the cell with drop-down list, when on ribbon Data->Data validation. It looks like
In Source it could be text as here, or usually reference on the range within workbook. Expand reference with names of new tabs and correct the Source above. It will be like
24 Replies
- This is the BEST RESPONSE! Thanks again Sergei!!
Marcus_Booth , you are welcome, glad to help
If drop-down is in cell B2 and we speak about first column in another sheet, that could be
=HYPERLINK("#" & ADDRESS(XMATCH(,INDIRECT("'" & B2 & "'!A:A") ),1,,,INDIRECT("B2") ), "mycell" )- Thank you very much for your response! I'm not sure if this is to be nested in my original formula or is this a replacement for the way I'm approaching it. One thing I just noticed is that I'm not able to add to my list of 'office tabs' and have it expand accordingly in my drop-down. Somehow, I need to use a named list that automatically expands. I will be adding many offices (new office tabs) down the road. I'm not very good at this stuff with my limited understanding of the functions and their syntax.
Please stay on the cell with drop-down list, when on ribbon Data->Data validation. It looks like
In Source it could be text as here, or usually reference on the range within workbook. Expand reference with names of new tabs and correct the Source above. It will be like
