I've been searching around and I am struggling to find why my formulas are not working.
Before I post my formulas I need to explain what I'm trying to do and it is a bit complicated (At least, this is the only way I can think of doing it).
I need to create a spreadsheet on my division's budget (sorted by category), currently when I download the income/expense from our Finance system it just pumps out rows of data (RAW DATA), sorted by date.
Goal 1 - Have the exported RAW DATA automatically sort into categories which then a table shows clearly how much has been spent in each category ACHIEVED
Goal 2 - Have the exported RAW DATA sort into categories and ONLY show specific months based on the user's selection STUCK
RAW DATA EXPORT EXAMPLE:
Date | Description of Expense | Amount | Codes Jan-15 | Workshop for employee | 100000 | WRKS Jan-15 | Workshop for employee | 100000 | WRKS Feb-15 | Sponsors for employee | 200000 | SPON Feb-15 | Sponsors for employee | 100000 | SPON Feb-15 | Workshop for employee | 300000 | WRKS Mar-15 | Sponsors for employee | 100000 | SPON
If we use the above table as an example of the RAW DATA (Named "Data2015") I get when I export from our Finance systems (Which I converted to a table within Excel).
What I did to achieve Goal 1 was to create a new table (Named "Categories") with the descriptions of the categories.
Categories | Codes Workshop | WRKS Sponsors | SPON
Then created another table (Named "Budget") like below:
Categories | Codes | Amount Workshop | WRKS | 500000 Sponsors | SPON | 400000
The formula I used for the Amount column on the Budget table is
So this formula helps me achieve Goal 1.
My attempt at achieving Goal 2, I tried the following.
Created a table to capture the months the user wants to see:
Month | X | Background Jan-15 | x | 1/01/2015 Feb-15 | x | 1/02/2015 Mar-15 | x | 1/03/2015
The user puts an "x" in the X column if they would like to see the expenses for that particular month.
I used this formula in the Background column:
I want to then further filter the months into categories like before.
So similar to the Categories table above, but for specific months based on what the user wants to see.
I managed to get it sort of working but only for 1 line, not the whole table.
I would like you to review my formula and let me know what I may be doing that is incorrect or maybe even my logic in achieving goal 2 is incorrect. Your insight will be very helpful and I apologise for the very confusing situation, I can't think of an easier way to explain.
I have created another spreadsheet that will mimic the Data2015 sheet with the following formula
=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))
Month_View! is where TABLE: Conditions is located and $D$5 is Jan-15, $D$6 is Feb-15 and so on.
This formula pretty much populates the table if it detects that A5 (Column where the date is) is the same as the TABLE: Data2015's Date column (on the same row, hence the @).
Up to this point it's all good, it works, so long as I have "Jan-15" or "Feb-15" in cell A5. To populate this cell based on the user's preference I used the following formula (which doesn't produce the result I want):
So to me, that formula is meant to do the following:
IF the Date in the Data2015 table matches one of the dates/values (in the format of "d/mm/yyyy") in Condition's column named "Background" (Which will only show if the user put an "x" in the month they want to see) then fill this cell with the value from Data2015's Date column.
If the cell then populates with "Jan-15" or "1/01/2015" then the rest of the table will populate which I can then use to then further filter down by category with the formula I used to achieve Goal 1.
But the cell is not populating with "Jan-15" or "1/01/2015" instead it's showing blank or #VALUE!
It seems to be referencing the Condition table with @ as well which I don't want.
As confusing as it is... If you can understand what I'm trying to achieve, any insight or discussion will help. Maybe my brain is just too overloaded at this stage.