Hi,
I have a workbook where I track expenses against a number of allocated codes. It is a date progressive worksheet, and I need a way to determine a YTD total based on how I filter the list. I have attached a scaled down version of the model. If you look at the 'A&P Tracking' tab you will see a range of expenses entered in by date, allocated to a brand, assigned to a cost code and subcategory, and allocated to a supplier and a cost entered for that expense.
On the 'Summary' tab I have a list of the brands, and the expense categories along the top. Codes relating to these categories are below this summary table. I am having trouble finding a way to do an index match on column D of the 'A&P Tracking' tab to identify the brand in the Summary table, and then to look up the respective expense category code / name from column E of F of the 'A&P' tab, and then to do a SUMIF of some sort on column K of this tab to give a YTD total.
As an example, if I filter the 'A&P' tab on column D to show 'Brand A' and on column E to show 'Markdowns' the filtered list will return a value at the top of column K of $98,862. At the moment if I want to find out what the total expense for Markdown for Brand A is for the full year, I have to do this manually in this manner. Similarly if I filter the list by date to see only July, I can do so however have to do it manually.
What I am therefore trying to achieve is on the Summary tab, to have a formula that in this case would look up Brand A on the 'A&P' tab, cross reference with Markdown, and return a total value in that cell. If I further filter the 'A&P' tab to show say selected months, I would like to also be able to build in the option of the formula being able to look at the months selected and return that value.
Trust that makes sense????
Thanks in advance
Bookmarks