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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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,520
Messages
5,548,528
Members
410,845
Latest member
OldSwimmer1650
Top