petty cash form - need to split some lines not others

[ajm]

has anyone got a quick bit of code that will enable me to split an expense item into three separate items if i select "Yes" from a validation list at the end of the data input? I am trying to do a quick fix for a colleague in accounts payable who wishes to split some of the petty cash transactions across three departments. I thought, add the validation list to each line (col K), then use a worksheet change event to copy the amount to a helper cell, insert the extra three lines, mark them with a flag to signify already split, split the amount and input into the three new lines, remove the existing line, and then have a cup of tea. have i missed anything? i figure I will also have to have a way for the user to be able to revert the three new lines back to a single entry if need be. it would be awesome if anyone has done this before and doesn't mind sharing. ideas, suggestions, solutions are all welcome.

 

[ajm]

has anyone got a quick bit of code that will enable me to split an expense item into three separate items if i select "Yes" from a validation list at the end of the data input? I am trying to do a quick fix for a colleague in accounts payable who wishes to split some of the petty cash transactions across three departments. I thought, add the validation list to each line (col K), then use a worksheet change event to copy the amount to a helper cell, insert the extra three lines, mark them with a flag to signify already split, split the amount and input into the three new lines, remove the existing line, and then have a cup of tea. have i missed anything? i figure I will also have to have a way for the user to be able to revert the three new lines back to a single entry if need be. it would be awesome if anyone has done this before and doesn't mind sharing. ideas, suggestions, solutions are all welcome.

just thought i should add, currently, each line in the worksheet i am dealing with carries one expense item with a few details. mostly, there are probably 40 or so expenses each month. so, at current state, that means 40 lines in the expense sheet. if half of these need to be split to properly reflect their owners cost centres, the resulting worksheet would end up with 80 expense lines - 20 non split and 20 split three ways (60).

 

[ajm]

bump - rather than thinking of this as "petty cash", has anyone done anything along the lines of a worksheet where the user can flag a line which then splits that line out into three sub lines? i need to come up with a method to then be able to handle it so that when the flag is removed the split out lines roll back up to the original. hmm......