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