+ Reply to Thread
Results 1 to 9 of 9

Structure table and headers like metadata

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

    Structure table and headers like metadata

    Hi Guys,

    i want to have named range Table1_Headers = [Var_Column1, Var_Column2...] - all headers from Table1.

    Var_Column is variable for specific column within table.

    I want to have reference to this specific column but when user will change the name of header- still i should have reference to data in Var_Column1.

    Example:

    Var_Column1 = Data for [Name] column (table column name)
    User changed [Name] to [Surname].
    Var_Column1 = Data for [Surname] column

    And second issue - what if user will change order of column.

    If in the Excel there is some kind of tag which i can add to column or cell?

    Best,
    Jacek

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Structure table and headers like metadata

    So, if a user changes the name of the column, AND the order of the columns... How do you intend to identify your required column?
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

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

    Re: Structure table and headers like metadata

    Olly,

    i think that i have the answer.

    I can add comment for each header column.
    And now i can change name of column, change order of columns and still comment will be the same!

    And other developers (Java in my company) can have variable set up by comment. And read exact values attached to this!

    Best,
    Jacek

  4. #4
    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: Structure table and headers like metadata

    The header row for a table is already effectively a named range (Table1[#Headers]). You can use that with the INDEX function to refer to a specific cell in that range by number.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

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

    Re: Structure table and headers like metadata

    Hi xlnitwit.

    thank you,

    But if you change the column name you will get error.
    And i need all data wtihin column, not just one cell.

    Best,
    Jacek

  6. #6
    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: Structure table and headers like metadata

    Why would you get an error? INDEX takes a column number. You can also use INDEX with the entire table to get a whole column.

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

    Re: Structure table and headers like metadata

    thank you xlitwit.

    Ok how can i refer to whole column table using index?
    Can you give the example.

    Best,
    Jacek

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

    Re: Structure table and headers like metadata

    Okey,

    i found the solution:

    Please Login or Register  to view this content.
    But with this approach i have a lot of named ranges for each table: for each column i have one named range.
    And this is too much.

    When i have a whole table i can read data by java easily and when i have tagged every header (for example with comment) i can know which cell (based on tag/comment)
    should i assign to specific variable.

    Imagine that with Column1 i have comment: Var1_Column1.
    Even if i change name from Column1 still i will have Var1_Column1 there which i get from comment.

    Best,
    Jacek

  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: Structure table and headers like metadata

    If you want to include the header
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If not
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change the column number (in red) as necessary.

+ 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. Changing the structure of table
    By bikashbehera95 in forum Excel General
    Replies: 1
    Last Post: 11-06-2017, 06:33 AM
  2. Create a Table Structure from raw data
    By CIRHS in forum Excel General
    Replies: 2
    Last Post: 10-18-2016, 01:58 PM
  3. autofilter method of range class failed -header has tables headers and non table headers
    By naveen.acheanz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2016, 08:21 AM
  4. inverting the structure of a table
    By seanclee88 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-15-2016, 08:06 PM
  5. Creating a Pyramid Hierarchy structure from a flat structure
    By thegamerulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2014, 01:28 AM
  6. Table structure
    By vietdieu in forum Excel General
    Replies: 1
    Last Post: 02-03-2012, 02:19 AM
  7. [SOLVED] help converting table structure
    By Chris Barnett in forum Excel General
    Replies: 2
    Last Post: 09-29-2005, 04: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