Get data into PivotTabel

Silaws

New Member
Joined
Oct 1, 2014
Messages
14
I have a sheet " get data " with a table where employees need to enter data , can anyone help me with a macro that retrieves the data to a pivot table and resets the table , or possibly just retrieves the data and puts it at the bottom of a table on a new sheet "locked data " so I can make my PivotTable therefrom
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
If you make the table in your "get data" sheet an actual table (Insert -> Table - http://www.contextures.com/xlExcelTable01.html) and then use that as the source for your pivot, you can simply refresh the pivot and any new lines will be included. This assumes that each employee would go to the last line in the table and add the data in the following line (which becomes part of the table once data is inserted anywhere in the line)

Alex
 

Silaws

New Member
Joined
Oct 1, 2014
Messages
14
Thanks Alex


But I have to do it the other way , it must idiot ensured and it will quickly become a very long list when around 20 people adding data several times a day
 

AlexandraT

Board Regular
Joined
Mar 23, 2015
Messages
144
It would still be fine I think. You would have your table with let's say 10 rows. The 11th row is not part of the table but once the employee starts typing anything in this 11th line beneath the table, the line itself will become part of the table. This happens automatically and for every new line just beneath the table. As long as the table is the source of the pivot, you can just refresh. It seems idiot proof to me. You can also set the pivot to refresh automatically every time the workbook is open as (Analyze -> Options -> Data -> check "Refresh data when opening the file").

Alex
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,878
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top