Forum Discussion

bbsin's avatar
bbsin
Iron Contributor
May 25, 2021

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

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, N...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 25, 2021

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.

bbsin's avatar
bbsin
Iron Contributor
May 26, 2021

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 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 26, 2021

    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.

    • bbsin's avatar
      bbsin
      Iron Contributor
      May 27, 2021
      hi Peter,

      Thanks will give it a try

      Cheers

Resources