Automatically Extract Data from Table and Input to Data Table Suitable for Pivot Table

T

theteerex

Board Regular
Joined
Mar 2, 2018
Messages
102
Hi All,

I am trying to link two tables such that inputs to one automatically creates a new set of data in another.
I have a Project table that contains a list of projects and their data. The table is arranged in such a way that each project is one row.
I also have a Data table that feeds a pivot table used to analyze the project data.

What I have done so far:
  1. I have created a Master table within the Data table.
  2. I have created a button that automatically copies this Master table and pastes it in the first empty row in the Data table.
  3. The user then changes the name of the project so that the Data table knows to go to the Project table and pulls the correct information into the Data table.
Is there a way to automate these tasks in the following way
  1. Once a user inputs information in the Project table, it is automatically detected by the Data table?
  2. Once it is detected, the Data table creates a copy of the Master table and then;
  3. Inputs the correct information, based on the new entry in the Project table?
I am basically using the Master table as a template so users do not mess up formatting, making the Data table useful for pivot analysis.
l72bs9V8ydF7eU3W2

l72bs9V8ydF7eU3W2


Please let me know if I haven't explained anything clearly.
Thank you for your help.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
To be more descriptive, I start with projects in a row on another sheet.
ProposalTypeProject_NoDeliveryCountryFabricatorCountryColumn1Unit TypeSizeUnitsSoldDateQuarterYearStatusDetailDesignerFabricator01_Fabrication01_FabCon02_Detail Design02_DDCon03_Shipping_duties
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4/13/201522015SoldABCXYZ $ 6,485,869.94 $ 827,485.00

<tbody>
</tbody>


I then transfer useful data to another worksheet in the format below so they can be analyzed with a pivot table.
Is there a way for me to have the data automatically be pulled in and follow the rules I have in place?

Project

<tbody>
</tbody>
TypeProjectNumberDeliveryCountryFabricatorCountryUnit TypeSizeUnitsSoldDateParameterQuarterYearStatusDetailDesignerFabricatorCategoryValue
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4210722015SoldABCXYZ01_Fabrication6485869.94
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4210722015SoldABCXYZ01_FabCon0
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4210722015SoldABCXYZ02_Detail Design827485
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4210722015SoldABCXYZ02_DDCon0
MoneypitProject1234567ME & AfricaS. KoreaISOM700BPSD4210722015SoldABCXYZ03_Shipping_duties0

<tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
How to automatically update a master table with tables from other worksheets ?
Replies
0
Views
204
Andre989
A
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
967
Alex Blakenburg
A
duteberta
  • Question
Correct language for referencing table
2
Replies
15
Views
240
Alex Blakenburg
A
D
  • Question
Update row data using another worksheet data
Replies
1
Views
146
denz0215
D
R
  • Question
Collecting data to an Invoice
Replies
2
Views
174
Runejors
R

Forum statistics

Threads
1,214,606
Messages
6,120,487
Members
448,967
Latest member
visheshkotha

Share this page

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
Back
Top