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!
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!
Last edited by dudumota14; 09-28-2020 at 02:40 PM.
https://www.youtube.com/watch?v=Hdvl...ContexturesInc.
Excel Table Does Not Expand Automatically
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.
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
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!
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks