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

Frequent Contributor

hi

 

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 ??

 

Thanks

4 Replies

@bbsin 

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.

hi

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?  

 

Thanks 

@bbsin 

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.

image.png

hi Peter,

Thanks will give it a try

Cheers