# Formula to select a named range depending on value in one cell

1. ## Formula to select a named range depending on value in one cell

I'm trying to find out if there is a formula out there that will allow a particular named range to show depending on a particular cell value.

For example, in cell E2 (in sheet 1 named "Work Order"), if you select 2928 from the list available, then in cell I2 you can select values from a range called Task_2928 (which has been created in sheet 2 named "Ranges"), or if you select 3417 from the list in E2, you can select from a range called Task_3417 etc. There are 15 different numbers you can select in cell E2.

I'm not sure if this can be done with a formula, or if some kind of macro would need to be created??

If anyone can help, that would be marvellous!

Thanks
Emma

2. ## Re: Formula to select a named range depending on value in one cell

Hi Emma

whilst the description of what you are trying to do means something to you, knowing what your workbook, worksheet, formula and data look like, it is lost on me and, I suspect, others.

From what you have said, I'm guessing that INDIRECT might be the function that you are looking for.

However, you would probably do a lot better if you uploaded a sample workbook with some typical data.

Regards, TMS

3. ## Re: Formula to select a named range depending on value in one cell

Hi Emma,

Following from what TMShucks said, =INDIRECT() is the way to go here.

I'm assuming you are ok with using Data Validation to make drop down lists for cells. If not, let me know and I'll run you through it.

In cell E2 you enter the number corresponding to your range. In cell I2, use data validation to make it a drop-down list, and in the Source: box enter this formula:

As long as you have your named ranges defined as task_XXXX, this should work

4. ## Re: Formula to select a named range depending on value in one cell

Hi TMS & cffndncr - thanks both for your advice. I'm attaching a copy of my spreadsheet so you can see what I'm trying to do and to check if that's correct

Thanks
Emma

5. ## Re: Formula to select a named range depending on value in one cell

Hi Emma,

I've attached with the formula included. You may need to adjust your named ranges to only include the values you want in the drop down lists though.

6. ## Re: Formula to select a named range depending on value in one cell

That's fantastic! Thank you so much for your help

7. ## Re: Formula to select a named range depending on value in one cell

No worries

If you have found what you are looking for, please mark the thread as [solved]

8. ## Re: Formula to select a named range depending on value in one cell

You could use something like:

Formula:
`Please Login or Register  to view this content.`

Note that 2954 doesn't have a Named Range. All the ranges are static which means you will need to manually adjust them if you add or delete any entries.

Regards, TMS

9. ## Re: Formula to select a named range depending on value in one cell

Thanks for the rep

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1