Populate a range of cells to end up with a given total, Solver? Formula?

[James8761]

Hi All,
I am working on a project for a school. We have a budget of £4,000 to spend in a month of Teaching Costs. I need to try and get this spent each month to be able to have that amount again next month. Ideally it would come back to this, although there is a £200 buffer either over or under.
For October I have set up the following
Column
A = Date
B = Data Validation list. Looking at the lookup table below.
C = VLOOKUP based on selection in Column B and then looking at the lookup table below.

F & G = Lookup Table. Teacher and their cost per day.

I can play around with this manually all day but wondered if there was a way to automate?

Thanks for any advice.

Cheers,
James

English	Budget =	£4,000​	Target =	£0​	LOOKUP TABLE
Date	Teacher	Cost			A	£243​
01/10/2024​	B	£178​			B	£178​
02/10/2024​	C	£276​			C	£276​
03/10/2024​	C	£276​			D	£129​
04/10/2024​	F	£110​			E	£155​
05/10/2024​					F	£110​
06/10/2024​					G	£298​
07/10/2024​	F	£110​			H	£188​
08/10/2024​	E	£155​			I	£145​
09/10/2024​	I	£145​			J	£222​
10/10/2024​		£0​
11/10/2024​	G	£298​
12/10/2024​
13/10/2024​
14/10/2024​	C	£276​
15/10/2024​	H	£188​
16/10/2024​	A	£243​
17/10/2024​	B	£178​
18/10/2024​	D	£129​
19/10/2024​
20/10/2024​
21/10/2024​	J	£222​
22/10/2024​		£0​
23/10/2024​	C	£276​
24/10/2024​	C	£276​
25/10/2024​	B	£178​
26/10/2024​
27/10/2024​
28/10/2024​	H	£188​
29/10/2024​	B	£178​
30/10/2024​	A	£243​
31/10/2024​	D	£129​
	Spent	£4,252​
	Remaining	-£252​

 

[NdNoviceHlp]

Hi James8761. You need to supply some more info/guidelines for your objective. Why no teachers on some days? Teacher availability, contracted hours and/or equitable hours distribution? You can randomly generate multiple solutions but they're meaningless if the teachers aren't available. Hope this gets you started. I'm sure others will assist as I hope to be busy with the harvest for the next several days. I'll check in when I can. Have a nice day. Dave

 

[James8761]

Thank you for your reply.
So, there will no teachers on weekends. Also, they could be covering in other subjects, such as History, Geography, Science etc. So I have columns I want to set up for those as well.
On a separate tab I have a lookup to check that a teacher is only teaching one subject per day.

Thanks again for your reply, and enjoy the next few days.

Kind regards,
James

 

[NdNoviceHlp]

Hi again James. How many teachers are needed each day? Are some teachers part time or full time? Are they all available always? Other than cost, need to know what other factors determine who works when. HTH. Dave