Forum Discussion
Help Needed: Nest XMATCH in h-linked drop-down to land on first blank record in the destination Tab
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
SergeiBaklan Great suggestion! I've attached a bare-bones template with no formulas. Everything is working on the dropdowns I have thus far. I want the user to use the Directory to locate the person who made the error. They can then select Office_X from the dropdown to be taken directly to that office's tab. This where I need help. When they land on the Office_X page chosen, I want the 'Staff Name' drop down to only show the employees in that office, as opposed to the whole list. That might also auto populate their Supervisor's name and contact information as well. I hope that is more clear. Thank you!
I'd suggest into Lists add names by Office
using
=IFERROR( TOROW( FILTER('Staff Tables'!$B$5:$B$19, 'Staff Tables'!$D$5:$D$19=$A2) ), "" )Since it's problematic to get sheet name in Excel for web, add Office name in each tab, let say as
Data validation for Staff
=XLOOKUP($A$1, Lists!$A$2:$A$20, Lists!$B$2:$B$20, "" )#Get supervisor with
=LET(s, XLOOKUP($B4, 'Staff Tables'!$B$5:$B$20, 'Staff Tables'!$E$5:$E$20, "" ), IF(s="", "", s) )- Hi Sergei! It's me again. This time, I think I may be out of luck. When I try and protect the cells with formulas in my tables, it won't allow the table to expand and copy the formulas and drop-downs to the next line. Do you have any suggestions for how to at least keep folks from typing over the formulas? Doesn't appear that I will be able to protect the table or worksheet while allowing it to expand. Thanks!
Hope you too, thank you!
SergeiBaklan Perfect! I will certainly check it out. I hope you have a fantastic rest of your weekend!!
Lot of resources, but afraid I can't recommend something specific. I'm not consultant and not a trainer, not follow related resources. It's better to check on LinkedIn what is available. Couple of years ago John MacDougall listed best Excel resources The Top Excel Resources You Need to Know About | How To Excel, not a lot of changes for today. Majority of authors are Excel MVP:s, have free and paid content. Check what is most suitable for you and go deeper.
SergeiBaklan Everything worked like a charm Sergei! I'm going to have to reverse engineer the formulas to find out how they work. Lol! Hey, if you can recommend a good training for folks like me to use to understand how to build formulas like this, I would certainly tap into that! I've learned all I know from watching youtube videos and trying to reverse engineer what I get here on this forum. I can tell you, it's been life saver!! One of these days I would like to be able to contribute... you know, give something back. Thank you again, so very much!
- It worked just fine after all! I pretty much suck at this, but I'm getting better. Thanks to this forum. 🙂
- Never mind. I left a bunch out of the formula. Lol!
- When I try to enter the formula for IFERROR, it forces the table and column name instead of letting me use the range. Then it returns an error that doesn't offer any suggestions. Here is what it looks like...
=IFERROR(Staff_tbl[Staff Name (KEY)],Staff_tbl[Supervisor]=$H2),"") - Ok. Thanks for the information. I will do it the safe way. I'll be sure to let you know how it goes. Thank you again for all your help!
On desktop we may use CELL("filename") and extract sheet name with formula from it. On web that function doesn't work. There is workaround with set of formulae but, from my point of view, not reliable. If you don't rename sheets often, much easier to duplicate sheet name in one of the cells.
- I'm developing the Workbook in the desktop app and then uploading it to the web. Does that make a difference with the 'sheet name' functionality, or is it still best to put the office/sheet-tab names at the top of each worksheet?