Alternative for Slicer

[Farid Tanwar]

Hello Ninjas,

I’m attaching my PO tracker file I currently use a slicer to filter items quickly, but it’s getting too big and difficult to manage.

Could you please advise if there’s an alternative solution to using the slicer?

Additionally, is there any function where, once a PO is fully delivered, it can automatically move to another sheet?

Appreciate your guidance.

Best Regards
Farid Tanwar

---

 

[vletm]

# Add one slicer for Company ...
# When something is ... fully delivered?
... do something should move or paste?

 

[Farid Tanwar]

Thank you this method works well and makes it easier to move between different company POs.

I have one more request:
In the attached file, if you check the Company Name column, for example CATRION, it has multiple POs and each PO has multiple items. I’ve assigned internal references such as Catrion 14, Catrion 15, etc.

I would like a solution where:

• If all items under a specific PO are delivered,
• that entire PO (all its items/rows) automatically moves to the “CLOSED PO” sheet.
  I’m using Data Validation in the Status Column (Column L) to select “Delivered.”
  So once all items for a particular PO are marked as Delivered, they should shift to the CLOSED PO sheet automatically.

Please advise how this can be done.

 

[vletm]

# move means ... that data will really move (data will be in one time)
# what would be ... PO?
# that entire PO (all its items/rows) automatically moves to the “CLOSED PO” sheet.
This is possible but then something really moves at once!
# what do You mean about shift? ... You just wrote that those have moved to other sheet!
# Please advise how this can be done.
I need to know exact - what do You really would like to happen?

 

[Farid Tanwar]

# move means ... that data will really move (data will be in one time)
# what would be ... PO?
# that entire PO (all its items/rows) automatically moves to the “CLOSED PO” sheet.
This is possible but then something really moves at once!
# what do You mean about shift? ... You just wrote that those have moved to other sheet!
# Please advise how this can be done.
I need to know exact - what do You really would like to happen?

# move means ... that data will really move (data will be in one time)
Move to CLosed PO sheet( yes data will be moved in one time)

#what would be ... PO?
PO means Purchase Order (Cloumn D2)

# what do You mean about shift? ... You just wrote that those have moved to other sheet!
Shift means Moved, sorry for the confusion

I need to know exact - what do You really would like to happen?
Please see attached file, i have done it manually, Catrion 15 is fully delivered hence moved to the CLOSED PO sheet.

 

[vletm]

Something like this ... ?
Notes:
# move will happen after all ( ~the last one ) ... has marked as delivered
... there seems to be some ... which has already marked as delivery ... which will stay there until You'll mark one again!
# there is at least one invalid ... PO, which did a lot of troubles, until I found it
... Your data should be valid

 

[Farid Tanwar]

Awesome!!!
Just one last thing, when the PO is marked as delivered, it is should move to the closed po sheet, means it should disappear from the PO Tracker Sheet

 

[vletm]

... hmm?
As I tried to explain above and before that ...
As You've asked
# once a PO is fully delivered, it can automatically move to another sheet?
# the PO is marked as delivered
Above means ... one more time ...
Situation with the PO is that one of PO is different than 'delivered' - okay?
After that - user should change the last PO as 'delivered' - okay?
... after above change a PO will be fully delivered - okay?

If Your the latest image is something like below:
You could paste new rows there and
after that ... all those POs, which all 'members' are 'delivered' should move to ... other sheet
... that's is a different story.
Of course, it's possible to do too ... someway.

As well as...
I've noticed that too many times users just test to set one to 'delivered' and ... hups!
... user has panicked ... where is that row? ... where are 33 rows?
After minutes, user could find those row from other sheet.
... with Your the latest logic.
User will copy those rows back and ...
... what will happen next?
Answer: Those just copied rows will be 'missed' again ... guess where?

 

[Farid Tanwar]

... hmm?
As I tried to explain above and before that ...
As You've asked
# once a PO is fully delivered, it can automatically move to another sheet?
# the PO is marked as delivered
Above means ... one more time ...
Situation with the PO is that one of PO is different than 'delivered' - okay?
After that - user should change the last PO as 'delivered' - okay?
... after above change a PO will be fully delivered - okay?

If Your the latest image is something like below:
You could paste new rows there and
after that ... all those POs, which all 'members' are 'delivered' should move to ... other sheet
... that's is a different story.
Of course, it's possible to do too ... someway.

As well as...
I've noticed that too many times users just test to set one to 'delivered' and ... hups!
... user has panicked ... where is that row? ... where are 33 rows?
After minutes, user could find those row from other sheet.
... with Your the latest logic.
User will copy those rows back and ...
... what will happen next?
Answer: Those just copied rows will be 'missed' again ... guess where?

You’ve raised a valid point and I fully agree that it must be handled carefully. At the same time, I do need a method to move the PO to the Closed PO sheet once it is fully delivered. Otherwise, the main PO tracker will become overcrowded, making it difficult to use and defeating the purpose of having a tracker in the first place.

Could you please suggest a practical way to achieve this? Ideally, I need a controlled method that prevents accidental movement, but still ensures that once a PO is completed, it is moved out of the active sheet.

Looking forward to your input on how best to implement this

 

[vletm]

You're using terms like ... automatic, practical, prevents, ensures, best.
All of those depends of user acts ...
... or the code should ask a lot ... a lot to try to make sure that user really would like to do just as user has done.
I added one button there.
With it,

Farid Tanwar​

Modified file and text.
While activating sheet ...
... user can move in one time more POs ( ... but still Your original automatic works automatically and You do not need to use this option).
I've not tested many even impossible combinations which user can do or what kind of data is there.
I already noted that one invalid PO - You should take care it.
Remember Backups.

If something not work then click [ YES ]-button.