Mar 05 2018
11:52 AM
- last edited on
Jul 25 2018
11:16 AM
by
TechCommunityAP
Mar 05 2018
11:52 AM
- last edited on
Jul 25 2018
11:16 AM
by
TechCommunityAP
Hi,
I am trying to import my google sheet into my SharePoint. I have already noticed when I download my google sheet into excel the formulas are not compatible. So I was hoping someone could provide me a compatible formula for my now excel sheets.
I have a total of 6 different sheets. Four sheets (sheets 1-4) need to feed information into the two other sheets (sheet 5 and Sheet 6).
Sheets 1-4 columns A-E need to feed into Sheet 5. If and only if columns J-AB (Sheet 1-4) has the word, "Done" or "NA" filled in. (column a=Product, column b= Stability, column c= Lot Number, column d=Submitted date, column e=due date.
Here is google sheets formula for sheet 1 to feed into sheet 5:
={ʺProductʺ, ʺStabilityʺ, ʺLot Numberʺ, ʺSubmitted Dateʺ, ʺDue Dateʺ;Filter(Sheet1!A2:E,Arrayformula(IF(Sheet1!A2:A=ʺʺ,, MMult( (Sheet1!H2:AB=ʺDoneʺ) + (Sheet1!E2:AB=ʺNAʺ), Transpose(Sign(Column(Sheet1!E2:AB2))))))=Columns(Sheet!E2:AB))}
Sheets 1-4 I need to feed into Sheet 6 columns A-E.
Here is google sheets formula: This is the formula to feed Sheets 1-4 into Sheet 6
=QUERY({Sheet1!A1:Z;Sheet2!A2:Z;Sheet3!A2:Z;Sheet4!A2:Z}, ʺSelect *where Col1 is not null order by Col4 ascʺ,1)
Thanks for your help.