Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Ok, I am trying to create a running workbook where I have data in different tabs (each tab is one month) that I want to add values to, and create a ytd summary tab that will track how much is being spent for each month, I am attempting to use the data model pivot table tool but am having trouble building the relationships, is there an easier way to do this ?
Solved by V. Y. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 25/04/2018 - 05:29
Excelchat Expert 25/04/2018 - 05:30
how are you and how may i help you with today?
User 25/04/2018 - 05:32
Hi! Ok, I just put an example month up. I am trying to make a running excel workbook where I can add in amounts, then have a sheet that is a running total to summarize the months to make sure the reps do not go over the ytd amount allotted
User 25/04/2018 - 05:32
does that make sense?
Excelchat Expert 25/04/2018 - 05:33
User 25/04/2018 - 05:35
I just wrote the example column headers, I can't upload the actual doc because of confidentiality reason
User 25/04/2018 - 05:35
Excelchat Expert 25/04/2018 - 05:36
yes i understand
Excelchat Expert 25/04/2018 - 05:36
so rep code is 151
Excelchat Expert 25/04/2018 - 05:36
and you want to limit that value, is that right?
User 25/04/2018 - 05:37
I just want to keep track of how much each rep is giving another party in each month, and have a ytd tab that summarizes all the reps and how much they have given throughout the year
Excelchat Expert 25/04/2018 - 05:39
can you just show me an example
Excelchat Expert 25/04/2018 - 05:40
Okay, i can see that
User 25/04/2018 - 05:41
but yeah, I want to create a YTD tab to track it all, and when I add amounts in months under specific repcodes for it to keep a running total
Excelchat Expert 25/04/2018 - 05:42
yeah i need to see that example how you want it
User 25/04/2018 - 05:45
Does that make sense?
User 25/04/2018 - 05:47
Excelchat Expert 25/04/2018 - 05:49
sorry my connection was interrupted
Excelchat Expert 25/04/2018 - 05:49
yep it makes sense
Excelchat Expert 25/04/2018 - 05:49
working on it now
User 25/04/2018 - 05:49
Ok thanks, I know how to do the pivot table if all the data is in one sheet
User 25/04/2018 - 05:50
I tried the data model but I am confused on how to make all the relationships link
Excelchat Expert 25/04/2018 - 05:57
Okay i have created a dropdown list to select the "rep code"
Excelchat Expert 25/04/2018 - 05:58
please have a look
[Uploaded an Excel file]
Excelchat Expert 25/04/2018 - 05:58
basic idea
Excelchat Expert 25/04/2018 - 05:58
like this we can select the rep code for all the months in the drop down and can keep a track of the same
User 25/04/2018 - 06:00
ok...so you used a vlookup? so the easiest/best way is to do a vlookup?
User 25/04/2018 - 06:01
I know how to do that, I was hoping for a better table or pivot table
Excelchat Expert 25/04/2018 - 06:01
Excelchat Expert 25/04/2018 - 06:01
that was a king of test thing to check if this is what is required
User 25/04/2018 - 06:01
but lets say its April, I go in to add an amount under a rep code for April, will this keep a running tab of it in the year to date tab or what?
Excelchat Expert 25/04/2018 - 06:02
yes it can keep it
User 25/04/2018 - 06:02
without updating the formula though?
Excelchat Expert 25/04/2018 - 06:02
but for that you need to have a macro/VBA code
User 25/04/2018 - 06:03
theres no way for the relationships to work within the data model of a pivot table?
Excelchat Expert 25/04/2018 - 06:05
yep, it would have been done using a pivot but all those data's need to be on the same sheet for a better comparision and traking
Excelchat Expert 25/04/2018 - 06:07
Are you there?

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc