Table dynamic to PivotTable

Mack23

New Member
Joined
Aug 11, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a current issue with trying to use a table to add two new columns to a pivot table. The pivot table pulls all the needed information, but the order needs to have a discounted column and a discounted total column for new pricing. the discounted column is manually entered and the discounted total column will need to multiply the discount column and the quantity column, which is in the pivot table.

Is there a way to add two columns in table format that update the needed rows with how many rows the pivot tables are using. the pivot table changes length depending on the amount of product the customer is ordering. I have been coming up short on finding any solutions or threads that have addresses this before. I do not think a power query will work in this instance, unless you can manually add data to a column of a power query.

Thank you,
Mack
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Post your data and the expected result to make things clear.
 

Mack23

New Member
Joined
Aug 11, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Here is a quick pic of the scenario. The pivot on the left changes as the source data is updated with more or less of different products. There are times that a discount price is used but has to be manually inputted per customer, hence the reason for a "discount table" that I am trying to link dynamically to the pivot table as it changes per the source data. I have not been able to get anything to work so far and it doesn't look good that I can actually link a table to the changing size of the pivot table.

1631104872963.png
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Show the source data and the discount table to show how they are related.
Generally speaking, if you have two tables you can add them to the data model and link them. Then you can make a pivot based on the relationship between them.
 

Mack23

New Member
Joined
Aug 11, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
I think I know what you mean, but the two discount columns are manually updated after the pivot table is updated (blank until discount prices are inputted to those products in the pivot table at that time). The discount table is more of an add-on after all the source data and pivot table has been updated so I don't think the data model-link method will work. I might be misunderstanding how to do a data model link, but I think that method needs both the source data/pivot table and the discount table updated with needed data before the refresh happens, which is not the case.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,573
Messages
5,765,181
Members
425,266
Latest member
CPAgirl

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