Hi all,
kindly tell me how solve this issue, we are issuing a item based on PO wise supply in hospital. PO means purchased order against hospital. in this file sharing with you, you will see that PO repeat from different hospital with different items, once PO No issued, next time it will not be issued again from the same hospital.
when PO supply complete it will auto convert to PO complete instead of PO partial.
please help me to solve this.
thanks
Why is K11 PO Complete?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi glenn,Originally Posted by Glenn Kennedy
Its a mistake, actully file is too lenghthy only one PO complete
Is this what you wanted?
=IF(C2="","",IF(ISNUMBER(MATCH(1,INDEX(($C$2:$C$15=C2)*($I$2:$I$15=0),0),0)),"PO Complete","PO Partial"))
Thanks for your reply,
but i want auto select balance,
like I2=G2-H2, and balance carry forward when PO transaction again record. remaining PO will be treat as this.
view attached file, show you with arrow direction.
Thanks.
This is the FIRST ime you have asked for that!! It was not mentioned previously.
How come the PO Number 62500 is listed in O:S as being for TABBA/Onyx/Resolute... but is alaso uased in A:K for Balloon as well. Is there another mistake ??
Last edited by Glenn Kennedy; 12-12-2018 at 06:09 AM.
its means thanks to me.
is it possible?
How come the PO Number 62500 is listed in O:S as being for TABBA/Onyx/Resolute... but is alaso uased in A:K for Balloon as well. Is there another mistake ??
Hi Glenn,
OK, I set it please review.
please review.
This STILL cannot be right... PO 62500 2000 items (S3)
Total delivered (H) 7100... but the order is still only partially complete. What is going on here???
I have assumed that this is a mistake and changed S3 to 10000. see sheet.
Last edited by Glenn Kennedy; 12-12-2018 at 08:30 AM.
thank Glenn,
i cannot change any values on it because i think excel version problem.
thanks
I do not understand your last post. You are using Excel 2010. Mine is 2013 - essentially the same. Did you enable editing when you opened my file?
Hi glenn,
My mobile excel version is old so that i cant use this,
I update this thrn reply you
Thanks
Hi glenn,
Its a great work, i am cheer
Thanks a lot
Please one thing tell me more, i want double chk in cell G2, first PO match then hospital name then pick the value because same PO number can be issued onther hospitsl.
Second, is that possible when po complete the row automatic lock no body can change the cell value
Thanks
Yes. use this in G2:
=IFERROR(INDEX($S$2:$S$6,MATCH(1,INDEX(($O$2:$O$6=C2)*($P$2:$P$6=D2),0),0)),"")
Your other request should be in a new thread as it is an entirely new question. It will probably need VBA.
For now, you're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
Thank a lot
Hi richard,
I would like to ask one more thing. Is it possible the balance of PO carryforword below in G2 cell after deduct supply?
afterall you have done it.
Thanks
I give one simple solution. If you re-structure your data its more easy of you. But give sometime to me. i will give simple solution on this issue.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Richard??? I presume you mean me (Glenn).
I do not understand what you are asking. Show me on a sample sheet.
Hi glenn,
Plz review attached file
Thanks
Dean glenn and avk,
I am maintainig a return item in sheet2. If PO returm some qty after few days due to any reason then how to settle this situation? Sometime hospital return item qty, some time said exchange item same PO.
THANKS
There is no sheet 2 in your file at post 21!!!!!
Hi glenn,
Its a normal situation when you issue items so its reture. It happens sometimes. It is also a part of it but first tell me about PO balance carry forword.
Thanks for your reply.
Thanks
So show me a sheet with returns in it. I am not a mind reader!!!
Hi glenn,
Plz review file. I maintain return and exchange sheets. Return item affect to invoice and exchange donot affect.
Its a complex model of supply item. If you have better idea to record this trasaction so please tell me. That is i am sharing with you step by step.
Thanks for your help
Hi Glenn and avk,
do you find any solution?
No. You have not shown me what your expected results are. A few arrows pointing here and there are not enough.
Hi Glenn.
first thing i want to balance Qty of PO will be down as it is. please see the pic.
second, in this i want when Qty Return from the hospital then i will add to PO Qty. one think to consider to return date.
example, return Qty 200 dated: 7-11-2018 so i will add to PO Qty. Last issue qty 1000 dated: 5-11-2018. PO qty = 2900+200=3100 then issue qty 2900 dated 9-1-2018. balance will be 200.
Thanks for tour reply.
As per your requirement, i have made little bit changes. I hope you comfortable with this.
First thing, in DataEntry sheet from column "A" to "H" enter manually data. Column "I" & "J" is status of balance quantity & Remark.
Second, in POSummary sheet : Extract unique Po Number with formula
Formula:
Please Login or Register to view this content.
also extract record hospital name, item, description : Using index, match formula.
and po qty, supply qty, return qty, balance qty : using simple sumifs formula.
For smallest value in po : using Min, if formula
For PO status : using if function.
for more details plz look attach file.
Dear AVK,
Thanks for your reply but there is some confusion. you send me a file, in this PO 95255 shows 12000 but actually PO Qty is 4000. please see the pic.
I think you are adding 4000+4000+4000=12000 but in this way not, PO Qty 4000 only.
Ok. With helper column in DataEntry sheet in K2
Formula:Please Login or Register to view this content.
Above formula in formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously.
File attach
Dear AVK,
please review my update file. here i changed little thing. when i supply qty of any PO, first i match 2 criteria, ( PO No and Hospital Name ) kindly check in this file result is not OK. both criteria will be match of issuing qty.
Thanks.
Actually no need of hospital name, because i have already taken two criteria Po No & Item. Since PO No is unique number.
I have revised file in dataentry sheet column "L" which is unique Po No.
Dear AVK,
I agree with you but we need PO and Hospital. same PO No will be generated by another hospital with same or different items.
in data entry sheet adding one more criteria hospital name.
Changed formula in column K
Formula:Please Login or Register to view this content.
Extract unique amount with Po Number criteria.
Hi AVK,
i am very near to close this, BUT tell me one thing in this formula. what is List? see the pic, finally i want hospital Name instead of PO No in Data entry sheet.
Thanks.
List mean : I define range of PO Number column called us List. Which is range =PO_SUMMARY!$A$2:$A$100000
Kindly change Unique PO No instead of Hospital Name (M1)
Hi Avk,
i changed it but formula return PO no. i want Hospital Name.
i change from list( Name ).
result like this
Hi AVK,
I changed it, there is a little bit diff. see the pic PO No correct but Hospital Differ. please tell me correct this formula.
please see the pic
Refer updated file. I have separate column of hospital name, one is unique hospital name & hospital name base on PO number.
Hope matter is now clear.
Hi AVK,
No doubt, its clear, fantastic job done,
Great,
Thanks a lot.
only final thing tell me, not related to this issue. tell me it is possible? see pic.
Thanks
Thanks for both Glenn and AVK.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks