Code to Allocate Funds

[Kro]

A B C D E F G
1Cat Item Mon Tue Wed Thu 24
2 24
3 0
4
5 Cat 1
6 Item 1.1 4 4 6
7 Item 1.2 6 6
8 Cat 2
9 Item 2.1 5 5 5
10 Item 2.2 7 7

A- Category Name
B- Item Name
C-F- Item Expenses

G1- Budget
G2- Amount Allocated
G3- Balance to Allocate
G6- Allocated to Item 1.1
G7- Allocated to Item 1.2
G9- Allocated to Item 2.1
G10- Allocated to Item 2.2

I have an excel spreadsheet with expenses required for items scheduled
Monday thru Thursday. I also have a budget (G1) & want to allocate these
funds toward each item (G6:G10).

I need to write code that will allocate the budget amount toward these items
& load these values into the respective item row of column G (G6:G10).

As the Budget is allocated (G2), the Balance to Allocate (G3) will approach
0, and at some point will be less than the expense amount. If the Balance to
Allocate (G3) is less than expense amount for an item being considered, then
the Balance to Allocate amount will be allocated toward the item (not the
expense amount) & the program will stop since all funds have been allocated.

 

[willy]

Hi Kro

I need a little clarification,
can it just be worksheet functions and calculations, why code? How
repetitive is this allocation exercise?
and the structure of your table, I don't fully understand, and maybe you
should pull it apart a bit to get A B away from C - F? If you do a little
homework on learning the value of a well structured list/table/database then
that might make these problems easier?

Cheers willy

 

[stew]

Hi Kro

1.Is the Budget a weekly budget
2. what is the criteria for the percentage Distribution to the 4 items
3. on your example has item 1.1 and 2.1 gone over budget?

Best

Stew