Excel Sheet Setup (Advance)

Here's where I've got to on this thus far:View attachment 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:
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

Here's where I've got to on this thus far:View attachment 1473

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.
Here's where I've got to on this thus far:View attachment 1473

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.
 

Attachments

  • NEW BD Build test.xlsb
    44 KB · Views: 14
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:
Here's my starter for 10 (do they say that in your neck of the woods? Where is your neck of the woods?).View attachment 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.
 
Even better, do the monthly breakdown in the individual tabs, and pull it through to the summary, like this: View attachment 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.
 
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

Even better, do the monthly breakdown in the individual tabs, and pull it through to the summary, like this: View attachment 1487

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.
 
Ahhh...I'd correlated them to the "ENGAGE" data instead of the "LTA /#1 POS" date. See the revised version.View attachment 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).
 
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?


Ahhh...I'd correlated them to the "ENGAGE" data instead of the "LTA /#1 POS" date. See the revised version.View attachment 1502

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).
 
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.
 
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
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.
 
when I input.. say Mar-13 on the LTA column for row# 10. The output skips Jan / Feb 2014 I must be misunderstanding you, because it seems to work fine on my copy. Can you upload a file, and put in some callout shapes with text or textboxes with arrows outlining exactly what the issue is?
 
Jeff,

Please see the attached SS and excel sheet. The highlighted cells in "Asia Pacific" are the values that are missing when it's suppose to show.

Thanks as always, View attachment Build Test V6 Errors.xlsb

HanError SS 1.PNG

when I input.. say Mar-13 on the LTA column for row# 10. The output skips Jan / Feb 2014 I must be misunderstanding you, because it seems to work fine on my copy. Can you upload a file, and put in some callout shapes with text or textboxes with arrows outlining exactly what the issue is?
 
I suspect this is either a date format issue or a country-specific version issue. Down here in New Zealand we use DD/MM/YY and everything displays just fine. What date format do you use, and what country are you in?

See attached. Capture.PNG

Capture.PNG
 
I'm here in US California. It's MM/DD/YR for us I believe. But how do I tell if my excel options is using which specific date format. In the format cell inside "custom" it shows mmm-yy.

Han

I suspect this is either a date format issue or a country-specific version issue. Down here in New Zealand we use DD/MM/YY and everything displays just fine. What date format do you use, and what country are you in?

See attached. View attachment 1547

View attachment 1547
 
I might have to get one of the US-based moderators to take a look, because they will be able to better see where this is falling over.

Quick question: if you type =DATEVALUE(AF8) into the sheet, what value does it give you?
 
it outputs "41288"

I might have to get one of the US-based moderators to take a look, because they will be able to better see where this is falling over.

Quick question: if you type =DATEVALUE(AF8) into the sheet, what value does it give you?
 
Ahhh. That corresponds to 14 January 2013. Mine returns 41640, which corresponds to 1 January 2014.

Try this: reenter the dates along the top, one at a time. Don't do one then drag them across. Instead, do each one individually. So go to say AF8 and type in 01/01/2014
 
It seems to be doing the same thing..

Ahhh. That corresponds to 14 January 2013. Mine returns 41640, which corresponds to 1 January 2014.

Try this: reenter the dates along the top, one at a time. Don't do one then drag them across. Instead, do each one individually. So go to say AF8 and type in 01/01/2014
 
Weird. what does =DATEVALUE(AF8) show after you manually reenter the date 1/1/2014 into AF8? And what does VALUE(AF8) show?
 
Also, see if the issue is still happening on the attached file. View attachment Build Test V7 - dates in helper cells.xlsb.

Apparently some weird stuff can happen to formulas where the column headers contain dates. So I put a 'helper' column in above the column headers, to see if it makes any difference.

EDIT: I just changed my date settings to US format, and this approach seems to work fine.

The issue is that for some obscure reason, if you enter say 1/1/2013 into a table column header, Excel stores it as a string, not a date. If you then tell excel "Apply the date format of MMM-YY to this", then Excel displays 'Jan-13' (which is what we want) but when it converts the string to a date using any of the date-related functions, Excel thinks that this number for some reason relates to the 13th of January 2013.

Way around it is just to have some helper cells one row above. Because they are not table column headers, they are stored as dates, not strings.

Note that you don't need these helper cells in the summary tab, because that summary table doesn't contain any formulas. Also note that no-one will see this extra helper row, because the month-by-month breakdown is only required to be visible in the summary sheet...i.e. you should hide columns T onwards in the data input sheets, like in this version. View attachment Build Test V7 - dates in helper cells_v2.xlsb
 
Last edited:
Back
Top