May 25 2021 03:45 AM
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
May 25 2021 04:38 AM
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.
May 25 2021 09:28 PM - edited May 25 2021 09:44 PM
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
May 26 2021 03:32 AM
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.
May 26 2021 05:10 PM