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

excel
Formulas and Functions

4 Replies

  • 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.

Resources