How to display data with a filter?

[Nelly3007]

Hello All,

I have a spreadsheet with a record of orders and each order is allocated to a certain cost code (indicates which department is paying for the order) and a certain GL category (indicates which budget the money is coming from).

It also has a separate tab of all the budgets, so each cost code and GL code has a budget that can be spent each month.

I am looking to have a bit more visibility of what has been spent each month and what's left so was wondering if its possible to display the data from the Orders tab on a separate sheet and then filter by cost code, GL code and month, so for example, we can view how much has been spent on cost E81000 in R&M for the month of January. But then (and this is why I'm stuck) I'd also like to view how much is left in that budget, so view what's already been spent under E81000, R&M, January, and then also see how much is left from the budget (taken from the 'budget' tab), and also have the budget/what's left correspond to whatever filter we have on. So if we were to switch the filter to see what's been spent under E82000, Plant Hire, February and then the 'what's left' bit will update as well.

The 'month' will come from column C 'GR Month' and the amount spent will be column L 'Real spend'.

I hope that makes some sort of sense.....

I cannot upload a mini spreadsheet as my company IT has blocked the necessary app but I do have a test spreadsheet here: Costs Template.xlsx

This has got minimal data on it but we do actually have 12 cost codes which each have 12 GL categories so in total we've got 144 different budgets to monitor.

Thanks so much to anyone that can make sense of that and offer any help

 

[HongRu]

Try.
The first table shows the splent.

Costs Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Cost code	E81000
2	GL code	R&M	Plant hire	Other	Other 2	Other 3	other 4	Other 5	Other 6	Other 7	Other 8	other 9	Other 10
3	Jan	£1,600.00	£0.00	£600.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
4	Feb	£0.00	£0.00	£600.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
5	Mar	£600.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
6	Apr	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
7	May	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
8	Jun	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
9	Jul	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
10	Aug	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
11	Sep	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
12	Oct	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
13	Nov	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
14	Dec	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00	£0.00
Spent

Cell Formulas
Range	Formula
B3:M14	B3	=SUMIFS(Table2[Real cost],Table2[Cost Code],$B$1,Table2[GL Code],B$2,Table2[Month],$A3)

The 2nd table shows the left.

Costs Template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Cost code	E81000
2	GL code	R&M	Plant hire	Other	Other 2	Other 3	other 4	Other 5	Other 6	Other 7	Other 8	other 9	Other 10
3	Jan	£8,400.00	£25,000.00	£4,400.00	£2,000.00	0	0	0	0	0	0	0	0
4	Feb	£25,000.00	£2,000.00	£2,400.00	£4,000.00	0	0	0	0	0	0	0	0
5	Mar	£29,400.00	£30,000.00	£30,000.00	£30,000.00	0	0	0	0	0	0	0	0
6	Apr	0	0	0	0	0	0	0	0	0	0	0	0
7	May	0	0	0	0	0	0	0	0	0	0	0	0
8	Jun	0	0	0	0	0	0	0	0	0	0	0	0
9	Jul	0	0	0	0	0	0	0	0	0	0	0	0
10	Aug	0	0	0	0	0	0	0	0	0	0	0	0
11	Sep	0	0	0	0	0	0	0	0	0	0	0	0
12	Oct	0	0	0	0	0	0	0	0	0	0	0	0
13	Nov	0	0	0	0	0	0	0	0	0	0	0	0
14	Dec	0	0	0	0	0	0	0	0	0	0	0	0
Left

Cell Formulas
Range	Formula
B3:M14	B3	=Budgets!B3-Spent!B3

 

[HighAndWilder]

Hello All,

I have a spreadsheet with a record of orders and each order is allocated to a certain cost code (indicates which department is paying for the order) and a certain GL category (indicates which budget the money is coming from).

It also has a separate tab of all the budgets, so each cost code and GL code has a budget that can be spent each month.

I am looking to have a bit more visibility of what has been spent each month and what's left so was wondering if its possible to display the data from the Orders tab on a separate sheet and then filter by cost code, GL code and month, so for example, we can view how much has been spent on cost E81000 in R&M for the month of January. But then (and this is why I'm stuck) I'd also like to view how much is left in that budget, so view what's already been spent under E81000, R&M, January, and then also see how much is left from the budget (taken from the 'budget' tab), and also have the budget/what's left correspond to whatever filter we have on. So if we were to switch the filter to see what's been spent under E82000, Plant Hire, February and then the 'what's left' bit will update as well.

The 'month' will come from column C 'GR Month' and the amount spent will be column L 'Real spend'.

I hope that makes some sort of sense.....

I cannot upload a mini spreadsheet as my company IT has blocked the necessary app but I do have a test spreadsheet here: Costs Template.xlsx

This has got minimal data on it but we do actually have 12 cost codes which each have 12 GL categories so in total we've got 144 different budgets to monitor.

Thanks so much to anyone that can make sense of that and offer any help

Initially for the first objective.

I've put the data in a table called tblOrders on a sheet called Orders.

On a sheet called Report I have three drop down validation lists that enable you to display a filtered list of the orders table.

There are many tweaks that can be made to this but is this the sort of thing that you are after.

The lists on the right that form the data for the validation lists can be on another sheet.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Date raiised	GR Month	Company	Raised By	RETRO ORDERS	Req No	PO No	GR No	Quote number	Cost	Real cost	Cost Code	GL Code	Authorised By	Received	COMMENTS
2	03/01/2024		GH	Lee	NO		101	5051	1	£1,000.00	£1,000.00	E81000	R&M	Lee	COMPLETE	Remove flooring and modify
3	03/01/2024		DWS	Gemma	NO		102	5052	2	£2,000.00	£2,000.00	E82000	R&M	Gemma	COMPLETE	Clean Gauges July - Dec
4	05/01/2024		Meto	Callum	NO		103	5053	3	£3,000.00	£3,000.00	E83000	Plant Hire	Callum		Monthly service visits
5	08/01/2024		Robins	Callum	NO		104	5054	4	£1,500.00	£1,500.00	E84000	Plant Hire	Callum		Fabrication and installation of 2x bunkers
6	08/01/2024		MQPC	Lee	Yes		105	5055	5	£600.00	£600.00	E81000	Other	Lee	COMPLETE	Audit
7	10/02/2024		MQPC	Lee	Yes		106	5056	6	£600.00	£600.00	E81000	Other	Lee	COMPLETE	2nd Audit
8	10/03/2024		MQPC	Lee	Yes		107	5057	7	£600.00	£600.00	E81000	R&M	Lee	COMPLETE	3rd Audit
9	10/04/2024		Vulcs	Lee	No	1002365894	200568978	5056504896	8	£200.00		E81000	R&M	Lee	CANCELLED	Work
Orders

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2								Lists for drop down validation.
3		Month	Cost Code	GL Code				Cost Codes	GL Code	Months
4		January 2024	E83000	Plant Hire
5								E81000	Other	January 2024
6		E83000	Plant Hire	3000				E82000	Plant Hire	February 2024
7								E83000	R&M	March 2024
8								E84000		April 2024
9
10
11
12
13
Report

Cell Formulas
Range	Formula
H4:H8	H4	=VSTACK(" ",SORT(UNIQUE(tblOrders[Cost Code])))
I4:I7	I4	=VSTACK(" ",SORT(UNIQUE(tblOrders[GL Code])))
J4:J8	J4	=VSTACK(" ",LET(d,SORT(tblOrders[Date raiised],1),UNIQUE(TEXT(d,"MMMM YYYY"))))
B6:D6	B6	=IFERROR(CHOOSECOLS(FILTER(tblOrders,(TEXT(tblOrders[Date raiised],"MMMM YYYY")=$B$4)*(tblOrders[Cost Code]=Report!$C$4)*(tblOrders[GL Code]=Report!$D$4)),{13,14,12}),"No Records To Show")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$J$4#
C4	List	=$H$4#
D4	List	=$I$4#