H'oh, boy.
In my valiant attempts to try to make my team do as little actual entry as possible and because we currently seem to have a habit of having multiple records inputters who don't know our system very well, I've been trying to automate our Batch Log as much as possible to reduce errors and confusion. I've got drop down lists and VLOOKUPs whenever possible and that will reduce errors but I'd like even more automation.
But first, a few caveats: we work in XP with Excel 2000 (I know:::sigh::: ). We can not download or install any add-ons on our machines without IT's permission... and they're not ever going to give it.
OK. Now, as you can deduce from the expandable image below, we receive batches of invoices that we process to eventual payment. The batches are given a number based on the Fiscal Year Period+sequence number and we never know how many batch numbers will be used in any given Period or how many invoices will be included under any one batch number.
This Batch Log is our record. Immediately after entering the information for the batch, we go on to use the information in Oracle and process the batches for payment and then send the physical batches up to our Finance Department for possible audit.
1. What I'm trying to do is to figure out how to get D4 to count how many instances a singular batch # will appear in column 'B' and just record that number. And then for every other singular batch number that will eventually be inputted into column 'B'.
I don't really care if the calculated number appears in 'D' on all the rows with the same batch #, if that makes things easier. This'll cut down on possible discrepancies between what we've recorded originally and what we processed through Oracle in case an invoice is pulled from the batch but the pull is not properly reflected in the batch log (i.e. someone pulls John Davis' $20.00 invoice but doesn't alter the # of Invoices or Total Amount of Invoices columns).
2. Another thing Oracle will require is the total cost amount of invoices per Batch #. Yeah... seems some of our team aren't too good at hitting all the right number keys on their calculators and we've had some batches be off. I'd like to be able to blame Excel from now on if the totals don't match
This one's a little tricky since I'd prefer finding a way where the total only appears in the first cell of the Batch # sequence (in case we may play with AutoSum-ming in the future). If that's too much to hope for, then we'll just live with multiple entries.
I thought of the COUNT functions but so many examples seem dependent on dates that it's hard to figure out how to do a simple count of a number in a dynamic range without having to define that number in the formula itself.
Any guidance would be appreciated in this 'cause at this point I'm flummoxed. Help?
B.W.
In my valiant attempts to try to make my team do as little actual entry as possible and because we currently seem to have a habit of having multiple records inputters who don't know our system very well, I've been trying to automate our Batch Log as much as possible to reduce errors and confusion. I've got drop down lists and VLOOKUPs whenever possible and that will reduce errors but I'd like even more automation.
But first, a few caveats: we work in XP with Excel 2000 (I know:::sigh::: ). We can not download or install any add-ons on our machines without IT's permission... and they're not ever going to give it.
OK. Now, as you can deduce from the expandable image below, we receive batches of invoices that we process to eventual payment. The batches are given a number based on the Fiscal Year Period+sequence number and we never know how many batch numbers will be used in any given Period or how many invoices will be included under any one batch number.
This Batch Log is our record. Immediately after entering the information for the batch, we go on to use the information in Oracle and process the batches for payment and then send the physical batches up to our Finance Department for possible audit.
1. What I'm trying to do is to figure out how to get D4 to count how many instances a singular batch # will appear in column 'B' and just record that number. And then for every other singular batch number that will eventually be inputted into column 'B'.
I don't really care if the calculated number appears in 'D' on all the rows with the same batch #, if that makes things easier. This'll cut down on possible discrepancies between what we've recorded originally and what we processed through Oracle in case an invoice is pulled from the batch but the pull is not properly reflected in the batch log (i.e. someone pulls John Davis' $20.00 invoice but doesn't alter the # of Invoices or Total Amount of Invoices columns).
2. Another thing Oracle will require is the total cost amount of invoices per Batch #. Yeah... seems some of our team aren't too good at hitting all the right number keys on their calculators and we've had some batches be off. I'd like to be able to blame Excel from now on if the totals don't match
This one's a little tricky since I'd prefer finding a way where the total only appears in the first cell of the Batch # sequence (in case we may play with AutoSum-ming in the future). If that's too much to hope for, then we'll just live with multiple entries.
I thought of the COUNT functions but so many examples seem dependent on dates that it's hard to figure out how to do a simple count of a number in a dynamic range without having to define that number in the formula itself.
Any guidance would be appreciated in this 'cause at this point I'm flummoxed. Help?
B.W.
