+ Reply to Thread
Results 1 to 12 of 12

Formula to Identify Named Range

  1. #1
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Formula to Identify Named Range

    I am creating a request form in Excel that will capture the requirements for one or more products needed to meet the customers need. I have an index formula that will sort the ranking of each product available based on a score in an adjacent column but I need to be able to change the range that the formula pulls from based on the input. Is there a way to name a range within a formula based on a cell value?

    Here is an example of what I'm working with.


    Product Ball Bat Glove Helmet
    Ball 3 2 1
    Bat 3 1 2
    Glove 2 1 3
    Helmet 1 2 3

    3 = high 1 = low

    So in the example above I need to be able to ask the customer questions based on their need for each of the products. So if they ask for a "ball" from the drop-down menu on my form, I want to also ask them about their need for a bat and then a glove and then a helmet. My formula will work as long as the range is static. I would like to make the range change with the drop-down. So if they select "Glove" then my range should change to the Glove column and order questions based on highest need to lowest need.

    For reference, the formula I am using right now is: {=index(ball,match(large(countif(ball,"<"&ball),row(1:1)),countif(ball,"<"&ball),0))}

    Any help you can lend is very much appreciated!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula to Identify Named Range

    Hi,

    Can I suggest you check out Debra's site here http://www.contextures.com/xlDataVal02.html where you'll learn all you ever need to know on this subject - and more.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Re: Formula to Identify Named Range

    Can I suggest you check out Debra's site here http://www.contextures.com/xlDataVal02.html where you'll learn all you ever need to know on this subject - and more.
    Thanks for the recommendation however, this doesn't really meet the need. I need an array of questions to be populated on the form when the product is selected. In my example, if a customer selects "Ball" I want to ask them questions about the type of ball and then (because I have defined the relationship in my table) I would want to ask questions about a bat and then a glove and then a helmet in that order. Each product would have multiple questions. A drop-down list wouldn't allow all of those questions to be populated on the form.

    If ball then:
    Ball Question 1
    Ball Question 2
    and then:
    Bat Question 1
    Bat Question 2
    and then:
    Glove Question 1
    Glove Question 2
    and then:
    Helmet Question 1
    Helmet Question 2

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to Identify Named Range

    You get better help on your question if you add a small excel file, without confidential information.

    Please also add manualy the expected result in your file.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula to Identify Named Range

    Hi,

    If you are saying that in response to a drop down cell in which the user selects 'ball' you want to see another cell which contains a drop down which allows Ball Q1, Ball Q2, and then in a third drop down which allows Bat Q1, Bat Q2 etc.. as a response, I don't see anything in your request that can't be achieved with the stuff Debra mentions. At the end of the day it's simply a matter of identifying the relationship between each drop down and creating the relevant lists.

  6. #6
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Re: Formula to Identify Named Range

    Sorry for the confusion. There are a lot of moving parts on this. Here is the sample file.

    Example Form.xlsx

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to Identify Named Range

    The question is not clear (for me).

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula to Identify Named Range

    I created individual lists on sheet2 which list the questions in the order of your matrix under each product. I then used INDEX and OFFSET to pull those lists into A19 and down depending on what's in B8

    In A19 copied down

    =IF(ISBLANK($B$8),"",IFERROR( INDEX(OFFSET(Sheet2!$A$1,1, MATCH($B$8,Sheet2!$A$1:$G$1,0)-1, COUNTA(OFFSET(Sheet2!$A$1, 1, MATCH($B$8, Sheet2!$A$1:$G$1,0)-1,50,1)),1),ROWS($A$19:$A19)),""))
    Is this what you are looking for?
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Re: Formula to Identify Named Range

    Referring to the highlighted section on my example file...

    For the formulas in cells B12:C15, is it possible to change the named range in the formulas based on the value in the drop-down box?

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula to Identify Named Range

    C12:C15 is always going to be 4,3,2,1
    B12 copied down

    =INDEX(DropDown, MATCH(C12,INDEX($B$3:$E$6, MATCH($B$8, $B$2:$E$2,0),),0))

  11. #11
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Re: Formula to Identify Named Range

    ChemistB, that is really close. It definitely creates the list that I want. The one issue is if I have two products with the same relationship score...on other words, if C12:C15 are variable instead of static. That's a wrinkle that I didn't include in the data (sorry for that). If two items both have a score of a "2" for instance, how do I ensure that both products are still listed in the results?

  12. #12
    Registered User
    Join Date
    11-05-2015
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2013 (365)
    Posts
    11

    Re: Formula to Identify Named Range

    Solved it! I had to create a named formula that would look at the rankings for each product and prioritize the list based on that ranking. I wrote an if formula that compared the dropdown box against the column headers and selected the appropriate named range. If(B8=B2,Ball,if(B8=C2,Bat,if(B8=D2,Bat,if(B8=E2,Glove)))). I named this formula "Arrays" in name manager so I could refer to it more easily in my other formulas. So in my formulas in A12:B15 of my sample file I replaced the named range "Bat" with "Arrays" and it worked perfectly.

    Thank you all for your help on this. I realize the problem was probably not explained clearly enough to put you in a better position to solve it but I really appreciate the time that you took to review it with me.

+ 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. [SOLVED] Extracting cell data to identify a named range
    By JimDandy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-08-2015, 09:36 AM
  2. Formula for identify missing numbers from a given range.
    By Noorking in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-12-2015, 05:14 AM
  3. [SOLVED] Formula needed to identify number within a range of numbers
    By easglos in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-26-2012, 09:26 AM
  4. Identify which named range(s) ActiveCell intersects
    By yay_excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 12:31 PM
  5. Identify duplicate roes in a named range
    By SSGMiami in forum Excel General
    Replies: 3
    Last Post: 03-24-2009, 08:47 AM
  6. Identify intersection in named range
    By Freddy K in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2009, 10:29 AM
  7. [SOLVED] How do you Identify text as a named range in excel
    By DMDave in forum Excel General
    Replies: 7
    Last Post: 05-07-2006, 06:55 PM

Tags for this Thread

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