Table won't update due to blank formulas

[Excelsup]

Hi there,

I have attached a spreadsheet. In this spreadsheet, you can see a table in cells A1 to B2. If I were to add a value to C3, that table would expand automatically to that row. To the right of this table you can see another table, from G1 to H2. Underneath it, in the highlighted section, I have a formula that links to a blank cell. If a value were to appear in that blank cell (e.g. the linked cell in green were to be changed from ="" to a value), the table wouldn't automatically update. Is there a way to correct this, so that my table can be dynamic? The only reason I ask is because I want to make a chart based on this data, but the data will change in the amount of rows there are from 1 to 10. I could make a chart and just highlight 10 rows, but then, because of the blank formulas, blank space is added to my chart. I've heard that if I change "" to NA() the chart won't add these blank values, but I have found this not work personally. Anyone can help please?

[Glenn Kennedy]

Please attach a sample file COMPLETE with the chart you're trying to add.

[AliGW]

you can see a table in cells A1 to B2. If I were to add a value to C3, that table would expand automatically to that row.

No, it won't. You can add a new column by typing into C1.

Is there a way to correct this, so that my table can be dynamic?

The row below the table is not part of the table. Select cell H3 and then hit TAB to add a new row, then type your formulae into this row.

[Excelsup]

No, it won't. You can add a new column by typing into C1.

The row below the table is not part of the table. Select cell H3 and then hit TAB to add a new row, then type your formulae into this row.

Hi, thanks for the response, typo on my end I meant C1. I want the table to automatically add that row when it fills with data as the formula is updated so that my chart also updates around that table. If this isn't possible, I would like a way to make my chart only use data that is actually there, and ignore blanks.

[Excelsup]

Hi, thanks for the response. I reattached with my chart. On the left there is a chart that automatically updates with the table, and on the right is my chart (cluster column) with the cells I want it to use, but currently it isn't ignoring the blanks, making it look spacious.

[AliGW]

I have explained what you need to do. You clearly don't understand how tables (NOT charts!) work.

Do as Glenn says and provide a more detailed sample workbook.

[Excelsup]

I have explained what you need to do. You clearly don't understand how tables (NOT charts!) work.

Do as Glenn says and provide a more detailed sample workbook.

Hi, what do you mean by more detailed sample workbook? The workbook I attached is effectively the same as the one I want to use, just with different words. On my recent attachment, I made a cluster column chart but as you can see it is too spacious as it isn't ignoring blank cells. I tried making those cells NA() but that didn't work. I tried changing gap to zero on the chart data but that didn't work either.

[Excelsup]

I have explained what you need to do. You clearly don't understand how tables (NOT charts!) work.

Do as Glenn says and provide a more detailed sample workbook.

Also, I took your suggestion and made the table longer THEN added the formulas then made the chart, but it still isn't ignoring blanks.

[AliGW]

There was no chart in your first workbook AT ALL!!! Now at least there is. I have run out of time, though - sorry.

[Excelsup]

There was no chart in your first workbook AT ALL!!! Now at least there is. I have run out of time, though - sorry.

i understand if this was beyond your capabilities but appreciate the attempt. anyone else?

[Glenn Kennedy]

After your last comment???

No.

Good luck but goodbye.

[AliGW]

I have closed the thread since the OP does not wish to receive any further help.