Dynamic Offset Formula to populate a Data Validation list

[TC1980]

Hi All,

I'm trying to use an OFFSET formula to populate a data validation list so that when a team manager name is selected in another cell (A1, also a named range of ipMgr) it lists the team members relevant to that manager.

I have got the following 'static' formula to work correctly:

Please Login or Register  to view this content.

I'm trying to amend the formula to replace the $B$8 with another dynamic formula that provides the relevant cell reference based on the manager selected in cell A1/ipMgr. The formula I have for this part is:

Please Login or Register  to view this content.

"TeamMgrs" is a range in the Team Structure sheet that lists the managers, with their team members directly underneath.

However, I'm having some trouble merging the two as the second formula just returns the cell reference and I need to combine this with the sheet name 'Team Structure'! I'm sure I'm doing something stupid here, but I've tried CONCATENATE and INDIRECT, but can't find anyway to get it to work as it does in the first formula.
Indirect example below, will only return the first team member from the list, not the full list, which I guess it due to INDIRECT returning the value rather than the address.

Please Login or Register  to view this content.

I can't get CONCATENATE to work at all!

Any pointers on where I'm going wrong with this?

Thanks, TC

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TC1980]

I can't upload anything as I'm at work and there are restrictions in place.

[JohnTopley]

If "Team Mangers" head a list of team members, you could make each list a Named Range (using Manager) and then use =INDIRECT(named_Range)

Manager ="Tom Jones", Named range "Tom_Jones". (note the underscore "-")

If "Tom Jones" is name in first list ( in A2) , then for second list use DV ..List, Source =INDIRECT(Substitute(A2," ","_"))

This replaces the blank with underscore

[TC1980]

I have used this type of solution before, but never thought of combining it with Substitute to not affect the display name - nice and neat.

I cannot use this solution in this example though as the Team Managers list is unlocked to allow the users to amend when needed and 'self manage'. Therefore the manager names will change over time and they wouldn't know to create amend the named range or are likely to mess it up.

I've not done a lot with data validation formulas in Excel before, typically use more VBA - and at this moment I'm thinking I may need to resort to VBA to populate the data validation list on a worksheet change event! Thought this would be a simpler solution but apparently not for me!

[JohnTopley]

Try and post a small sample file (from home?). These DV lists can usually be done with formulae.

[Richard Buttrey]

Have you called in at Debra's Contextures site here.. where you'll learn everything you need to know on this topic - and more.

[TC1980]

I finally worked it out - I needed to use INDIRECT and CONCATENATE!

Please Login or Register  to view this content.

This provides a drop down list specific to the manager selected from a range on another sheet - ignoring blank cells. Got there in the end!

Thanks for the input though John and Richard. Much appreciated.

[JohnTopley]

Well done!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.