+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    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. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    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:

    =INDIRECT("Task_"&$E$2)

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

  4. #4
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    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
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    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.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

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

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

  7. #7
    Forum Contributor
    Join Date
    02-20-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    170

    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. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

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

    You could use something like:

    Formula: copy to clipboard
    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. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

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

    Thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. select from a data range depending on a certain cell input.
    By Andism in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-11-2012, 11:38 AM
  2. Select cell from a named range
    By Spagbog in forum Excel General
    Replies: 2
    Last Post: 09-09-2011, 09:04 AM
  3. Select Named Range in current cell row
    By Jmeyering in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2011, 05:25 PM
  4. Is there a way to select a named cell range by concatenating text?
    By apiekar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2009, 02:47 PM
  5. Replies: 1
    Last Post: 07-29-2005, 02:06 PM

Bookmarks

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