• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summarise Resources between two dates into Monthly Portions # 2

P

Pete Mccann

Member
Dear all - this is a follow up question on the original post (Summarise Resources between two dates into Monthly Portions). Further to Bosco's suggestions, I have developed the spreadsheet. I have now come to a difficult position where I need some more help. I am attaching two documents: a spreadsheet that shows the concept and a word document that shows the challenge. I would really appreciate some more help on this because this would be a really useful tool to have available.

Many thanks (in advance).
 

Attachments

  • Resource names summing by month.docx
    13.8 KB · Views: 5
  • Resource names summing by month_Ed 2.xlsx
    233.1 KB · Views: 7
Pete Mccann
If one of Your point was to get Range B3 : P32 calculated
this would be one possible solution.
Press [Do It]-button.
Ps. I cleaned some ranges.
 

Attachments

  • Resource names summing by month_Ed 2.xlsb
    22.6 KB · Views: 5
Last edited by a moderator:
@Pete Mccann Do you have Power Pivot? If so, you can set up a pivot table with slicers to show this quite easily. Let me know if you have PP, I can post the technique.
 
Thanks for your help vletm. The formulae in Column Y onwards were removed so the calculations that were in B3 : P32 no longer work. If I use the format control box to select a particular resource (R1 to R7 or All) and then run the "Do It" button, the data in the range B3 : P32 does not change. r2c2 has suggested a Power Pivot approach to solve the questions and I am investigating this option too.
 
Last edited by a moderator:
Pete Mccann
Range B2 : B32 values comes from Macro; no need to have 435 formulas.

I didn't add those 'Resource nor Activity'-options with previous sample.
Now, You can (have to) select needed combinations from both (de/activate by mouse). If You select a header, then You'll select all names.

Range B2 : B32 values will refresh only after [Do It].

Yes, it would be good to test Power Pivot.
 

Attachments

  • Resource names summing by month_Ed 2.xlsb
    29 KB · Views: 9
Macro-enabled sheets have the suffix .xlsm. If you save as .xlsx, then the macro will be lost.
 
Thanks AliGW. The file that vletm created is a Binary Worksheet. I am trying to understand if I can use the macro that was created in a macro enabled spreadsheet (sorry, you are correct, when I said .xlsx file, I meant to say .xlsm file).
 
Pete Mccann
You asked: Can I use this macro in an xlsx file? If so, how do I do that?
...later with .xlsm-file.
The answer is 'Yes' ... but why to change format?
btw ... with different layout, that would be more useful including other features
... all depends Your needs!
 
Pete Mccann said:
Thanks r2c2. Yes, I have managed to Add in the Power Pivot tab. I look forward to seeing your technique for this sort of analysis.
Click to expand...

Just in case you are still curious to see how to do this with Power Pivot:

Note: the measures used in this are not beginner level. So if you are new to DAX, you may want to learn more before trying to tweak them. I suggest our online power pivot class or Rob's DAX book.

What you need?

  • Your project data in tabular format. Just columns Q:X in your original file are sufficient. Convert this data to a table and name it plan.
  • A calendar table of all dates between 31Mar2017 and 15Jun2018 (or whatever max date you want). Call this table calendar. The table should look like this:

    calendar-table-format.PNG
Load both tables as linked tables in to Power Pivot. Do not connect both tables. We want calendar table to be disconnected to our plan data.

Now, insert a pivot thru Power Pivot.

Add Activit_name in column labels area, year and month from calendar table in row label area.

Add below measures thru New Measure button in PP ribbon.

SelActivity:= FIRSTNONBLANK(plan[Activity_Name],true)
TotalActs:= SUMX(calendar, countrows(filter(plan,plan[Activity_Name]=[Sel Activity] && plan[Start] <=calendar[Date] && plan[Finish] > calendar[Date])))

Add the TotalActs measure to values area of the pivot.

Set up a slicer on Resource Names if needed.

Done. Your interactive pivot with number of items by month, code and resources is ready.

See attached workbook.
 

Attachments

  • project-plan.xlsx
    279.5 KB · Views: 3
Dear r2c2. Thank you so much for the effort you have put in to creating this pivot table. The technique is really interesting. I will check out the links you've provided above. If I need any more help, I will post a new thread.
 
Dear vletm. I traditionally work with .xlsx files or (sometimes) .xlsm files. Most of the people I pass my spreadsheets to also normally work with .xlsx files so if I pass them a binary file it would no doubt get saved as an .xlsx file and get corrupted. Is there a link somewhere that explains the benefits of using binary files vs. xlsx files? The macros you've produced are excellent and very useful. Thanks for all your work on this thread. If I need any more help I will post a new thread.
 
Pete Mccann
If You use 'only formulas' then .xlsx would be Okay.
If You would like to use macros then
it would be 'better'*) to save with .xlsb or .xlsm -format.
Two snapshots below, which would explain something for You:
Screen Shot 2017-08-22 at 10.41.31.png Screen Shot 2017-08-22 at 10.41.50.png
*) It is possible to save and even 'use' that file with xlsx-format ...
but then You should run those macros from other file - useful?
For me, 'xlsb' means smaller file size than 'xlsm'.
Normally, I do not even want use same formula many times in my sheets.
I would use VBA to do almost same.
If there are massive amount of formulas in sheet and
for some case, one formula will 'cleared' for 'mistake'
and many times users do not notice ... it for ... few weeks ... Yes!
As You wrote: (You) I traditionally work with .xlsx files or (sometimes) .xlsm files.
> Would it be time to test something 'new'?
 

Attachments

  • Resource names summing by month_Ed 2.xlsm
    40.2 KB · Views: 3
You must log in or register to reply here.
Back
Top