Hi all - hope you can help me.
Problem #1
Populating a dynamic tab from an existing tab based on values
Please see the attached example file.
The spreadsheet is all about budget, actuals and variance year to date (YTD).
It has x5 tabs that work: Budget, Actual, PriorYear, DropDowns and Year to Date (YTD). The 6th tab (Variance from YTD) is what I’m trying to get working but don’t know how to go about it:
The YTD tab performs nested IFs on certain fields to determine how the Actuals data compares to the Budget data, and also how the %Budget used compares against %year elapsed. The resulting values are conditionally formatted to (supposedly!) make it easy to read/consume.
However, as the real YTD sheet is quite long, and because a lot of the data returns a “OK” after the nested IFs (and hence don’t need explanation), I want to produce another tab which will populate itself from the YTD tab but only by listing those items which need attention/focus (i.e. the NOT-“OK” data).
Additionally, I want the new tab to include a column for “Comments/Explanation” so that the person generating/circulating the report can add their text to explain the reason for the variance.
[ASIDE: PROBLEM - as the month selected changes, so will the YTD data being populated – is there any way to persist the Comments/Explanation entries made against e.g. YTD to Dec versus YTD to Sep? (imagine this is too complex but thought I’d ask anyway
) :END ASIDE]
The logic / criteria for what must be included in the new tab is here:
For INCOME items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE income received than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Income items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value DOES=“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. % of the income budget has been received at this point in the year that is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Income items that satisfy criteria for the selected month are listed
Always include the line showing Total Income
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
For SALARY items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE has been spent than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Salary items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value =“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. the %Budget spent at this point in the year is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Salary items that satisfy criteria for the selected month are listed
Always include the line showing Total Salaries
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
For OVERHEAD items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE has been spent than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Overhead items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value =“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. the %Budget spent at this point in the year is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Overhead items that satisfy criteria for the selected month are listed
Always include the line showing Total Overheads
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
Always include the lines for:
Total Expenditure
Surplus/Deficit
(NB these values to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
Problem #2
Adjusting the nested IFs to change depending on the Items being looked at.
Right now, if you look at the nested IFs in columns J and N they are a pretty simple “one size fits all” approach (i.e. my limit!). However, I’m interested in a more granular approach to this. I could of course handcode specific values into each specific Item’s formula but don’t really want to do that (as may want/need to change sensitivity of items quickly and easily).
A more elegant solution would be that I could put in permitted variation amounts for each Item into a table, such that when the J and N columns’ formulas do their stuff they would include the right “bounds” in that table for each Item and then put in the result accordingly. I’ve included a tab showing what such a table might look like (see Permitted Variation table tab nb data is dummy nonsense). All ideas for how I can use this most welcome.
As always, I really appreciate any time you can give to figuring this out / helping me out.
Huge thanks in anticipation.
Best wishes
Olly
Problem #1
Populating a dynamic tab from an existing tab based on values
Please see the attached example file.
The spreadsheet is all about budget, actuals and variance year to date (YTD).
It has x5 tabs that work: Budget, Actual, PriorYear, DropDowns and Year to Date (YTD). The 6th tab (Variance from YTD) is what I’m trying to get working but don’t know how to go about it:
The YTD tab performs nested IFs on certain fields to determine how the Actuals data compares to the Budget data, and also how the %Budget used compares against %year elapsed. The resulting values are conditionally formatted to (supposedly!) make it easy to read/consume.
However, as the real YTD sheet is quite long, and because a lot of the data returns a “OK” after the nested IFs (and hence don’t need explanation), I want to produce another tab which will populate itself from the YTD tab but only by listing those items which need attention/focus (i.e. the NOT-“OK” data).
Additionally, I want the new tab to include a column for “Comments/Explanation” so that the person generating/circulating the report can add their text to explain the reason for the variance.
[ASIDE: PROBLEM - as the month selected changes, so will the YTD data being populated – is there any way to persist the Comments/Explanation entries made against e.g. YTD to Dec versus YTD to Sep? (imagine this is too complex but thought I’d ask anyway
) :END ASIDE] The logic / criteria for what must be included in the new tab is here:
For INCOME items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE income received than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Income items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value DOES=“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. % of the income budget has been received at this point in the year that is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Income items that satisfy criteria for the selected month are listed
Always include the line showing Total Income
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
For SALARY items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE has been spent than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Salary items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value =“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. the %Budget spent at this point in the year is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Salary items that satisfy criteria for the selected month are listed
Always include the line showing Total Salaries
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
For OVERHEAD items …
IF Variance Status (column E) value DOES NOT=“OK” (i.e. LESS or MORE has been spent than expected at this point in the year), populate the values under the column headings listed; continue doing so until all Overhead items that satisfy criteria for the selected month are listed
OR
IF Variance Status (column E) value =“OK“ BUT -vs- % year elapsed (column G) value DOES NOT=“OK”, (i.e. the %Budget spent at this point in the year is ABOVE/BELOW the %year elapsed), populate the values under the column headings listed; continue doing so until all Overhead items that satisfy criteria for the selected month are listed
Always include the line showing Total Overheads
(NB this value to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
Always include the lines for:
Total Expenditure
Surplus/Deficit
(NB these values to be for the year to date copied from the YTD sheet rather than just for those selectively listed items that match the criteria [provides context overview for how whole year to date is doing relative to those values being focused on])
Problem #2
Adjusting the nested IFs to change depending on the Items being looked at.
Right now, if you look at the nested IFs in columns J and N they are a pretty simple “one size fits all” approach (i.e. my limit!). However, I’m interested in a more granular approach to this. I could of course handcode specific values into each specific Item’s formula but don’t really want to do that (as may want/need to change sensitivity of items quickly and easily).
A more elegant solution would be that I could put in permitted variation amounts for each Item into a table, such that when the J and N columns’ formulas do their stuff they would include the right “bounds” in that table for each Item and then put in the result accordingly. I’ve included a tab showing what such a table might look like (see Permitted Variation table tab nb data is dummy nonsense). All ideas for how I can use this most welcome.
As always, I really appreciate any time you can give to figuring this out / helping me out.
Huge thanks in anticipation.
Best wishes
Olly