Return value after multiple check

[pradhishnair]

Hello friends,


i am in a fix please help me with my problem.

i have uploaded a file on my box account Link: https://app.box.com/s/b2af1fqy1m2dua2vgw3o

Scenario:

There are two sheets, Sheet A and Sheet B

In sheet A I have highlighted three columns Column D, H & Q. Columne D has a date, Column H has a Boq No luch like an item no or serial no and column Q contains quantity of the particular item number executed on the particular date.


What i need to achieve is in sheet B i want the quantities of column Q of Sheet A to be entered after checking for the corresponding dates and BoQ Number( item number, Sr. no)


Please help me with this problem.

 

[SirJB7]

Hi, pradhishnair!


If you define three dynamic named ranges:

RequestDateList: =DESREF(A!$D$3;;;CONTARA(A!$D:$D)-1;1) -----> in english: =OFFSET(A!$D$3,,,COUNTA(A!$D:$D)-1,1)

BOQList: =DESREF(A!$H$3;;;FILAS(RequestDateList);1) -----> in english: =OFFSET(A!$H$3,,,ROWS(RequestDateList),1)

MeasureList: =DESREF(A!$Q$3;;;FILAS(RequestDateList);1) -----> in english: =OFFSET(A!$Q$3,,,ROWS(RequestDateList),1)


Then you can use this formula for range M8:AQ284 :

=SI($A8="";"";SUMAPRODUCTO((RequestDateList=M$7)*(BOQList=$A8)*(MeasureList))) -----> in english: =IF($A8="","",SUMPRODUCT((RequestDateList=M$7)*(BOQList=$A8)*(MeasureList)))

Otherwise you should replace the names for the proper addresses.


Just advise if any issue.


Regards!