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
hello?
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
okay
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
s
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
Hello?
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
yep
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.
