PDA

View Full Version : array



oracle_coorg
09-03-2007, 09:33 PM
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
09-04-2007, 01:45 AM
How's this?

oracle_coorg
09-04-2007, 04:17 AM
hi
thanxs a lot in one shot gr8
:clap:
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
09-04-2007, 04:22 AM
You can't do that with a formula so it would have to be VBA - is that acceptable?

oracle_coorg
09-04-2007, 04:28 AM
yes

rory
09-04-2007, 04:39 AM
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
09-04-2007, 04:45 AM
no dont want to overwrite them each time?
rest every thing
thanxs rory for soo fast

rory
09-04-2007, 05:35 AM
How's this - I added a button to run the allocation.

oracle_coorg
09-04-2007, 10:46 PM
hi
:think:
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
09-05-2007, 01:45 AM
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
09-05-2007, 01:54 AM
hi
ok i enabled
i got different error
when i dont have any notes i get error (debug)... in code..

oracle_coorg
09-05-2007, 01:55 AM
hi
ok i enabled it works
i got different error
when i dont have any notes i get error (debug)... in code..

rory
09-05-2007, 02:44 AM
I admit I never considered that option! Try this one.

oracle_coorg
09-05-2007, 04:29 AM
no attachment did not recieve attachement

rory
09-05-2007, 04:50 AM
It's in the post above.