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

%3CLINGO-SUB%20id%3D%22lingo-sub-2383126%22%20slang%3D%22en-US%22%3EExcel%20how%20to%20copy%20out%20only%20specific%20data%20to%20another%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383126%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20main%20data%20and%20would%20like%20to%20only%20copy%20other%20another%20sheet%20when%20the%20column%20of%20the%20start%20date%20and%20expiry%20date%20has%20info%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMain%20data%20has%20a%20lot%20of%20column%20-%20Description%2C%20Name%2C%20Start%20Date%2C%20Expiry%20Date%20etc%20-%20sheet%201%3C%2FP%3E%3CP%3Ehow%20to%20copy%20only%20when%20the%20start%20and%20expiry%20date%20has%20value%20to%20sheet%202%20with%20a%20few%20columns%20link%20like%20Descp%20and%20name%3F%3C%2FP%3E%3CP%3EIs%20there%20a%20video%20to%20show%20%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2383126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385999%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20how%20to%20copy%20out%20only%20specific%20data%20to%20another%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385999%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%3CBR%20%2F%3E%3CBR%20%2F%3Emine%20is%200365.%26nbsp%3B%20I%20try%20your%20formula%20not%20to%20sure%20the%20selectedrows%20is%20it%20for%20the%20new%20spread%3F%26nbsp%3B%20%26nbsp%3BThe%20-%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EselectedHeaders%2C%20Table1%5B%23Headers%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20it%20for%20the%20Main%20Data%3F%26nbsp%3B%20ColumnIndices%20is%3F%26nbsp%3B%20%26nbsp%3BIs%20there%20a%20video%20that%20I%20can%20view%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2386802%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20how%20to%20copy%20out%20only%20specific%20data%20to%20another%20sheet%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2386802%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F612792%22%20target%3D%22_blank%22%3E%40bbsin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20Excel%20Tables%20and%20Structured%20References%20to%20reference%20the%20source%20data%20and%20Defined%20Names%20for%20the%20data%20that%20does%20not%20form%20part%20of%20a%20table.%26nbsp%3B%20The%20LET%20function%20allows%20the%20user%20to%20break%20formulas%20down%20into%20bite%20size%20portions%2C%20each%20referenced%20by%20name.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F283827iA0B805102E84FB76%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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