+ Reply to Thread
Results 1 to 17 of 17

Refer to name of named range - name manager

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Refer to name of named range - name manager

    Hi,

    i have a table (Example Table)in Access with named range name column and ServiceModel in second column, something like this:

    Named Range:
    Var_NR1

    ServoceModel:
    ServiceModel1

    I am connecting from Excel to Access and using Access' tables as source for dynamic tables in Excel.

    And now it is possible to refer within name manager to Named range from dynamic table?

    I mean something like this in name manager--> name: Var_NR1, RefersTo: VLookup (Var_NR1.name,Indirect("Example Table"),2,0)
    Var_NR1.name is refer to named range name?

    Please help,
    best
    Jacek

  2. #2
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    Anyone?

    Jacek

  3. #3
    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
    28,007

    Re: Refer to name of named range - name manager

    Attach a sample workbook (not image) {as it is not clear to me what exactly you want]

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    Hello John,

    please find example in attachment.

    Example.xlsx

    Screenshot_2.png

    As you can see to find the corresponding value i had to write manually "Var_NR1".

    In this field i want to write something like Var_NR1.name or this - it is possible?

    Best,
    Jacek

  5. #5
    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
    28,007

    Re: Refer to name of named range - name manager

    Still not sure what your are trying to do: You appear to want to reference the named range "Var_NR1" to itself

    Named range : Var_NR1 Refers to: A2

    then

    =VLOOKUP(Var_NR1,Table1,3,0)

    will return 5.

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    Hi John,

    thank you.

    When i will automatically download tables from Access i want to indicate Excel to take only proper values for proper named ranges.

    Imagine that i am deleting table1.
    Now i have error as reference for "Var_NR1".
    "Var_NR1" should refer to dynamic table, not to specific cell.

    When i click the button and refresh all tables from Access (i will download it from Access as source tables) the error should disappear.
    Because i am referencing to dynamic table using Vlookup function.

    Problem is that i do not know how to refer within name manager to named range name as lookup value.

    Best,
    Jacek

  7. #7
    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
    28,007

    Re: Refer to name of named range - name manager

    VLOOKUP only has a single cell parameter as the "lookup" value so referring to a multi-cell range makes little sense.

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    John,

    agree.

    But when i will have a few ranged names i could take them named and use it for vlookup value...

    I think that this is not possible with name manager to use named range name as variable...

    Best,
    Jacek

  9. #9
    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
    28,007

    Re: Refer to name of named range - name manager

    It is possible: I did it but having the VLOOKUP in a named range did not make sense to me.

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    John how did you do this?

    It make sense when you are are inputing data using Database.

    I have a button to pull data out from Access to dynamic tables.
    And now depending on client i can pull different data from Access.

    But Named Range in my Master Workbook should be the same still !

    So Var_NR1 should be the Var_NR1 after changing value assigned to it.
    And variable assigned to Var_NR1 can change using vlookup...

    So if i have number 5 assigned to Var_NR1 for Client1, for Client2 i have for Var_NR1 named range number 156 assigned.
    And this is why i am using vlookup here.

    Best,
    Jacek

  11. #11
    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
    28,007

    Re: Refer to name of named range - name manager

    How do you differentiate Client 1 from Client 2: if Var_NR1 is a range, then simply VAr_Nr1(1) = Client1, Var_NR1(2)= Client 2 where 1 and 2 are indices relating to the client.

    (effectively a row number).

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    Hi John,

    I Have Access FE and Postgresql as BE.
    I am inputing data using FE.

    From Validation list user can choose which source data he wants to use - for Client 1 or Client 2 or both.

    Var_NR1 is a named range but referencing to dynamic table.
    Dynamic table can be deleted and pull out once again from database.

    I am referencing to them using indirect("Table1[Column1]") for example.

    Best,
    Jacek

  13. #13
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    Hi,

    anyone?

    Jacek

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Refer to name of named range - name manager

    You would have to use VBA (or possibly XLM), I think.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  15. #15
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    hi xlnitwit!

    what do you mean by using XLM?

    Best,
    JAcek

  16. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Refer to name of named range - name manager

    XLM is the Excel4 macro language that preceded VBA. It would be simpler to use VBA though, and you'd have to save the file as macro-enabled either way.

    To be honest, though, I don't really see the point of this.

  17. #17
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Refer to name of named range - name manager

    thank you xlnitwit.

    I think that i have to keep simple data in Excel workbook and refer like usual way named range to it.

    Best,
    Jacek

+ 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] Why can't i change scope of named range within name manager?
    By jaryszek in forum Excel General
    Replies: 16
    Last Post: 01-24-2024, 09:45 AM
  2. [SOLVED] Named Range- 'refers to' defined in a cell instead of in the Name Manager
    By MycroftHolmes in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-21-2017, 10:18 AM
  3. [SOLVED] What happens when I refer to a named range?
    By RogeratCCCC in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-21-2012, 04:06 PM
  4. VBA Refer to named range in different workbook
    By globalpontoon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2010, 03:20 AM
  5. Refer to named range in another file
    By Bob@Sun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-04-2010, 01:22 PM
  6. Refer indirectly to named range
    By chartguy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2009, 12:50 PM
  7. [SOLVED] How does one refer to the n-1 element of a named range?
    By Charles Hewitt in forum Excel General
    Replies: 2
    Last Post: 11-26-2005, 03:00 AM

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