• 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.

Table and Formulas

Jet Fusion

Member
Hi all,

Firstly I have submitted this before, but this is the final spreadsheet and need help with a few things if possible.

  1. Where before an "X" was used to indicate where allocations went now I need to insert a number instead of an "X" and it should carry thro to Master. If this could be done for now I'd appreciate it.
Then...
  1. This sheet will get sent to individuals and then back to 1 person who oversees the data so they will receive 4 WS back. What would be the best way to get the data back into the original document? Trying to not have to copy and paste.
  2. Maybe I'm going about this spreadsheet the wrong way :eek: :confused: o_O
  3. If VBA is required to do this, that's fine if someone can help with the coding :DD
Thank you in advance...

Jet
 

Attachments

  • TS - FINAL.xlsx
    70.8 KB · Views: 4
Given that you are using an up to date version of Excel, the ideal route is probably to leave the four data tables in separate workbooks and bring them together into a single table using Power Query. The cross-tab layout you are working with are hard work to implement whereas normalised data can be processed and then use a Power Pivot for charting and crosstab displays.

Given that you appear to be close to getting something workable I would recommend bringing your current approach to completion before researching powerful but very different strategies.
 
Hi, one or two ideas but without a specification to work to I am not sure they are going in the right direction. One formula brings a value forward rather than the default "x". The other formula show an approach to summing alternate columns without needing to select each term individually.
 

Attachments

  • TS - FINAL (PB).xlsx
    69.5 KB · Views: 2
Hi Peter,

Thank you for your reply and for the file. It works great.

Initially, they wanted just to put in X in every month to show the allocation but then some said that some months might have different allocations, which is understandable. I forgot to mention and change the Time Allocation on the Data sheet to Total Time Allocation which might have made it easier, my apologies.

I do however like that you have an option of either the x for months that might have the same or if you need to put in an alternative. I will leave it as is, for now, to see how that works.

Thank you for helping out much appreciated, good work, keep it up :) :awesome:
 
Thanks to you I have managed to get it to a working state :DD

I think the only is the copying back and forth like I have atm but will have to do for now till some better can be done,:confused:;) will continue to work and research to see if I can improve on the current one.

I am very interested and will do research about the Power Query. Thank you once again for helping out. :awesome:
 
Hi,

Is there a formula to calculate the total time allocation on the data sheet? So where you put a x instead of an amount that it also calculates when you do a total time allocation with the rest of the months?
 

Attachments

  • TS - FINAL (1).xlsx
    14.2 KB · Views: 1
Yes, you would simply need to count the "x"s.
=SUM(Data1[@[Jan ''18]:[DEC ''19]])
+ COUNTIF(Data1[@[Jan ''18]:[DEC ''19]],"x")*[@[TIME ALLOCATION]]


You may do better, however, to allow the manager to input a total and then calculate the distributed values from it. That would require reversing the formula to give a 'standard' allocation to be
=IF( COUNTIF(Data1[@[Jan ''18]:[DEC ''19]],"x"),
( [@[TOTAL TIME ALLOCATION]] - SUM(Data1[@[Jan ''18]:[DEC ''19]]) ) / COUNTIF(Data1[@[Jan ''18]:[DEC ''19]],"x"),

"" )
 
=IF( COUNTIF(Data1[@[Jan ''18]:[DEC ''19]],"x"),
( [@[TOTAL TIME ALLOCATION]] - SUM(Data1[@[Jan ''18]:[DEC ''19]]) ) / COUNTIF(Data1[@[Jan ''18]:[DEC ''19]],"x"),
"" )

When i input this formula it says it has a circular ref unless i'm inputting it in the wrong cell?
 
You have to choose. Either the Total is calculated and the 'standard' allocation is data OR the Total is the input data (a number) and the standard allocation is calculated from it. You cannot have each one calculated from the other.
 

Attachments

  • TS - FINAL (data sheet).xlsx
    16.5 KB · Views: 6
Hey me again ;)

Just a quick question, could one set the above with a formula or would it have to be VBA?

What I want to know is if a project will be 6 months duration could you insert a formula to populate the columns for that period of time (6 columns?

Thanks in advance
 
If you still have a data and a master sheet you can do pretty much whatever you wish. The problem will be 'How do you interpret missing or inconsistent data?'

For example, total does not
= duration * countMonths + hardCodedValues

With VBA, you could detect when something changes and adjust one of the other values to match. That still leaves the problem of which to change. e.g. If the total increases does the period increase or would you increase the monthly figure?
 
Hi Peter,

Thanks for the reply, okay I see what you mean. Actually, I don't know why I'm trying to make it so technical as the people using it don't even know how to use half the things anyway o_O

I think I will go with what we have accomplished and see how that goes.

Thank you once again for your assistance. :awesome::DD
 
Back
Top