Question_Multiple Running totals_Same worksheet

Hello,

I am trying to keep an excel sheet for a formulary at a volunteer clinic for the medications we carry. Part of it will include couple of columns to have the current on hand quantity. I would like one column to have the total on hand quantity, and another column right next to it to have the current amount of medication dispensed. I would like the current amount dispensed column to automatically be subtracted from the total on hand quantity column when a new value is entered. So, it will look like this:

Drug Name, Indication, Dosage, Dispensed Amount, Current On hand
A.....................................................90....................=(1000-90)
B......................................................15.....................=(300-15)
Then, when we dispense again:

Drug Name, Indication, Dosage, Dispensed Amount, Current On hand
A....................................................30.......................=(910-30)
B ................................................................................=(285)

and yet again...

Drug Name, Indication, Dosage, Dispensed Amount, Current On hand
A.................................................... 60...................... =(880-60)
B .....................................................30 ..................... =(285-30)

I have been able to do exactly what I'm looking for for just one row, using a code that I copy and pasted from another forum into the "view code" menu of Visual Basic editor, but I have multiple rows with multiple medications that I'd like to be able to do this for. Is this possible, and can anyone please help me?

If I do this on Windows, will it work on mac?

Thanks.

T

Originally Posted by PaulMcF

If you don't have too many drugs, you can do this using VBA and the Worksheet object's Change event. Open the worksheet's module (in the VBA Editor, right-click the worksheet and then click View Code). In the module window, select Change in the Procedure list (the one on the right).

Assuming your Dispensed Amount is in column D and the Current On Hand amount is in column E, and that your data starts in row 2, populate the Worksheet_Change stub with a series of If...ElseIf clauses, like so:

Please Login or Register  to view this content.

This works in Mac and Windows versions of Excel.

Cheers,
Paul

Thank you; if I copy and paste this code:

ElseIf Target = Range("D4") Then
Range("E4") = Range("E4") - Range("D4")

And repeat it for however many medications I have, changing the rows each time, will that work?

And I'm not sure how many medications. About 50 I'd say, and there is always a chance we'd have to add more later.

Another question: if I delete a row, will the code adjust accordingly? For example, down the line if we have this code entered and delete row 4 in the spreadsheet, will any code that follows row 4 adjust for the absence? (i.e. if we have code for row 5, will it adjust and become code for the new row 4, or will I have to go in and manually change it?)

I don't foresee this happening too much, but if we add a medication, and I believe this is an alphabetic list, then just wondering if the code will adjust for that or not.

Last question: Is there a way to get it to display the last time it was saved? I've got it to the point of telling me today's date, which is fine, but last saved would be great, too. That way we can make sure people are saving it.


Thanks!!!

Originally Posted by JapanDave

So how do you determine the amount to start with Paul?

This goes directly onto the worksheet, in the Current On Hand column.

Cheers,
Paul

Originally Posted by sportguy0913

Thank you; if I copy and paste this code:

ElseIf Target = Range("D4") Then
Range("E4") = Range("E4") - Range("D4")

And repeat it for however many medications I have, changing the rows each time, will that work?

And I'm not sure how many medications. About 50 I'd say, and there is always a chance we'd have to add more later.

Yes, copying-and-pasting that code (and, of course, adjusting the cell addresses as you go) will work. 50 is not so bad. This method would be a tad unwieldy if you were talking about hundreds or thousands of items.

Originally Posted by sportguy0913

Another question: if I delete a row, will the code adjust accordingly? For example, down the line if we have this code entered and delete row 4 in the spreadsheet, will any code that follows row 4 adjust for the absence? (i.e. if we have code for row 5, will it adjust and become code for the new row 4, or will I have to go in and manually change it?)

I don't foresee this happening too much, but if we add a medication, and I believe this is an alphabetic list, then just wondering if the code will adjust for that or not.

Deleting a row triggers the Change event, but it sends the entire affected range as the Target. However, the code assumes we're working with a single cell, so it throws up an error. The easiest (read: laziest) way to work around this is to add the following statement before the first If statement:

Please Login or Register  to view this content.

Cheers,
Paul

Originally Posted by sportguy0913

Last question: Is there a way to get it to display the last time it was saved? I've got it to the point of telling me today's date, which is fine, but last saved would be great, too. That way we can make sure people are saving it.


Thanks!!!

Yup. In the VBA Editor, double-click ThisWorkbook to open the workbook's module, choose Workbook in the Object list and BeforeSave in the Procedure list. You then write the current date and time to a cell. Here's an example:

Please Login or Register  to view this content.

Adjust to taste and you're done!

Cheers,
Paul