1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by Jet Fusion, Sep 4, 2018.

  1. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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

    Attached Files:

  2. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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.
    Jet Fusion likes this.
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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.

    Attached Files:

    Thomas Kuriakose and Jet Fusion like this.
  4. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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:
  5. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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:
  6. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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?

    Attached Files:

  7. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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"),

    "" )
    Thomas Kuriakose and Jet Fusion like this.
  8. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    Ah Peter always to the rescue, thank you very much. :awesome::awesome: Double awesome for you :DD
  9. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    When i input this formula it says it has a circular ref unless i'm inputting it in the wrong cell?
  10. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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.

    Attached Files:

    Thomas Kuriakose likes this.
  11. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    Oh I miss read lol, hahahahhaha sorry blonde moment there :confused:

    Thank you once again ;)
  12. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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
  13. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    379
    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?
  14. Jet Fusion

    Jet Fusion New Member

    Messages:
    12
    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
    Peter Bartholomew likes this.

Share This Page