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 Z. Y. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/04/2018 - 07:21
Hi…Welcome to Got It Pro
Excelchat Expert
25/04/2018 - 07:21
Could you please share your data or the file?
User
25/04/2018 - 07:25
Ok so lets say I have someone giving someone else a gift today
Excelchat Expert
25/04/2018 - 07:26
ok
User
25/04/2018 - 07:26
I want to be able to add these in and have them automatically total for YTD in the YTD tab
User
25/04/2018 - 07:26
and I want it sorted under rep code and then other party
User
25/04/2018 - 07:27
the purpose is to track the amounts per rep per other party for the year
Excelchat Expert
25/04/2018 - 07:28
could you also show the format that you want in YTD tab?
Excelchat Expert
25/04/2018 - 07:28
are you doing this in excel or google sheet in your actual work?
User
25/04/2018 - 07:29
In excel, I'm just showing you an example. The data is confidential
Excelchat Expert
25/04/2018 - 07:29
ok
Excelchat Expert
25/04/2018 - 07:30
ok...got you
User
25/04/2018 - 07:30
I have tried the consolidated table wizard and that doesn't work because I can't separate out the other party names into rows
Excelchat Expert
25/04/2018 - 07:31
well, pivot also will not work as you are trying to consolidate the tables in different tabs.
User
25/04/2018 - 07:31
I am attempting the data model in pivot table to connect everything
Excelchat Expert
25/04/2018 - 07:31
but a formula will work.
Excelchat Expert
25/04/2018 - 07:31
Let me set that up for you so that you can replicate it
User
25/04/2018 - 07:32
Ok, will I be able, say I get this set up and tomorrow someone gives a $5 gift and I go in and put it into the table, will it update?
Excelchat Expert
25/04/2018 - 07:32
yes, off course
Excelchat Expert
25/04/2018 - 07:33
You will need to make a list of Rep code to be use as filter
Excelchat Expert
25/04/2018 - 07:33
I will be using a data validation as a filter, is that ok?
User
25/04/2018 - 07:33
like a list of all the rep codes?
Excelchat Expert
25/04/2018 - 07:33
yes
Excelchat Expert
25/04/2018 - 07:34
and you can keep adding or deleting
User
25/04/2018 - 07:34
hmm, ok I don't have that, I just export this data and then use that data
Excelchat Expert
25/04/2018 - 07:34
I am working on a downloaded excel file
Excelchat Expert
25/04/2018 - 07:35
You can then use this as template
User
25/04/2018 - 07:43
ok thanks
Excelchat Expert
25/04/2018 - 07:44
will you be having multiple entries of other party on the monthlyl sheets
User
25/04/2018 - 07:46
ok
Excelchat Expert
25/04/2018 - 07:52
ok, then its going to work only through data consolidation model
Excelchat Expert
25/04/2018 - 07:52
I am working on that
User
25/04/2018 - 07:54
okie!
Excelchat Expert
25/04/2018 - 08:08
this can be either by rep code or by Other party
Excelchat Expert
25/04/2018 - 08:08
I have tried multiple ways but we cannot get both the criteria
Excelchat Expert
25/04/2018 - 08:09
Alternatively will be to pull all the month tabs to a consolidate YTD tab through query and then do a pivot from the consolidated data.
Excelchat Expert
25/04/2018 - 08:11
[Uploaded an Excel file]
User
25/04/2018 - 08:12
ah ok, yeah I already have the full list of the gifts thus far, but I want to be able to run a monthly report and then copy that tab in and have it add to the YTD is all but maybe I should go a different route
Excelchat Expert
25/04/2018 - 08:13
sql query will be the best way
Excelchat Expert
25/04/2018 - 08:13
You won't need any coding, query wizard is a very helpful and self explanatory
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.
