How would I create a new table in excel using specific data from columns of another table-CodePudding

So what I'm trying to do is I'm trying to populate another table in excel using data from two columns in my main table. The two columns are "Report Deadline" and "Date Report Issued". I want to take the rows of the "Report Deadline" dates that have already passed ( <= TODAY() ) and put I want to view them in the smaller table only if there is no value in the "Date Report Issued" column as well

Example of what I am looking for:

CodePudding user response：

If you can be flexible on the data not being in a table, and you have spill range functionality, then this first formula should bring in the values as your screen shot shows. It won't be a table, however I don't think you can create a function that changes the number of rows in a table without doing some VBA (which you didn't list as a potential option in your question).

=FILTER(A:D,(ROW(C:C)=1) ((C:C>0)*(C:C<TODAY())))

Alternatively, you could add a helper column to your existing table that could be used to consistently filter data to display your objective. Something like this:

=AND([@[Report Deadline]]<TODAY(),[@[Date Report Issued]]="",[@[Report Deadline]]>0)