+ Reply to Thread
Results 1 to 11 of 11

Dynamic name asigement to table

  1. #1
    Registered User
    Join Date
    11-22-2018
    Location
    Beek & Donk
    MS-Off Ver
    Windows 10
    Posts
    4

    Dynamic name asigement to table

    Hello,

    I've encountered a problem with a project i'm working on. So far I couldn't find anyone with a similar problem so here it is.

    In one of my worksheets I have a table containing 1's and 0's, which are generated using a v-lookup.
    Horizontal I have the names of companies and vertical I have students names.

    Because another program is reading this data, I have to assign a name to the table so the other program can read it (only the 1's and 0's, not the names).
    However, my model should be able to ad/delete names and companies, i.e.; every time I change the dimensions of the table, I have to change the range of the name definition as well manually.

    Because I have up to 12 of these (huge) tables, I was wondering if this could be done using a formula.

    So in short; if I add/delete rows/columns to a table, can the name of the table change dynamically with its dimensions?
    Attached Images Attached Images
    Last edited by billybouw; 11-22-2018 at 12:55 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic name asigement to table

    I can't see it clearly... let alone read it...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    11-22-2018
    Location
    Beek & Donk
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Dynamic name asigement to table

    Dear Glenn,

    Thank you for the quick response.
    I've made a sample for you to look at.
    Although I've followed your instructions, I don't know if it's attached or not...
    Looking forward to your reply

    Oh and i almost forget, these tables should be around 70x15.
    In both dimensions it could be 10/20 more or less.

    So if you look at the example.xlsx:
    If I ad something to column A or column F, i want the definition of table 'NAME' to change with that dimension
    Attached Files Attached Files
    Last edited by billybouw; 11-22-2018 at 12:58 PM.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic name asigement to table

    maybe something like this:

    =OFFSET(Sheet1!$B$2,,,MATCH(TRUE,INDEX((Sheet1!$A$1:$A$23=""),),0)-2,MATCH(TRUE,INDEX((Sheet1!$A$1:$G$1=""),),0)-2)

    as the Named range (table1). This is dynamic and can be called in a formula (example attached)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-22-2018
    Location
    New York
    MS-Off Ver
    2007
    Posts
    1

    Re: Dynamic name asigement to table

    For that you will go to the tools option and after that you will do it easily. For further about it you just get some review and from there you will be very helpful. For more just visit : it

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic name asigement to table

    Forget the formula above. This is a non-volatile alternative:

    =Sheet1!$B$2:INDEX(Sheet1!A:G,MATCH(TRUE,INDEX((Sheet1!$A$1:$A$23=""),),0)-1,MATCH(TRUE,INDEX((Sheet1!$A$1:$G$1=""),),0)-1)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-22-2018
    Location
    Beek & Donk
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Dynamic name asigement to table

    Thank you for the time and effort, however i'm afraid you misunderstood my problem.

    I've uploaded another example which i hope explains better what i mean.
    The following references can be found in EXAMPLE2.xlsx.

    In my example you can see,
    - when you add an value to the first list (LIST1), the the table (NAME) will expand vertical.
    - When you add an value to the second list (LIST2), the table (NAME) will expand horizontal.
    - The area enclosed by the table (NAME) is defined as "NAME" in the formula section under name definition.

    But if i expand the table, by adding values to LIST1 or LIST2, the definition of "NAME" won't change with it, this is where i'm looking for.

    So far i can get an text representation of the desired address of the cell at the lower right bottom to change with the table,
    however i don't know how to let excel "read" this address and use it as a name definition.

    I've uploaded another example which i hope explains better what i mean.
    Attached Files Attached Files

  8. #8
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Dynamic name asigement to table

    I'd probably stick with OFFSET:

    =OFFSET(Blad1!$F$2,0,0,COUNTA(LIST1),COUNTA(LIST2))
    Rory

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic name asigement to table

    removed by GK
    Last edited by Glenn Kennedy; 11-23-2018 at 08:26 AM.

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Dynamic name asigement to table

    Change E2 to =LIST1&"" copied down

    Change F1:T1 to =TRANSPOSE(LIST2)&""

    Before addition of a new column and a new row:

    Name evaluates to $F$2:$N$9

    Afterwards Name evaluates to $F$2:$O$10

    To evaluate


    formulas/formula auditing/evaluate formula.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    11-22-2018
    Location
    Beek & Donk
    MS-Off Ver
    Windows 10
    Posts
    4

    Re: Dynamic name asigement to table

    Thank you so much!! after some tweaking of your example i got what i wanted

+ 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. Replies: 1
    Last Post: 11-12-2018, 02:46 PM
  2. Dynamic Table of Store/Bank with location assignment (Dynamic Inventory)
    By x_ampl1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2017, 01:58 PM
  3. Wants to Create Dynamic Chart basis on Dynamic Table
    By Nisha Dhawan in forum Excel General
    Replies: 6
    Last Post: 04-30-2015, 12:08 AM
  4. Replies: 10
    Last Post: 10-09-2014, 06:20 PM
  5. Replies: 9
    Last Post: 02-26-2014, 04:17 PM
  6. [SOLVED] Dynamic Table Range, Dynamic Columns
    By bimo in forum Excel General
    Replies: 6
    Last Post: 06-24-2013, 08:16 AM
  7. Dynamic table formula for sorting data to another table dynamic
    By 650dozer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 07:22 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