R rahulshewale1 Active Member Jan 2, 2017 #1 Dear sir, please find the attached sheet.i want lookup item description,item qty. Thanks Rahul shewale
Dear sir, please find the attached sheet.i want lookup item description,item qty. Thanks Rahul shewale
R rahulshewale1 Active Member Jan 2, 2017 #2 sorry i forgot to upload sheet Attachments chandoo.org Consuption report 4 month - Copy.xls 36 KB · Views: 7
B bosco_yip Excel Ninja Jan 2, 2017 #3 Try, 1] In "Sl no" AR3, formula copy down : =IF(ROWS($1:1)<=COUNTA(T$3:T$100),MAX(AR$2:AR2)+1,"") 2] In "Item desc" AS3, formula copy down : =IF(AR3="","",INDEX(T$3:T$100,AGGREGATE(15,6,(ROW(T$3:T$100)-ROW(T$3)+1)/(T$3:T$100>0),AR3))) 3] In "Item Total Qty" AT3, formula copy down : =IF(AR3="","",INDEX(V$3:V$100,AGGREGATE(15,6,(ROW(I$3:I$100)-ROW(I$3)+1)/(I$3:I$100="Item Total Qty/Value"),AR3))) Regards Bosco Attachments Consuption report 4 month.xls 40.5 KB · Views: 11
Try, 1] In "Sl no" AR3, formula copy down : =IF(ROWS($1:1)<=COUNTA(T$3:T$100),MAX(AR$2:AR2)+1,"") 2] In "Item desc" AS3, formula copy down : =IF(AR3="","",INDEX(T$3:T$100,AGGREGATE(15,6,(ROW(T$3:T$100)-ROW(T$3)+1)/(T$3:T$100>0),AR3))) 3] In "Item Total Qty" AT3, formula copy down : =IF(AR3="","",INDEX(V$3:V$100,AGGREGATE(15,6,(ROW(I$3:I$100)-ROW(I$3)+1)/(I$3:I$100="Item Total Qty/Value"),AR3))) Regards Bosco
