array

[oracle_coorg]

hi
there are three sheets one bank_notes second notes and last value
i need an array function which can calcualte the notes aviavable on banks_notes
which i enter in the value sheet the put then in notes sheet
eg:
1.if i enter 150 in value sheet value cell
i should GET from the bank_notes sheet CELL avaiable_NOTES WITH THE HIGHEST facevaule
i should get one 100 note and one 50 note in notes sheet
2. if i enter 255 in value sheet value cell
i should GET from the bank_notes sheet CELL avaiable_NOTES WITH THE HIGHEST facevaule
i should get one 200 note and one 50 note and one 5 note in notes sheet
3.so on

[rory]

How's this?

[oracle_coorg]

hi
thanxs a lot in one shot gr8

one more issue in this
if for eg:
250 one 200 and one 50
it should delete from bank_notes cell AVAILABLE_NOTES
18 to 17 and one 50 AVAILABLE_NOTES 25 to 24
because AVAILABLE_NOTES will decrease as the notes are given
thanxs once agin

[rory]

You can't do that with a formula so it would have to be VBA - is that acceptable?

[oracle_coorg]

yes

[rory]

OK and how do you want it triggered? Via a button, or when you change the value in the cell on the value sheet? Also, do you want the used notes stored as a list on the notes sheet, or do you want to overwrite them each time?

[oracle_coorg]

no dont want to overwrite them each time?
rest every thing
thanxs rory for soo fast

[rory]

How's this - I added a button to run the allocation.

[oracle_coorg]

hi

its not working when i click allocate notes
1.one more thing when i get the notes
it should decares the value from avaiable _notes
eg:if 150
avaiable _notes 100 is 75 it should be 74 and 50 is 22 it should be 21
2.if there is no avaiable _notes it should give message "no currency avaiable"
balance is ....

[rory]

Did you enable macros? It will allocate the notes, reduce the available count for each and show any unallocated balance in the final column.

[oracle_coorg]

hi
ok i enabled
i got different error
when i dont have any notes i get error (debug)... in code..

[oracle_coorg]

hi
ok i enabled it works
i got different error
when i dont have any notes i get error (debug)... in code..

[rory]

I admit I never considered that option! Try this one.

[oracle_coorg]

no attachment did not recieve attachement

[rory]

It's in the post above.