Hi Folks,
I basically have some Sales Forecast data by month spanning over a couple of years with 3 sections, Volume, Value, Cost. I'm trying to put some checks in place to ensure the data they are inputting is logical, IE If there is a Sales Volume then there needs to be a corresponding Sales Value and Cost of Sales Value etc.
My initial thought was to use SUMIFS to bring back any Salve Value/ COS where the Volume was 0 or indeed blank. This is a good enough check but I wanted to make the formula dynamic so it finds the relevant SUM Range and corresponding Criteria Range depending on what Month I denote in another cell. The biggest issue i have is that the header title (month) isn't unique because it's essentially a sub heading (Volume) that spans across the 12 columns for the year in the Row above.
I have tried googling for hours but clearly other companies employ people who understand what a sales forecast should like lol Has anybody got any Ideas? I've Uploaded my mini test version image, the formula I'm trying to play with in Cell Q6 is =MATCH("Val"&O2,C4:M4&C5:M5,0).
Jodie
I basically have some Sales Forecast data by month spanning over a couple of years with 3 sections, Volume, Value, Cost. I'm trying to put some checks in place to ensure the data they are inputting is logical, IE If there is a Sales Volume then there needs to be a corresponding Sales Value and Cost of Sales Value etc.
My initial thought was to use SUMIFS to bring back any Salve Value/ COS where the Volume was 0 or indeed blank. This is a good enough check but I wanted to make the formula dynamic so it finds the relevant SUM Range and corresponding Criteria Range depending on what Month I denote in another cell. The biggest issue i have is that the header title (month) isn't unique because it's essentially a sub heading (Volume) that spans across the 12 columns for the year in the Row above.
I have tried googling for hours but clearly other companies employ people who understand what a sales forecast should like lol Has anybody got any Ideas? I've Uploaded my mini test version image, the formula I'm trying to play with in Cell Q6 is =MATCH("Val"&O2,C4:M4&C5:M5,0).
Jodie
