HI,
i have a file in which i generate a report of how much material has packed and shipped. in sheet "FINAL REPORT" there is a formula cell A4. that shows the report when we select drop down in F1 & H1.
my requirement is that if we leave F1 blank and select only H1 then the data should be according to that.
i mean to say that FI is drop down of quality which belongs to sheet "UNIQUE ENTRIES" COLUMN B.
i want that when we select only p.o. no.(3966, 3965 etc) which belongs to sheet "UNIQUE ENTRIES COLUMN A. all regarding qualities should be shown up and when we select drop down in F1 this should be as it is now. means if F1 is blank then result for all quality and if F1 is selected then result only for that particular quality.
given formula is currently working in sheet " FINAL REPORT", in cell a4.
=arrayformula(
query( query( { query(
{ UNI, row(UNI) }, "where Col1 = " & H1 & " and Col2 = '" & F1 & "' order by Col12, Col1, Col2, Col3", 1 );
query( { UNI, row(UNI) }, "select Col1, Col2, Col3, 'Total', sum(Col5), sum(Col6), sum(Col7), sum(Col8), sum(Col9), sum(Col10), sum(Col11), max(Col12) where Col1 = " & H1 & " and Col2 = '" & F1 & "'
group by Col1, Col2, Col3 order by max(Col12), Col1, Col2, Col3 ", 1 ) }, "where not Col4 contains 'Total""()' order by Col12, Col1, Col2, Col3", 1 ), "select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11", 1 ) )
and some one has suggested me to joint this formula with above. here it is.
& if( len(F1), " and Col2 = '" & F1 & "', ", "" ) & "
i have tried to joint them but couldn't. so please solve it.
Bookmarks