I have been racking my brain and spent weeks now trying to build functionality into an excel project cost estimation tool that will allow the user to create their own 'view' of a table based upon parameters from checkboxes in a user form. Apologies for my lengthy and detailed description. The table is built with the following heirarchy structure:
each of the above four Period groups have the following sub-groups
each of those are broken down further
and depending on which category the effort or cost is related to additional SUMIF calculated columns for:
You can see that my spreadsheet has well over 600 columns and I need a way in which the user can select (by checkboxes) exactly what columns he needs to see whilst filtering all other out.
I have created a userform with the following label checkboxes:
PLANNED
ACTUALS (either Planned, Actual, or both must be selected)
EFFORT
COSTS (either Effort, Costs or both must be selected)
CAPEX COSTS
OPEX COSTS (Capex & Opex are only represented across Project Stages)
INPUT ONLY
PRIOR YEARS
CURRENT YEAR
NEXT YEAR
FUTURE YEARS
ALL PERIODS (at least one of the four periods must be selected, default is CURRENT YEAR)
BY STAGE
BY MONTH (either by Stage, by Month or both must be selected)
ALL SUPPORT
PMO SUPPORT
T2S SUPPORT
OTHER SUPPORT
ALL EXPENSES
INFRASTRUCTURE EXPENSES
MATERIALS EXPENSES
OTHER EXPENSES
TRAVEL EXPENSES
Lastly, I have ensured that all column headers are unique and are consistently titled to include each of the above keywords. Here are a few example header titles:
STAGE 0 PLANNED COSTS INPUT PRIOR YEARS
STAGE 1 ACTUAL COSTS PRIOR YEARS
STAGE 2 PLANNED EFFORT CURRENT YEAR
STAGE 3 ACTUAL EFFORT CURRENT YEAR
STAGE 2 PLANNED COSTS CURRENT YEAR
STAGE 3 ACTUAL COSTS NEXT YEAR
STAGE 4 PLANNED COSTS NEXT YEAR
STAGE 5 PLANNED COSTS FUTURE YEARS (future years have no actuals columns)
APR PLANNED EFFORT INPUT CURRENT YEAR
JAN ACTUAL COSTS INPUT NEXT YEAR
TRAVEL EXPENSES PLANNED COSTS FUTURE YEARS
PROJECT TEAM ACTUAL EFFORT PRIOR YEARS
PMO SUPPORT PLANNED EFFORT CURRENT YEAR
PMO SUPPORT ACTUAL EFFORT CURRENT YEAR
I've looked at several possible solutions in vba, like building an array string, using FIND/SEARCH, instr, but can't seem to get anything to do what I need it to do. I am happy to share the tool on request as I realise my query is complex and my explanation above possibly too lengthy and confusing.
I truly would appreciate greatly the advice or guidance that any EXCEL/VBA genius could provide!
Stephen (swhgraham)
- Prior Years
- Current Year
- Next Year
- Future Years
each of the above four Period groups have the following sub-groups
- Planned Effort (Manual Input columns)
- Actual Effort (Manual Input columns)
- Planned Costs (Calculated columns)
- Actual Costs (Manual Input columns)
- Capex Costs (Calculated columns)
- Opex Costs (Calculated columns)
each of those are broken down further
- by Project Stages (0-6)
- by Months (Jan-Dec) ....for Current and Next Year only (Prior and Future are represented only by Project Stages)
- Project Stage Total
- Year Total
- YTD Total
and depending on which category the effort or cost is related to additional SUMIF calculated columns for:
- Project Team
- Business Team
- PMO Support
- T2S Support
- Other Support
- Total Support
- Infrastructure
- Materials
- Other Expenses
- Travel Expenses
- Total Expenses (total of Infrastructure, Materials, Other and Travel and only have Cost columsn - no effort columns)
You can see that my spreadsheet has well over 600 columns and I need a way in which the user can select (by checkboxes) exactly what columns he needs to see whilst filtering all other out.
I have created a userform with the following label checkboxes:
PLANNED
ACTUALS (either Planned, Actual, or both must be selected)
EFFORT
COSTS (either Effort, Costs or both must be selected)
CAPEX COSTS
OPEX COSTS (Capex & Opex are only represented across Project Stages)
INPUT ONLY
PRIOR YEARS
CURRENT YEAR
NEXT YEAR
FUTURE YEARS
ALL PERIODS (at least one of the four periods must be selected, default is CURRENT YEAR)
BY STAGE
BY MONTH (either by Stage, by Month or both must be selected)
ALL SUPPORT
PMO SUPPORT
T2S SUPPORT
OTHER SUPPORT
ALL EXPENSES
INFRASTRUCTURE EXPENSES
MATERIALS EXPENSES
OTHER EXPENSES
TRAVEL EXPENSES
Lastly, I have ensured that all column headers are unique and are consistently titled to include each of the above keywords. Here are a few example header titles:
STAGE 0 PLANNED COSTS INPUT PRIOR YEARS
STAGE 1 ACTUAL COSTS PRIOR YEARS
STAGE 2 PLANNED EFFORT CURRENT YEAR
STAGE 3 ACTUAL EFFORT CURRENT YEAR
STAGE 2 PLANNED COSTS CURRENT YEAR
STAGE 3 ACTUAL COSTS NEXT YEAR
STAGE 4 PLANNED COSTS NEXT YEAR
STAGE 5 PLANNED COSTS FUTURE YEARS (future years have no actuals columns)
APR PLANNED EFFORT INPUT CURRENT YEAR
JAN ACTUAL COSTS INPUT NEXT YEAR
TRAVEL EXPENSES PLANNED COSTS FUTURE YEARS
PROJECT TEAM ACTUAL EFFORT PRIOR YEARS
PMO SUPPORT PLANNED EFFORT CURRENT YEAR
PMO SUPPORT ACTUAL EFFORT CURRENT YEAR
I've looked at several possible solutions in vba, like building an array string, using FIND/SEARCH, instr, but can't seem to get anything to do what I need it to do. I am happy to share the tool on request as I realise my query is complex and my explanation above possibly too lengthy and confusing.
I truly would appreciate greatly the advice or guidance that any EXCEL/VBA genius could provide!
Stephen (swhgraham)
