Hi All,
Probably the hardest thing I've had to do in excel and I need someone with a better skill set than I currently have please!
I've put together a pretty big spreadsheet which draws data from ODBC links to a database.
Part of the report shows how much has gone out over 12, 6, and 3 months using sumifs and eomonths formulas.
However, we now also need to add columns to show how much is due to be sold in this current month which I can do with
=SUMIFS('SO DEMAND'!I8:I16070,'SO DEMAND'!A8:A16070,A6,'SO DEMAND'!D4:D16066,"Nov-17",'SO DEMAND'!G8:G16070,"<>Complete",'SO DEMAND'!F8:F16070,"<>Cancelled")
But the bits im struggling with is I've been asked to add 3 buttons at the top of the spreadsheet, 3 months, 6 months and 12 months.
If you click 3 months then the spreadsheet will change the column next to current month sold to 3 month average. (Everything in last 3 months sold / 3)
If you click 6 month then the spreadsheet will change the formula so it looks at the average of 6 months sold.
Then if you click 12 the formula changes so it will show an average of last 12 months.
I will then do a conditional format to show which one is higher between current month sales and the 3, or 6, or 12 months column, depending on what button is pressed.
Is this possible and if so how?
Huge thanks in advance!
Probably the hardest thing I've had to do in excel and I need someone with a better skill set than I currently have please!
I've put together a pretty big spreadsheet which draws data from ODBC links to a database.
Part of the report shows how much has gone out over 12, 6, and 3 months using sumifs and eomonths formulas.
However, we now also need to add columns to show how much is due to be sold in this current month which I can do with
=SUMIFS('SO DEMAND'!I8:I16070,'SO DEMAND'!A8:A16070,A6,'SO DEMAND'!D4:D16066,"Nov-17",'SO DEMAND'!G8:G16070,"<>Complete",'SO DEMAND'!F8:F16070,"<>Cancelled")
But the bits im struggling with is I've been asked to add 3 buttons at the top of the spreadsheet, 3 months, 6 months and 12 months.
If you click 3 months then the spreadsheet will change the column next to current month sold to 3 month average. (Everything in last 3 months sold / 3)
If you click 6 month then the spreadsheet will change the formula so it looks at the average of 6 months sold.
Then if you click 12 the formula changes so it will show an average of last 12 months.
I will then do a conditional format to show which one is higher between current month sales and the 3, or 6, or 12 months column, depending on what button is pressed.
Is this possible and if so how?
Huge thanks in advance!
