Help Autopopulate

[Peta Durose]

I have two spreadsheets I need to streamline.
First is a list of 250 "clients" and the second is a list of 13 "case managers", each have a different level of capacity.
ie.
Case manager 1 has capacity of 5
Case Manager 2 has capacity of 3

I need to be able to auto populate so my "case managers" are auto assigned to the "clients".

Really hoping someone can help me fast!!

 

[SanjayGMusafir]

Really hoping someone can help me fast!!

Upload sample data using XL2BB to understand the scenario and help you in best possible way.

 

[Peta Durose]

mrexcel help.xlsx
	A
5
Client referral

 

[Peta Durose]

Upload sample data using XL2BB to understand the scenario and help you in best possible way.

Sheet 1 = Clients details

sheet 2 = referral tool (auto populates from Sheet 1)

Sheet 3 = Cm's / Cp's and their capacity.

I need the information from sheet 3 to go into sheet 2 - where it randomly populates in the "referred to" cell.
But the populating cannot exceed the Capacity.

 

[SanjayGMusafir]

I have been trying hard for last 4 hrs to find a solution using formulae but whichever method I use, it gives circular reference error.

Probably someone expert at VBA may visit the post and help you out.