+ Reply to Thread
Results 1 to 8 of 8

Using another cell value to create and display a named range in another set of cells.

  1. 06-17-2021, 09:52 AM #1
    the frost is offline
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Using another cell value to create and display a named range in another set of cells.

    Hi,

    I believe I am so close to finishing this one!

    Is there a way that I can use the value of another cell to define and show a named range in the formula cell?

    I.e if I type =" named range " into my formula cell then, viola!, my list appears, the problem I have is that I want it to draw the named range from another cell which could be a validation list of the available named ranges within my sheet.

    Hope this makes sense!

    Please Help!
    Reply With QuoteRegister To Reply
  2. 06-17-2021, 10:12 AM #2
    TMS is online now
    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
    48,565

    Re: Using another cell value to create and display a named range in another set of cells.

    Let's say you have a list of Named Ranges, nr_A1, nr_B1, nr_C1 in cells I1:I3. Set your DV list to =$I$1:$I$3 in cell K1. In cell L1: =INDIRECT(K1) will produce a list of the values in the selected Named Range
    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

    Reply With QuoteRegister To Reply
  3. 06-17-2021, 11:04 AM #3
    the frost is offline
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Using another cell value to create and display a named range in another set of cells.

    Hi TMS,

    I have tried to apply this in the attached file, and it did not work.

    In the workbook I am working on, the source cell is auto-populated by another process with one of the named ranges I have pre-defined. What I would like is for the destination cell to recognize this as a named range and return the list associated with it.

    I have put an example workbook together to hopefully show what I mean.

    Thanks for the swift reply.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-17-2021, 11:35 AM #4
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,965

    Re: Using another cell value to create and display a named range in another set of cells.

    See attached.

    See DV for D2 list =indirect(B2)

    NOTE: Named ranges with INDIRECT cannot be dynamic so they are now fixed in the attached.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-17-2021, 11:43 AM #5
    the frost is offline
    Registered User
    Join Date
    03-25-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365
    Posts
    15

    Re: Using another cell value to create and display a named range in another set of cells.

    Hi John,

    In principle that's even more than I am trying to achieve, is there any way with a formula other than "indirect" that allows the whole list to be shown rather than just a new DV list? (even if its multiple steps) What I'm trying to achieve is a list that I can then apply rates to so when I make a selection the list is automatically generated and I just have to apply the rates.

    appreciate the response.
    Reply With QuoteRegister To Reply
  6. 06-17-2021, 11:52 AM #6
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,965

    Re: Using another cell value to create and display a named range in another set of cells.

    So if "Selection_2" is selected in B2, you want list in D5 to D13 of Data shown in D2 down ? (not DV)
    Reply With QuoteRegister To Reply
  7. 06-17-2021, 12:04 PM #7
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,965

    Re: Using another cell value to create and display a named range in another set of cells.

    Assuming list is required

    in D2

    =IF(COUNTA(INDIRECT($B$2))>= ROWS($1:1),INDEX(INDIRECT($B$2),ROWS($1:1)),"")

    still uses INDIRECT

    Without INDIRECT

    =IF(INDEX(Data!$C$1:$F$1,MATCH('Display Tab'!$B$2,Data!$C$4:$F$4,0))>= ROWS($1:1),INDEX(Data!$C$5:$F$12,ROWS($1:1),MATCH('Display Tab'!$B$2,Data!$C$4:$F$4,0)),"")

    Matches on Named Range Titles and uses the Named Range counts in row 1 of Data
    Attached Files Attached Files
    Last edited by JohnTopley; 06-17-2021 at 12:26 PM.
    Reply With QuoteRegister To Reply
  8. 06-17-2021, 04:57 PM #8
    TMS is online now
    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
    48,565

    Re: Using another cell value to create and display a named range in another set of cells.

    Another option is to create individual structured tables for each range. You could have a simple macro to set them up after data has been imported.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Two questions : named range display & alternate cell
    By shahab47 in forum Excel General
    Replies: 12
    Last Post: 02-02-2021, 09:30 PM
  2. Create Worksheets named after cells in a range
    By Excel-VBA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-23-2019, 09:07 AM
  3. [SOLVED] Display a named range of cells each time a selection from a drop-down list changes
    By Aland2929 in forum Excel General
    Replies: 2
    Last Post: 02-11-2017, 07:52 AM
  4. [SOLVED] Compare cells to named range and create new sheets using related named range
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2015, 03:35 PM
  5. [SOLVED] create MsgBox for cells within named range
    By bob07904 in forum Excel General
    Replies: 6
    Last Post: 02-22-2015, 10:33 AM
  6. Create a named range of cells BUT variable in reference to a cell value
    By peponfree in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 02-13-2014, 12:46 PM
  7. inserting a named range into new cells based on a named cell
    By Peter S. in forum Excel General
    Replies: 1
    Last Post: 06-03-2006, 10: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1