• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula with structured reference not working when row inserted into table

Wmickle

New Member
I have a worksheet for team members to complete several fields in row. I want to count # of fields that are incomplete(countblank) when a date, they enter into worksheet, is greater than 02/01/2020. If the entered date is less than 02/01/2020, I don't want a count. The fields they are completing are included in the table range. Here's cell formula I'm using for this count.

=IF([@[Entered date]]>=DATE(2020,2,1),COUNTBLANK(Table4[@[Field1]:[Field10]])," ")

When a new row is inserted within the table the cell formula is blank. What am I missing?
 
I think it depends on:
1. Whether the countblank formula is within Table4 or not (with the formula including Table4 as a reference implies that it's not part of the table, but it's not necessarily the case.
2. How you're inserting rows (inserting whole sheet rows, or highlighting a row within a table and right-clicking and choosing Insert|Insert table rows above.

Ideally, attach a workbook here which exhibits this behaviour so we don't have to guess (wrongly) what your setup looks like, and describe how you're adding rows.

I don't know if the following will make any difference, but in Excel's Options, under Advanced, in the Editing options section, there's a checkbox Extend data range formats and formulas; perhaps changing that setting may change the behaviour?
 
When I add a new row, the formula does replicate. Not that you see anything until a date is inserted that matches the constraint.
 

Attachments

  • table4.xlsx
    10.8 KB · Views: 2
I think it depends on:
1. Whether the countblank formula is within Table4 or not (with the formula including Table4 as a reference implies that it's not part of the table, but it's not necessarily the case. Yes, countblank formula is within the Table4 but only counting selected blank fields in row table not the entire row.
2. How you're inserting rows (inserting whole sheet rows, or highlighting a row within a table and right-clicking and choosing Insert|Insert table rows above. Yes using this approach to insert rows into table

Ideally, attach a workbook here which exhibits this behaviour so we don't have to guess (wrongly) what your setup looks like, and describe how you're adding rows.

I don't know if the following will make any difference, but in Excel's Options, under Advanced, in the Editing options section, there's a checkbox Extend data range formats and formulas; perhaps changing that setting may change the behaviour? yes extend data range format is selected.

Thank you for your suggestions. I'm unable to attach a sample file at this time
 
Back
Top