Index Function with Named/Defined Ranges

[cosco]

Pulling my hair out over this one...

I have a master sheet (Tech Counts) that lists hospitals in column B and the workgroup each hospital belongs to in column A. There are several other columns that irrelevant to my problem.

I created dynamic name ranges for each workgroup so as I add new hospitals to the workgroup I don't have to manually update the range. The "Index" sheet, pairs the workgroup number in "Tech Counts" with the appropriate defined name.

In the "Report" sheet I set up validation in cell F5 to allow me to select a workgroup from the Index sheet. In cell A10, I have created a formula intended to use INDEX and INDIRECT to pull the first hospital in the workgroup I select. As you can see, I get a REF error. However if I simply type =INDEX(Detroit,1) I get Childrens Hospital of Michigan (the correct answer).

Can some take a look and help me get the correct answer by using the dropdown?

[DonkeyOte]

I'm afraid INDIRECT won't work with Dynamic Named Ranges.

You can circumvent by using an old XLM Call via a Name but this is in effect using a bit of VBA, eg:

Please Login or Register  to view this content.

You can then use

Please Login or Register  to view this content.

Hopefully that gives you an idea.

The other alternative - if you have only a few names with which to work - would be to use a CHOOSE based construct thereby avoiding the Volatility (and need for XLM Call)

[cosco]

I am really trying to avoid VBA as I am not too comfortable with it at this point. So index with only work with a named range not a dynamic range?

[DonkeyOte]

INDIRECT will only work with static ranges.

In the prior post there's no code to worry about but the file would warrant being saved as .xlsm type given use of Evaluate in the _Workgroup named range.

As mentioned, if you have only a handful of named ranges you can use a CHOOSE based construct rather than the XLM Evaluate call

Please Login or Register  to view this content.

The formula for A10 onwards remain as outlined in the prior post.

The CHOOSE approach has added benefit of being non-volatile but has limited practicality pending number of named ranges in play.

[cosco]

I will give the XLM Call a try. Could you instruct me a bit further on where the code you posted needs to be copied to?

[DonkeyOte]

Simply create a new Defined Name as outlined in post # 2 (ie via Name Manager as per normal).

[sailepaty]

Review your file I think this can be resolve without INDIRECT. I added an auxiliary column and row in sheet Report, then the rest is just INDEX and MATCH.

Review the attachment.

Regards

[cosco]

All three solutions work GREAT!! Much obliged...