+ Reply to Thread
Results 1 to 10 of 10

Named range and reference to one cell within dynamic table

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

    Named range and reference to one cell within dynamic table

    Hi Guys,

    Using named range is powerful tool.
    I have dynamic table and want to refer exactly to one cell within this using structured references.

    So in named ranges something like this:
    =Indirect("Table1[Row4][Column2]").

    It is possible?

    Best,
    Jacek

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Named range and reference to one cell within dynamic table

    Jacek

    Is it a named range you have or a table?
    If posting code please use code tags, see here.

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

    Re: Named range and reference to one cell within dynamic table

    Hi Norie,

    I have named range and i have to refer to dynamic table.
    Why?

    Because of automations which i am implementing now.
    I will be loading about 15 tables from database as dynamic tables and in other tabs i have to refer to theirs cells. And ranges.

    Best,
    JAcek

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Named range and reference to one cell within dynamic table

    Jacek

    I'm a little confused, you have tables and you also have named ranges that refer to the tables?

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

    Re: Named range and reference to one cell within dynamic table

    yes exactly.

    These named ranged are used by java so i can not change them. And they are in other tabs.

    Maybe i will change it to use irectly tables but it is possible to refer to exact one cell from table range as i asked before?

    Best wishes,
    Jacek

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Named range and reference to one cell within dynamic table

    Jacek

    Still not sure why you have named ranges and tables, each of the tables will already have its own named range.

    Anyway, as far as I know you can't refer directly to a specific cell in a named range using row/column numbers in a formula.

    If you were working in code you could use Cells to refer to a specific cell in a named range by row/column number.

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

    Re: Named range and reference to one cell within dynamic table

    hmm.

    Ok thank you Norie.
    Do you have code for refer to specific cell witihn table range?

    Best,
    Jacek

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: Named range and reference to one cell within dynamic table

    Are you wanting the named range to
    - refer to the same original cell value (even if table is sorted)
    OR
    - refer to a paricular postion in the table

    Here is a possible workaround for you
    - choose the one that you need
    - if Name1 is your choice then you MUST insert a column of unique values as column1 of your table

    (in G2) Name1 with RefersTo:
    =VLOOKUP(4,Table1,2,FALSE)
    (In F2) Name2 with RefersTo:
    =INDEX(Table1,4,2)

    In attached workbook F2 and G2 originally contain the same value
    (B5 = Pear)

    NamedRangesIndexOrLookup.jpg

    If table is sorted by last column F2 returns value in original location, but G2 returns the original value
    (B5 = Orange)

    NamedRangesIndexOrLookupAfterSort.jpg
    Attached Files Attached Files
    Last edited by kev_; 03-22-2018 at 11:36 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  9. #9
    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: Named range and reference to one cell within dynamic table

    Perhaps
    =INDEX(Table1[Column2],4)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

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

    Re: Named range and reference to one cell within dynamic table

    Wow love your solution kev_!!!

    thank you very much !
    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] how to link worksheet1 column to a dynamic named range list in a table in worksheet2
    By rny1ef in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-05-2014, 08:09 AM
  2. Dynamic Named Range based on Pivot Table for MULTIPLE COLUMNS
    By Pho6 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-29-2014, 02:04 PM
  3. [SOLVED] Dynamic Named Range based on Pivot Table
    By Pho6 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 08-27-2014, 01:53 PM
  4. Combobox addition to Rowsource in dynamic named range corrupting table
    By BFD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2014, 11:03 PM
  5. Dynamic Named Range + Index/Match Returning Circular Reference
    By jennarenae in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2014, 06:23 PM
  6. using a dynamic reference cell to copy a range of cells within a data table
    By mcash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-11-2010, 10:22 AM
  7. How to dynamically reference a dynamic named range
    By paris3 in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 09-06-2005, 12:05 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