Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast
Results 21 to 30 of 46

Thread: Excel Sheet Setup (Advance)

  1. #21
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Here's where I've got to on this thus far:Han 20130710.xlsb

    Things to note:
    0. This approach relies heavily on table functionality and the fact that tables can be set to automatically expand when new data is put in the first empty row below. Sometimes this auto-expand functionality gets inexplicably turned off, and the setting to turn it on again is so well hidden that you'll never find it without knowing where it is. So here's where it is: In Excel Options click Proofing, then click the 'AutoCorrect Options' button. On the 'AutoFormat as You Type' tab, check the box labeled "Include new rows and columns in table". I could include a line of code that enforces this option...but need to check with you first if you generally have this option turned off (I doubt it, but you never know).
    1. I've revised the conditional formatting formulas, and also added the new CF to the summary sheet. So formatting is now handled dynamically
    2. If someone sorts the data entry tabs, you're screwed. That's because there's nothing that denotes what the 'Actual' rows actually relate to. To be safe, you either need to copy all the info from the reg/region/customer/partnumber/aircraft etc fields from the 'Forecast' to the 'Actual' so that you can always match these things back up again, or you want to add a unique number that is shared between pairings of forecast and actual. My suggestion would be to add a button, that inserts an 'Actual' row below the currently selected 'Forecast' row, so that all info but the date and financial info is also copied to the new 'Actual' row. I've done something similar on another project, and can assist if this is the way you want to go.
    3. Note that I've added a 'Forecast or Actual' column too. This is what the conditional formatting uses to determine what rows to format.
    4. Note I've added a 'Source?' column at the far right of each table. You can hide this from users...I just left it visible so you could see it. This auto-populates whenever you add another row to the table, so users don't have to fill it in. But it is required if you want to see on the Summary tab where the various information was pulled from.
    5. If adding additional columns to the input and summary tables, DON'T insert a whole column into the workbook, as this will screw up placement of the hyperlink tabs. Instead, select the column in the table, right click, select 'Insert', then 'Table Column to the left'


    Look forward to your feedback, and happy to help with ongoing tweaks.
    Last edited by JeffreyWeir; 2013-07-10 at 05:21 AM.

  2. #22

    Jeff,

    This looks amazing! We're making significant progress here. To your previous comments, Yes, there will be an "Forecast" and "Actual" for each new entires. And yes, lets move forward pertain to our note#2, I think with the addition of this new button it'll help sort the data on an overview level, especially with what I'm about to mention below.

    In order to provide a visual presentation with our data (creating sand chart) I want to create a new tab with basically the same information that's on the "Summary" page but with the addition of entire calendar months from Jan-2013 ~ Dec-2013 (Column K-V). Under each calendar month's column, we will compare the "Actual" date per all the entries entered, to the month of that column. We will then use the value under "$/YR GG" and divide that number by 12 (12 months..) and assign the divided result into the columns corresponding from the "Actual" date, forward. The formula I've used for the action I mention is this one below:

    =IFERROR(IF(ISNUMBER($H$7),IF(K$6>=EOMONTH($H$7,-1)+1,$I$7/12,0),$I$7/12),0)

    However, I've find a hard time to get this formula working.. and my assumption is because this new tab I created is a copy sheet of the "Americas"'s tab.

    What it should do:

    -This new tab (GG) will include all the entries from the "Summary" tab with exception of the "Forecast" date, so it should only ready one line per entry.
    -On columns K-V will be list of months from Jan-13 to Dec-13
    -If the date shown on the "LTA/#1 POS" column is May-13, then from column "O" (May-13) going forward will include the result of "$/YR GG" / 12. Anything before column "O" should be blank

    Also, my method of transferring the data from the "Summary" tab to the "GG" tab is using the "=" function, and that seems to cause problems as it shows "#REF!", I wonder if this has to do with the macro that's setup.

    Please take another look at this and let me know how I can support further.

    Thank you,

    Han

    Quote Originally Posted by JeffreyWeir View Post
    Here's where I've got to on this thus far:Han 20130710.xlsb

    Things to note:
    0. This approach relies heavily on table functionality and the fact that tables can be set to automatically expand when new data is put in the first empty row below. Sometimes this auto-expand functionality gets inexplicably turned off, and the setting to turn it on again is so well hidden that you'll never find it without knowing where it is. So here's where it is: In Excel Options click Proofing, then click the 'AutoCorrect Options' button. On the 'AutoFormat as You Type' tab, check the box labeled "Include new rows and columns in table". I could include a line of code that enforces this option...but need to check with you first if you generally have this option turned off (I doubt it, but you never know).
    1. I've revised the conditional formatting formulas, and also added the new CF to the summary sheet. So formatting is now handled dynamically
    2. If someone sorts the data entry tabs, you're screwed. That's because there's nothing that denotes what the 'Actual' rows actually relate to. To be safe, you either need to copy all the info from the reg/region/customer/partnumber/aircraft etc fields from the 'Forecast' to the 'Actual' so that you can always match these things back up again, or you want to add a unique number that is shared between pairings of forecast and actual. My suggestion would be to add a button, that inserts an 'Actual' row below the currently selected 'Forecast' row, so that all info but the date and financial info is also copied to the new 'Actual' row. I've done something similar on another project, and can assist if this is the way you want to go.
    3. Note that I've added a 'Forecast or Actual' column too. This is what the conditional formatting uses to determine what rows to format.
    4. Note I've added a 'Source?' column at the far right of each table. You can hide this from users...I just left it visible so you could see it. This auto-populates whenever you add another row to the table, so users don't have to fill it in. But it is required if you want to see on the Summary tab where the various information was pulled from.
    5. If adding additional columns to the input and summary tables, DON'T insert a whole column into the workbook, as this will screw up placement of the hyperlink tabs. Instead, select the column in the table, right click, select 'Insert', then 'Table Column to the left'


    Look forward to your feedback, and happy to help with ongoing tweaks.
    Quote Originally Posted by JeffreyWeir View Post
    Here's where I've got to on this thus far:Han 20130710.xlsb

    Things to note:
    0. This approach relies heavily on table functionality and the fact that tables can be set to automatically expand when new data is put in the first empty row below. Sometimes this auto-expand functionality gets inexplicably turned off, and the setting to turn it on again is so well hidden that you'll never find it without knowing where it is. So here's where it is: In Excel Options click Proofing, then click the 'AutoCorrect Options' button. On the 'AutoFormat as You Type' tab, check the box labeled "Include new rows and columns in table". I could include a line of code that enforces this option...but need to check with you first if you generally have this option turned off (I doubt it, but you never know).
    1. I've revised the conditional formatting formulas, and also added the new CF to the summary sheet. So formatting is now handled dynamically
    2. If someone sorts the data entry tabs, you're screwed. That's because there's nothing that denotes what the 'Actual' rows actually relate to. To be safe, you either need to copy all the info from the reg/region/customer/partnumber/aircraft etc fields from the 'Forecast' to the 'Actual' so that you can always match these things back up again, or you want to add a unique number that is shared between pairings of forecast and actual. My suggestion would be to add a button, that inserts an 'Actual' row below the currently selected 'Forecast' row, so that all info but the date and financial info is also copied to the new 'Actual' row. I've done something similar on another project, and can assist if this is the way you want to go.
    3. Note that I've added a 'Forecast or Actual' column too. This is what the conditional formatting uses to determine what rows to format.
    4. Note I've added a 'Source?' column at the far right of each table. You can hide this from users...I just left it visible so you could see it. This auto-populates whenever you add another row to the table, so users don't have to fill it in. But it is required if you want to see on the Summary tab where the various information was pulled from.
    5. If adding additional columns to the input and summary tables, DON'T insert a whole column into the workbook, as this will screw up placement of the hyperlink tabs. Instead, select the column in the table, right click, select 'Insert', then 'Table Column to the left'


    Look forward to your feedback, and happy to help with ongoing tweaks.
    Attached Files Attached Files

  3. #23
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    My method of transferring the data from the "Summary" tab to the "GG" tab is using the "=" function, and that seems to cause problems as it shows "#REF!", I wonder if this has to do with the macro that's setup. Correct. When you set up this reference, you point to particular cells in the summary table. And then when you select the Summary table, my code merrily deletes any cells in that table, which effectively cuts the 'anchor' to which your formulas point at.

    A good way around this is to construct a pivot tables that points at that summary table, and refresh the pivot any time the summary table is refreshed. So we'll always have a dynamic list replicating what's in that summary table.

    But we’d then need to point some formulas at a pivot. The problem with this is that – with the exception of data(aggregation) fields - any formulas that reference pivotfields don’t get updated by excel if the position of those fields move as a result of changes to the structure of the pivottable itself. So if you point a formula at a particular row field - say ‘Customer’ - and if you later move the position of that rowfield within the pivottable one column over, your formulas don’t get updated by excel to ‘follow’ this move, and so point to the wrong place (i.e. one column back).

    I’m working on some very clever ways to get around this. Watch this space.
    Last edited by JeffreyWeir; 2013-07-12 at 03:41 AM.

  4. #24
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Here's my starter for 10 (do they say that in your neck of the woods? Where is your neck of the woods?).NEW BD Build test.xlsb.

    Note that the formula in L4 =IF(AND(ISNUMBER($G4),L$3>=$F4,DATE(YEAR(L$3)-1,MONTH(L$3),DAY(L$3))<$F4),$G4/12,0) is hard-coded to the current position of the $/YR GG and ENGAGE columns, but as I say above as soon as you alter the structure of the pivot you will have to adjust the $F4 and $G4 bits in L4 and then copy the revised formula down and across.

    Thinking about this further as I type, a better way to tackle this issue is to actually do the monthly breakdown in the Summary table itself, rather than bolting it on "after the fact" to the pivot table. In fact, I'll do this next.

  5. #25
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Even better, do the monthly breakdown in the individual tabs, and pull it through to the summary, like this: NEW BD Build test v5.xlsb

    Breakdown is done in columns T:AI, which are hidden. These can also be hidden on the Summary tab if you wish (I haven't hidden them) but can still be viewed by way of a pivot linked to the Summary tab (see the Pivot tab).

    Only minor issue is that after making changes to the input sheets, you need to select the Summary tab (and trigger the summary macro) before details will show up in the pivot tab. But that's pretty minor. And given the info you want is in the Summary tab, maybe you don't need the Pivot tab at all.

  6. #26

    Lightbulb Re:

    Jeff,

    I see your point in the pivot and understand the work. However, this does not drive the formula correctly as discussed in the previous post. I do see the values occurred on the months but they are not correlated to the "LTA /#1 POS" date. In the summary tab, which I suppose you embedded the formula to run as part of the macro codes, it's not showing up correctly on the months. Here is what we're looking for:

    EX:
    For the entry on row 9&10, we will be using the ACTUAL row which in this case is row 10. Since the "LTA /#1 POS" date is "May-13", we should be seeing the result of ( "$/YR GG" / 12).. which in this case would be $75,000 from column X (May-13) to column AJ (APR-14)..

    The values should always start from the month listed on the "LTA /#1 POS" date going forward.

    I hope this made sense, please let me know if any specifics needs to be addressed.

    Han

    Quote Originally Posted by JeffreyWeir View Post
    Even better, do the monthly breakdown in the individual tabs, and pull it through to the summary, like this: NEW BD Build test v5.xlsb

    Breakdown is done in columns T:AI, which are hidden. These can also be hidden on the Summary tab if you wish (I haven't hidden them) but can still be viewed by way of a pivot linked to the Summary tab (see the Pivot tab).

    Only minor issue is that after making changes to the input sheets, you need to select the Summary tab (and trigger the summary macro) before details will show up in the pivot tab. But that's pretty minor. And given the info you want is in the Summary tab, maybe you don't need the Pivot tab at all.

  7. #27
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ahhh...I'd correlated them to the "ENGAGE" data instead of the "LTA /#1 POS" date. See the revised version.NEW BD Build test v6.xlsb

    Note that as per my last comment, the monthly breakdown formulas are in the individual tabs, which then get pulled through to the Summary tab. Breakdown is done in columns T:AI, which are hidden. These can also be hidden on the Summary tab if you wish (I haven't hidden them) but can still be viewed by way of a pivot linked to the Summary tab (see the Pivot tab).

  8. #28
    It makes a lot more sense now! The formula is working but for some reason it seems like it's only driving the value all the way till the end of 2013 it does not continue throughout 2014.
    EX: If the "LTA /#1 POS" date is Dec-13, the number should appear from column "Dec-13" onwards till the end of the laste "month/yr" column
    If the "LTA /#1 POS" is Jan-14, number should appear from Jan-14 forward to the last "month/yr" column

    Also, if I need to add additional "month/yr" columns, what would be the most appropriate way?


    Quote Originally Posted by JeffreyWeir View Post
    Ahhh...I'd correlated them to the "ENGAGE" data instead of the "LTA /#1 POS" date. See the revised version.NEW BD Build test v6.xlsb

    Note that as per my last comment, the monthly breakdown formulas are in the individual tabs, which then get pulled through to the Summary tab. Breakdown is done in columns T:AI, which are hidden. These can also be hidden on the Summary tab if you wish (I haven't hidden them) but can still be viewed by way of a pivot linked to the Summary tab (see the Pivot tab).

  9. #29
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Nothing was showing up because there was nothing in the $/YR GG for that particular entry. Put a value in there, and you'll see everything works fine.

    If you want to add additional columns, then - assuming your current range where the hidden columns are in the source sheets goes out to April 2014 in column AD - do the following:
    1. Input the additional dates you need in the column headers from AJ8 across. Let's say you add another year - this will take you out to AU 8. (I'm afraid you'll have to enter these manually...simply dragging the last two columns across to complete the series doesn't work properly, because for some reason Excel doesn't recognise dates in column headers as dates and just repeats the last two column headers over and over - NOT what you want)
    2. Select from AI9 to the bottom right of your table (which currently would be AU14). In the formula bar it will already have this:
    =IF(AND(ISNUMBER([@[$/YR GG]]),[@[Forecast or Actual]]="Actual",DATEVALUE(AI$8)>=[@[LTA / '#1 POS]],DATE(YEAR(AI$8)-1,MONTH(AI$8),DAY(AI$8))<[@[LTA / '#1 POS]]),[@[$/YR GG]]/12,0)
    Click in the formula bar and press CTRL + ENTER. This will enter that formula across the entire range that you've just selected.

    Unfortunately you have to do this, because you can't drag table formulas across...it destroys the references.

    Do the same for every input sheet in your source data. Don't worry about the summary sheet...this should update automatically.

    Give that a go, and post back to let me know how you got on.

  10. #30
    Jeff,

    Thanks for getting back. I understand the value must be in $/YR GG. But when I input.. say Mar-13 on the LTA column for row# 10. The output skips Jan / Feb 2014. Wonder if you could take a look at that formula for me.

    Also, thanks for the notes on adding the column. What if I just want a single year? Anything beyond the 2013 calendar month will be entered as years. So, instead of Jan-2014 Feb-2014.. it will be 2014, 2015

    Thanks as always,

    Han
    Quote Originally Posted by JeffreyWeir View Post
    Nothing was showing up because there was nothing in the $/YR GG for that particular entry. Put a value in there, and you'll see everything works fine.

    If you want to add additional columns, then - assuming your current range where the hidden columns are in the source sheets goes out to April 2014 in column AD - do the following:
    1. Input the additional dates you need in the column headers from AJ8 across. Let's say you add another year - this will take you out to AU 8. (I'm afraid you'll have to enter these manually...simply dragging the last two columns across to complete the series doesn't work properly, because for some reason Excel doesn't recognise dates in column headers as dates and just repeats the last two column headers over and over - NOT what you want)
    2. Select from AI9 to the bottom right of your table (which currently would be AU14). In the formula bar it will already have this:
    =IF(AND(ISNUMBER([@[$/YR GG]]),[@[Forecast or Actual]]="Actual",DATEVALUE(AI$8)>=[@[LTA / '#1 POS]],DATE(YEAR(AI$8)-1,MONTH(AI$8),DAY(AI$8))<[@[LTA / '#1 POS]]),[@[$/YR GG]]/12,0)
    Click in the formula bar and press CTRL + ENTER. This will enter that formula across the entire range that you've just selected.

    Unfortunately you have to do this, because you can't drag table formulas across...it destroys the references.

    Do the same for every input sheet in your source data. Don't worry about the summary sheet...this should update automatically.

    Give that a go, and post back to let me know how you got on.

Page 3 of 5 FirstFirst 1 2 3 4 5 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •