Hi Guys,
I'm looking for some help with formula's please, We've got a spreadsheet which has our 12 top suppliers at the top and the months below and we put the figures per supplier per month, spreadsheet attached.
I would like the spreadsheet to be able to show us the following
1) Total for the past 12months from today's date (ie whenever we open the spreadsheet it updates based on the date)
2) Yearly Figure for the supplier (Jan to Dec)
4) Would like this to be a rolling so we can keep adding to the bottom of the sheet.
5) Would like to be able to see the best month we've had with that supplier.
6) Grand total for all supplier from the start
Thanks in advance for any suggestions and support that you can provide and hope you all have a nice break and a good new year.
James
<tbody>
</tbody>
I'm looking for some help with formula's please, We've got a spreadsheet which has our 12 top suppliers at the top and the months below and we put the figures per supplier per month, spreadsheet attached.
I would like the spreadsheet to be able to show us the following
1) Total for the past 12months from today's date (ie whenever we open the spreadsheet it updates based on the date)
2) Yearly Figure for the supplier (Jan to Dec)
4) Would like this to be a rolling so we can keep adding to the bottom of the sheet.
5) Would like to be able to see the best month we've had with that supplier.
6) Grand total for all supplier from the start
Thanks in advance for any suggestions and support that you can provide and hope you all have a nice break and a good new year.
James
| NETT SPEND | ||||||||||||||
| Supplier 1 | Supplier 2 | Supplier 3 | Supplier 4 | Supplier 5 | Supplier 6 | Supplier 7 | Supplier 8 | Supplier 9 | Supplier 10 | Supplier 11 | Supplier 12 | Monthly Totals | ||
| CREDIT | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | 100 | ||
| MONTH | ||||||||||||||
| Apr-14 | £50.00 | £20.00 | £30.00 | £22.00 | £33.50 | £20.00 | £50.00 | £20.00 | £30.00 | £22.00 | £33.50 | £20.00 | £351.00 | |
| May-14 | £60.00 | £25.00 | £35.00 | £23.00 | £34.50 | £21.00 | £60.00 | £25.00 | £35.00 | £23.00 | £34.50 | £21.00 | £397.00 | |
| Jun-14 | £70.00 | £30.00 | £40.00 | £24.00 | £35.50 | £22.00 | £70.00 | £30.00 | £40.00 | £24.00 | £35.50 | £22.00 | £443.00 | |
| Jul-14 | £80.00 | £35.00 | £45.00 | £25.00 | £36.50 | £23.00 | £80.00 | £35.00 | £45.00 | £25.00 | £36.50 | £23.00 | £489.00 | |
| Aug-14 | £90.00 | £40.00 | £50.00 | £26.00 | £37.50 | £24.00 | £90.00 | £40.00 | £50.00 | £26.00 | £37.50 | £24.00 | £535.00 | |
| Sep-14 | £100.00 | £45.00 | £55.00 | £27.00 | £38.50 | £25.00 | £100.00 | £45.00 | £55.00 | £27.00 | £38.50 | £25.00 | £581.00 | |
| Oct-14 | £110.00 | £50.00 | £60.00 | £28.00 | £39.50 | £26.00 | £110.00 | £50.00 | £60.00 | £28.00 | £39.50 | £26.00 | £627.00 | |
| Nov-14 | £120.00 | £55.00 | £65.00 | £29.00 | £40.50 | £27.00 | £120.00 | £55.00 | £65.00 | £29.00 | £40.50 | £27.00 | £673.00 | |
| Dec-14 | £130.00 | £60.00 | £70.00 | £30.00 | £41.50 | £28.00 | £130.00 | £60.00 | £70.00 | £30.00 | £41.50 | £28.00 | £719.00 | |
| Jan-15 | £140.00 | £65.00 | £75.00 | £31.00 | £42.50 | £29.00 | £140.00 | £65.00 | £75.00 | £31.00 | £42.50 | £29.00 | £765.00 | |
| Feb-15 | £150.00 | £70.00 | £80.00 | £32.00 | £43.50 | £30.00 | £150.00 | £70.00 | £80.00 | £32.00 | £43.50 | £30.00 | £811.00 | |
| Mar-15 | £160.00 | £75.00 | £85.00 | £33.00 | £44.50 | £31.00 | £160.00 | £75.00 | £85.00 | £33.00 | £44.50 | £31.00 | £857.00 | |
| Apr-15 | £170.00 | £80.00 | £90.00 | £34.00 | £45.50 | £32.00 | £170.00 | £80.00 | £90.00 | £34.00 | £45.50 | £32.00 | £903.00 | |
| May-15 | £180.00 | £85.00 | £95.00 | £35.00 | £46.50 | £33.00 | £180.00 | £85.00 | £95.00 | £35.00 | £46.50 | £33.00 | £949.00 | |
| Jun-15 | £190.00 | £90.00 | £100.00 | £36.00 | £47.50 | £34.00 | £190.00 | £90.00 | £100.00 | £36.00 | £47.50 | £34.00 | £995.00 | |
| Jul-15 | £200.00 | £95.00 | £105.00 | £37.00 | £48.50 | £35.00 | £200.00 | £95.00 | £105.00 | £37.00 | £48.50 | £35.00 | £1,041.00 | |
| Aug-15 | £210.00 | £100.00 | £110.00 | £38.00 | £49.50 | £36.00 | £210.00 | £100.00 | £110.00 | £38.00 | £49.50 | £36.00 | £1,087.00 | |
| Sep-15 | £220.00 | £105.00 | £115.00 | £39.00 | £50.50 | £37.00 | £220.00 | £105.00 | £115.00 | £39.00 | £50.50 | £37.00 | £1,133.00 | |
| Oct-15 | £230.00 | £110.00 | £120.00 | £40.00 | £51.50 | £38.00 | £230.00 | £110.00 | £120.00 | £40.00 | £51.50 | £38.00 | £1,179.00 | |
| Nov-15 | £240.00 | £115.00 | £125.00 | £41.00 | £52.50 | £39.00 | £240.00 | £115.00 | £125.00 | £41.00 | £52.50 | £39.00 | £1,225.00 | |
| Dec-15 | £250.00 | £120.00 | £130.00 | £42.00 | £53.50 | £40.00 | £250.00 | £120.00 | £130.00 | £42.00 | £53.50 | £40.00 | £1,271.00 | |
| Jan-16 | £260.00 | £125.00 | £135.00 | £43.00 | £54.50 | £41.00 | £260.00 | £125.00 | £135.00 | £43.00 | £54.50 | £41.00 | £1,317.00 | |
| Feb-16 | £270.00 | £130.00 | £140.00 | £44.00 | £55.50 | £42.00 | £270.00 | £130.00 | £140.00 | £44.00 | £55.50 | £42.00 | £1,363.00 | |
| Mar-16 | £280.00 | £135.00 | £145.00 | £45.00 | £56.50 | £43.00 | £280.00 | £135.00 | £145.00 | £45.00 | £56.50 | £43.00 | £1,409.00 | |
| Apr-16 | £290.00 | £140.00 | £150.00 | £46.00 | £57.50 | £44.00 | £290.00 | £140.00 | £150.00 | £46.00 | £57.50 | £44.00 | £1,455.00 | |
| May-16 | £300.00 | £145.00 | £155.00 | £47.00 | £58.50 | £45.00 | £300.00 | £145.00 | £155.00 | £47.00 | £58.50 | £45.00 | £1,501.00 | |
| Jun-16 | £310.00 | £150.00 | £160.00 | £48.00 | £59.50 | £46.00 | £310.00 | £150.00 | £160.00 | £48.00 | £59.50 | £46.00 | £1,547.00 | |
| Jul-16 | £320.00 | £155.00 | £165.00 | £49.00 | £60.50 | £47.00 | £320.00 | £155.00 | £165.00 | £49.00 | £60.50 | £47.00 | £1,593.00 | |
| Aug-16 | £330.00 | £160.00 | £170.00 | £50.00 | £61.50 | £48.00 | £330.00 | £160.00 | £170.00 | £50.00 | £61.50 | £48.00 | £1,639.00 | |
| Sep-16 | £0.00 | |||||||||||||
| Oct-16 | £0.00 | |||||||||||||
| Nov-16 | £0.00 | |||||||||||||
| Dec-16 | £0.00 | |||||||||||||
| Jan-17 | £0.00 | |||||||||||||
| Feb-17 | £0.00 | |||||||||||||
| Rolling Total | ||||||||||||||
| Grand Total | ||||||||||||||
| Best Month |
<tbody>
</tbody>
