+ Reply to Thread
Results 1 to 8 of 8

Table not considering formula as a new row

  1. #1
    Registered User
    Join Date
    09-28-2020
    Location
    England
    MS-Off Ver
    MS Office2017
    Posts
    4

    Post Table not considering formula as a new row

    Hi, I have a table that when I enter in the next line a value it expands it's range (as it should) but when I put a formula the range of the table doesn't expand.

    Can someone tell me how to make it expand with a formula?

    Thanks in advance!
    Attached Files Attached Files
    Last edited by dudumota14; 09-28-2020 at 02:40 PM.

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Table not considering formula as a new row

    https://www.youtube.com/watch?v=Hdvl...ContexturesInc.

    Excel Table Does Not Expand Automatically

  3. #3
    Registered User
    Join Date
    09-28-2020
    Location
    England
    MS-Off Ver
    MS Office2017
    Posts
    4

    Re: Table not considering formula as a new row

    That didn't solve the problem. I've seen the video and my settings are correct.
    The table is expanding but only with direct values/text and not with formulas.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Table not considering formula as a new row

    Excel defaults to assuming you want a subtotal if you enter a formula in the row below a table.

    What are you trying to do? There are only two sensible options when entering formulas in Structured Tables:

    1) Enter a formula which applies to every row within a column
    2) Enter a subtotal formula which sits in the last row of the table

    Alternative set-ups which involve having a mix of different formulas (or a mix of formulas and constants) within the same column are not to be recommended.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    09-28-2020
    Location
    England
    MS-Off Ver
    MS Office2017
    Posts
    4

    Re: Table not considering formula as a new row

    Quote Originally Posted by XOR LX View Post
    Excel defaults to assuming you want a subtotal if you enter a formula in the row below a table.

    What are you trying to do? There are only two sensible options when entering formulas in Structured Tables:

    1) Enter a formula which applies to every row within a column
    2) Enter a subtotal formula which sits in the last row of the table

    Alternative set-ups which involve having a mix of different formulas (or a mix of formulas and constants) within the same column are not to be recommended.

    Regards
    Thank you for your help.
    I'm trying to do the 1st option. All the rows in that column have the same formula. No mixed formulas or constants, but the table still doesn't expand...

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Table not considering formula as a new row

    Quote Originally Posted by dudumota14 View Post
    All the rows in that column have the same formula. No mixed formulas or constants
    Actually that's not true. The entries in cells Q6:Q8 are not formulas at all; they are all constants.

    If you want them all to have the same formula, then overwrite one of the existing cells in the table with that formula, instead of attempting to add it as a new row. Then click on the icon that appears and select "Overwrite all cells in the column with this formula".

    Regards

  7. #7
    Registered User
    Join Date
    09-28-2020
    Location
    England
    MS-Off Ver
    MS Office2017
    Posts
    4

    Re: Table not considering formula as a new row

    Quote Originally Posted by XOR LX View Post
    Actually that's not true. The entries in cells Q6:Q8 are not formulas at all; they are all constants.

    If you want them all to have the same formula, then overwrite one of the existing cells in the table with that formula, instead of attempting to add it as a new row. Then click on the icon that appears and select "Overwrite all cells in the column with this formula".

    Regards
    You are right about that but the problem is still the same. I did that example in a rush. Atatched you have another version.

    Actually if you drag the corner on the bottom right side of the table you will see that the "Tomato" is actually there. The problem is that I have to do it manually.


    My end goal is whenever I enter new data in one table, it would also enter/update/expand automatically the data in another table. It shouldn't be that hard...

    Thanks in advance!
    Attached Files Attached Files

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Table not considering formula as a new row

    Quote Originally Posted by dudumota14 View Post
    My end goal is whenever I enter new data in one table, it would also enter/update/expand automatically the data in another table
    Ah, this is a completely different matter. In fact, this is possible in Office 365 using the new dynamic array functionality, though such functionality is not supported within Excel Tables.

    For earlier versions, what you are asking is not possible without VBA. The only feasible workaround would be to set the second table to initially have a large enough number of rows so as to capture all potential future expansions of the first table, returning blanks in rows where necessary, e.g. with a formula such as:

    =IF(ROWS(INDEX([Product],1):[@Product])>COUNTA(Tabela1[Product]),"",INDEX(Tabela1[Product],ROWS(INDEX([Product],1):[@Product])))

    though this will of course contain potentially many blank rows at the end of the table.

    If you want the table to auto-resize then you will need some VBA.

    Regards

+ 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] Insert formula into table so that it carries down entire table column length
    By carlito2002wgn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2019, 10:26 AM
  2. [SOLVED] Extend currnt table whilst keeping the Formula (Create a live league table)
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-14-2018, 02:11 PM
  3. Replies: 1
    Last Post: 09-08-2017, 07:30 AM
  4. [SOLVED] Adding a row in a table corrupts formula in a summary table (2nd table)
    By How How in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2015, 02:35 PM
  5. [SOLVED] Formula for copying over relevant data from one table to populate another table
    By amasson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-19-2013, 06:11 AM
  6. Replies: 7
    Last Post: 01-25-2013, 05:17 PM
  7. [SOLVED] Need a formula to sort a table into table (for a Table Plan) and name in column 2
    By jbpianoman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-07-2012, 09:21 AM

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