populating the cells by performing calculations.

I have one sheet which have the set values as follows

category Total amount
category1 1000
category2 2500
category3 600
category4 9000

I have other set of values as

Name Amount Allocated finally
suresh 1000
ravi 800
rajesh 479
ramesh 3500

Is there any process to auto populate randomly the Allocated finally by taking the amount from second set of values and see whether the sufficient amount was there in first set of values

if sufficient amount is there allocate from one ,if sufficient amount was not there then we have to allocate from two categories or three or 'n' and print the categories in other column(i.e from what categories the amount was allocated)

For Example:

If i consider Ravi he has 800 to be allotted.

Then I should be able to check the first set whether 800 is present.

If exact 800 is present in one category allocate that category to ravi.
If 800 was not present in one category allocate from two categories to ravi
If from all categories if we sum and we get less amount than to be allotted then we should print error.

Please try to help or show me the way to proceed
Example3.xlsx

Hi,

not sure if you are still monitoring this thread, but it strikes me that it is similar to one I responded to in January. You might like to take a look here:

http://www.excelforum.com/excel-gene...nies-sums.html

Hope this helps.

Pete