Excel how to copy out only specific data to another sheet?

Frequent Contributor



I have a main data and would like to only copy other another sheet when the column of the start date and expiry date has info in.


How to?


Main data has a lot of column - Description, Name, Start Date, Expiry Date etc - sheet 1

how to copy only when the start and expiry date has value to sheet 2 with a few columns link like Descp and name?

Is there a video to show ??



4 Replies


It might be a good idea to specify the Excel version.  I would use something of the sort

= LET(
  selectedrows, FILTER(Table1, (Table1[Startdate]>0)*(Table1[Enddate]>0)),
  k, SEQUENCE(ROWS(selectedrows)),
  columnIndices, XMATCH(selectedHeaders, Table1[#Headers]),
  INDEX(selectedrows, k, columnIndices) )

but the process is built on functionality that is specific to Excel 365.


mine is 0365.  I try your formula not to sure the selectedrows is it for the new spread?   The - 

selectedHeaders, Table1[#Headers]


is it for the Main Data?  ColumnIndices is?   Is there a video that I can view?  




I use Excel Tables and Structured References to reference the source data and Defined Names for the data that does not form part of a table.  The LET function allows the user to break formulas down into bite size portions, each referenced by name.


hi Peter,

Thanks will give it a try