Filter by month on separate tab?

[Nelly3007]

Hello All,

We are trying to monitor spend per month and year to date and I was hoping to create some sort of dashboard on a separate tab to the data but without the dashboard being overloaded with information.

Screenshots attached show the main spreadsheet with all our info on and then a potential dashboard. Now, the dashboard could be nice and simple like that if we were just monitoring all spend through out the year but is there a way to be able to add onto that dashboard and filter each cost centre and GL code so you can just see one months spend at a time? And can we also keep the option to view year to date spend as well as monthly?

Many Thanks,

 

[HighAndWilder]

Hello All,

We are trying to monitor spend per month and year to date and I was hoping to create some sort of dashboard on a separate tab to the data but without the dashboard being overloaded with information.

Screenshots attached show the main spreadsheet with all our info on and then a potential dashboard. Now, the dashboard could be nice and simple like that if we were just monitoring all spend through out the year but is there a way to be able to add onto that dashboard and filter each cost centre and GL code so you can just see one months spend at a time? And can we also keep the option to view year to date spend as well as monthly?

Many Thanks,

Anything is possible.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Can you please explain where the Monthly Budget, Current Spend and Remaining figures come from?

 

[Nelly3007]

Hi HighAndWilder,

Unfortunately I am unable to use XL2BB as my company IT have annoyingly blocked add-ins. I don't know if a live O365 document helps: Costs.xlsx (its just an example document)

The monthly budget is not on the spreadsheet currently but I can add it on if needs be, or we can just enter it manually on the 'Dashboard' tab in C3, C12 etc.
The 'Current spend' would need to come from Column J on the Orders tab and then on the dashboard, we would need the box for cost centre E81000 to only show things are allocated to E81000 in column M, same for cost centre E82000 etc
The Remaining spend would then just be Budget minus Current spend.

Hope that makes sense

Many Thanks,

 

[HighAndWilder]

Hi HighAndWilder,

Unfortunately I am unable to use XL2BB as my company IT have annoyingly blocked add-ins. I don't know if a live O365 document helps: Costs.xlsx (its just an example document)

The monthly budget is not on the spreadsheet currently but I can add it on if needs be, or we can just enter it manually on the 'Dashboard' tab in C3, C12 etc.
The 'Current spend' would need to come from Column J on the Orders tab and then on the dashboard, we would need the box for cost centre E81000 to only show things are allocated to E81000 in column M, same for cost centre E82000 etc
The Remaining spend would then just be Budget minus Current spend.

Hope that makes sense

Many Thanks,

Makes sense.
Can the Orders data be in a table?
Could the Budget data be in a table in another worksheet as below?

Filter by month on separate tab.xlsm
	A	B	C	D
1	Date	Cost Code	GL Code	Budget
2	01/01/2024	E81000	123	£2,817
3	01/01/2024	E81000	125	£5,137
4	01/01/2024	E82000	123	£6,787
5	01/02/2024	E83000	124	£8,749
6	01/02/2024	E84000	124	£8,947
Budgets

 

[Nelly3007]

Makes sense.
Can the Orders data be in a table?
Could the Budget data be in a table in another worksheet as below?

Filter by month on separate tab.xlsm
	A	B	C	D
1	Date	Cost Code	GL Code	Budget
2	01/01/2024	E81000	123	£2,817
3	01/01/2024	E81000	125	£5,137
4	01/01/2024	E82000	123	£6,787
5	01/02/2024	E83000	124	£8,749
6	01/02/2024	E84000	124	£8,947
Budgets

Good Mornng,

Yes and Yes I don't see any reason why the orders cant be in a table