Editable Pivottable

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
Ok I know the heading is horrible but let me explain:

I have a large Inventory spreadsheet.
Column B: Part name
Column C: Quantity on hand
Column D: Re-Order (basically an X indicates it needs to be re-ordered when quantity is 0 or 1)

I have a pivot table on Sheet 2 that will show all the Items with a 1 or a 0 that also have the X
Basically its a condensed table for the person who does the ordering so they don't need to weed through the main list to see what needs to be ordered.

All I want to do is when a part comes in, is to edit this pivot table with the new quantity and have it update the main list. This way I don't need to sort through the main list to find the incoming part. This is to make receiving parts faster and less mistakes.

Obviously with a pivot table you cannot edit the list. its just a visual thing.

I have VBA code on the main list (ThisWorkbook.RefreshAll) to refresh that table. I'm hoping if I can edit the pivot table, it will then in turn refresh the pivot table as I receive parts.

Attached are the two tabs:
Parts: Main parts list (400+ parts normally)
Re-Order: The condensed list which I want to use to receive parts and edit the quantity as they arrive
 

Attachments

  • Re-Order.JPG
    Re-Order.JPG
    114.8 KB · Views: 3
  • Parts.JPG
    Parts.JPG
    169.6 KB · Views: 3

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
I need to mention that this is stored on a Sharepoint site. However, we open it locally so the macros and VBA code work.
 

jonnn21

New Member
Joined
Feb 9, 2012
Messages
21
I figured out a work around. I created two macros. One to filter the items and another to clear the filters.
I then inserted a Toggle button and assigned the macros to the button. Works the same way and is obviously editable
 

Watch MrExcel Video

Forum statistics

Threads
1,114,570
Messages
5,548,837
Members
410,877
Latest member
RaeB
Top