Looking for an experienced individual that can help refine a few kinks in a VBA script to have this document function effectively. Instead of diving into the particularities of the script, I will outline the tool here and the outputs that are desired.
I am trying to put together a greenhouse gas emissions (GHG) inventory that outlines the GHG emissions associated with the energy consumption from a commercial building both on the building owner and tenant levels. In the attached Excel template in the "Building Energy Info." tab, building owners and tenants can input their name, base year, GHG normalization factor, as well as GHG emissions percentage reduction goal. The intent of this document is that any user can input their information and then have the charts in the "Summary" tab update automatically/dynamically. This will be a public facing document for multiple commercial buildings to use, so it needs to be full-proof.
Users can input their energy data into the “Energy Consumption” tab and data for their normalization factor in the following three “Normalization Factor” tables. Each tenant can have a different normalization factor. To get to GHG emissions intensity, the information in the “Energy Consumption” table is divided by the selected normalization factor for the owner and each tenant and pulled into the “Greenhouse Gas Emissions Intensity” table starting on Row 133. Waste information can be input into the “Building Waste Info.” tab is also added into “Greenhouse Gas Emissions Intensity” table. The first 6 charts in the "Summary" tab pull from this table. In the “Summary” tab, the “123 Main Street” graph pulls from column D and columns K - O and the “Tenants 1-5 Charts” pull from each of the respective tenant columns. The last chart—“Whole Building Weighted GHG Emissions Intensity”—pulls from the “Weighted Greenhouse Gas Emissions Intensity – FOR CHART” table starting on row 165. The way that this chart is structured is that it aligns all of the tenants with the “Landlord Controlled” column in order to compare greenhouse gas emissions intensities. This information then pulls into the last chart on the “Summary” page.
A couple of things to note:
1) All of the tenants do not have to fill out energy information – in some cases these fields might be left blank. This chart will always be filled out from left to right based on participating tenants. For example, no tenant will ever fill out the column where “Tenant 5” is before “Tenant 1”.
2) For tenants that do fill out energy information, they will always input their energy information up until 2015. For example, no tenant will fill out their energy information from 2007 – 2014.
The VBA script will need to be refined to structured so that the front-end facing portion of the document operates in the following way:
1) All tables in the “Summary” tab should update automatically update and fluctuate based on the inputs into the “Building Energy Info.” tab. For example, if a tenant inputs energy information for “Tenant 1” between 2009 and 2015, the bars on the Tenant 1 table in the “Summary” tab should only show 2009 to 2015.
2) The greenhouse gas reduction target line should fluctuate on each of the charts based on the selected reductions made in G24:H34 for each respective party. For example, tenant 4 could change their greenhouse gas reduction target line in Cell G32 and they would see the line fluctuate on the “Tenant 4” graph in the “Summary” tab.
3) In the “Whole Building Weighted GHG Emissions Intensity” chart, the names of the entered in C15:C22 should pull through and be represented in the legend.
I have filled out this chart with some dummy data. As per the above requirements for the way that this spreadsheet should be structured, I still cannot get the document to function properly. As you will see, a few issues below:
1) The first graph at the top should not have the year 2010 displayed on the x-axis.
2) Tenant 1 graph in “Summary” tab should not have 2012 and 2013 displayed on the x-axis.
3) Tenant 2 graph should have 2011 – 2015 displayed.
4) Tenant 3 graph should have 2010 – 2015 displayed and GHG intensity goal line based on 30% of their selected base year GHG intensity.
5) Tenant 4 graph should have 2010 – 2015 displayed and GHG intensity goal line based on 30% of their selected base year GHG intensity.
6) Tenant 5 graph should have 2013 – 2015 displayed and a GHG intensity goal line based on 40% of their selected base year GHG intensity.
7) The whole building graph should have data only from 2011 – 2015 displayed and the tenants that have entered in data should be represented in the legend.
This is a short-term project which can hopefully be fixed by an individual that has an intimate knowledge of VBA. Once the script is refined the spreadsheet should be tested and quality controlled to see if different data inputs result in the desired outcome based on the criteria above.