majidsiddique
Board Regular
- Joined
- Oct 22, 2018
- Messages
- 164
Hi all,
I have a problem in issue the products on the basis of PO wise ( PO means purchased order issue by Hospital with all term and condition QTY, PRICE ,Supply, etc. ). We can’t serve complete PO qty on a one day with different reason but issue to hospital on demand basis. There is a problem in PO No because same PO No issued by another hospital with different or same item description.
In this way I make a small summary of PO wise detal issue by hospital:
In H3 used formula to find out value of PO Qty issued by Hospital:
=IF(AND(D3=$M$3:$M$7,E3=$N$3:$N$7),VLOOKUP(E3,$N$3:$P$7,3,0),0)
In H7 to H9 formula failed:
H7=0, H8=#value!, H9= #value! Result
In j3:
I have to balance out of PO Last balance Qty – issued Qty automatic like below mention table.
I find out out small PO balance Qty in a table but, how to apply with minus issued Qty.
P9={=SMALL(IF((n9=D3:D12)*(o9=E3:E12),J3:J12,""),ROWS(A1:A1))}
When PO balance Qty of any PO no ( 95255 and NICVD ) will be Zero then all PO partial would be changed PO complete where they are.
If anybody has a better idea to record this transaction in a different way to easy find out my result then tell me.
It is manual sheet update with small data:
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
I have a problem in issue the products on the basis of PO wise ( PO means purchased order issue by Hospital with all term and condition QTY, PRICE ,Supply, etc. ). We can’t serve complete PO qty on a one day with different reason but issue to hospital on demand basis. There is a problem in PO No because same PO No issued by another hospital with different or same item description.
In this way I make a small summary of PO wise detal issue by hospital:
In H3 used formula to find out value of PO Qty issued by Hospital:
=IF(AND(D3=$M$3:$M$7,E3=$N$3:$N$7),VLOOKUP(E3,$N$3:$P$7,3,0),0)
In H7 to H9 formula failed:
H7=0, H8=#value!, H9= #value! Result
In j3:
I have to balance out of PO Last balance Qty – issued Qty automatic like below mention table.
I find out out small PO balance Qty in a table but, how to apply with minus issued Qty.
P9={=SMALL(IF((n9=D3:D12)*(o9=E3:E12),J3:J12,""),ROWS(A1:A1))}
When PO balance Qty of any PO no ( 95255 and NICVD ) will be Zero then all PO partial would be changed PO complete where they are.
If anybody has a better idea to record this transaction in a different way to easy find out my result then tell me.
It is manual sheet update with small data:
Dec.No | Date | PO No | Hospital Name | Item | Description | PO Qty | Supply Qty | Balance | PO Status |
KCN-001 | 01/11/2018 | 95255 | NICVD | RINT | Resolute | 1500 | 100 | 1400 | PO Partial |
KCN-002 | 02/11/2018 | 62500 | TABBA | INT | Baloon | 4000 | 200 | 3800 | PO Partial |
KCN-003 | 04/11/2018 | 95255 | TABBA | INT | Baloon | 4000 | 400 | 3400 | PO Partial |
KCN-004 | 05/11/2018 | 95255 | NICVD | RINT | Resolute | 1500 | 200 | 1200 | PO Partial |
KCN-005 | 05/11/2018 | 95255 | TABBA | INT | Surgery | 4000 | 500 | 2900 | PO Partial |
KCN-006 | 06/11/2018 | 95255 | NICVD | RINT | Resolute | 1500 | 900 | 300 | PO Partial |
KCN-007 | 07/11/2018 | 95255 | TABBA | INT | Baloon | 4000 | 1000 | 1900 | PO Partial |
KCN-008 | 09/11/2018 | 95255 | NICVD | RINT | Resolute | 1500 | 300 | 0 | PO complete |
KCN-009 | 10/11/2018 | 95255 | TABBA | INT | Baloon | 4000 | 2000 | 900 | PO Partial |
KCN-010 | 10/11/2018 | 95255 | TABBA | INT | Baloon | 4000 | 900 | 0 | PO complete |
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>