Simple 'Dispensing' program

[peteprp]

Hi all,
I want to create a very simple dispensing program. I have an excel listing of medications - see below. I want to be able to do a 'wildcard' search for a particular medication and then be able to enter the quantity dispensed. The quantity dispensed should be added to the cumulative total dispensed for that particular medication. At the end of the day or week, I'd like to be able to see & download/copy the total amount dispensed for each medication.

[TABLE="width: 404"]
<tbody>[TR]
[TD]DRUG NAME[/TD]
[TD]UNITS[/TD]
[/TR]
[TR]
[TD]Aluminium Chlorohydrate Cream 20%[/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Aluminium sulphate Solution 20% Spray 25mL[/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Amethocaine Mimum 0.5% 20's[/TD]
[TD]Per Minum[/TD]
[/TR]
[TR]
[TD]Aminophylline Amps 250mg/10ml[/TD]
[TD]Per Amp[/TD]
[/TR]
[TR]
[TD]Amiodarone Inj 150mg/3mL[/TD]
[TD]Per Amp[/TD]
[/TR]
[TR]
[TD]Amlodipine Tabs 10mg[/TD]
[TD]Per Tab[/TD]
[/TR]
[TR]
[TD]Amoxycillin 100mg Drps[/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Amoxycillin 400mg, clavulanate 57mg Syr [/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Amoxycillin 875mg, clavulanate 125mg Tabs [/TD]
[TD]Per Tab[/TD]
[/TR]
[TR]
[TD]Amoxycillin Amps 1gm[/TD]
[TD]Per Vial[/TD]
[/TR]
[TR]
[TD]Amoxycillin Caps 250mg[/TD]
[TD]Per Caps[/TD]
[/TR]
[TR]
[TD]Amoxycillin Caps 500mg [/TD]
[TD]Per Caps[/TD]
[/TR]
[TR]
[TD]Amoxycillin Syrup SF 125mg/5ml [/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Amoxycillin Syrup SF 250mg/5ml [/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Antazoline/ Naphazoline Eye Drops [/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Aqueous Cream 100g cream[/TD]
[TD]Ea[/TD]
[/TR]
[TR]
[TD]Artesunate Inj 60mg[/TD]
[TD]Per Vial[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 317"]
<tbody>[TR]
[TD]Search Drug Name:[/TD]
[/TR]
[TR]
[TD]For example, type in amox 25 c[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 587"]
<tbody>[TR]
[TD="colspan: 2"]Then show result below together with Units column & add[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]the number of capsules dispensed in the column alongside[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Enter Number Dispensed[/TD]
[/TR]
[TR]
[TD]Amoxycillin Caps 250mg[/TD]
[TD]Per Caps[/TD]
[TD]

5​

[/TD]
[/TR]
</tbody>[/TABLE]

If anyone can help, I would really appreciate it. Is it possible to do this without using a macro?
Thank you.
Pete

 

[Peter_SSs]

My reply edit timed-out so I lost what I'd written down.

If you click the 'Remember Me?' box when you sign in, that shouldn't happen.

.. the first question was whether it would be possible for the cursor to return to cell C3 (SearchText) after having entered the amount dispensed for the particular medication?

That one is easy, just add the blue line of code where shown near the bottom of the existing code.

Range("SearchText").ClearContents '<- Delete if not required
Range("SearchText").Select

I didn't understand the second question.

 

[peteprp]

If you click the 'Remember Me?' box when you sign in, that shouldn't happen.

Thank you for the tip here.


That one is easy, just add the blue line of code where shown near the bottom of the existing code.

Range("SearchText").ClearContents '<- Delete if not required
Range("SearchText").Select

That works perfectly.Thank you.

I didn't understand the second question.

OK, sorry, I’ll try &explain it better - currently I have 2 tabs in the workbook. One tab is the'Dispense' Tab for which you have kindly written the code. The second tab containsthe Master Stock File. The Master Stock file has other information about eachmedication such as product code, trade name, total stock on hand, number perpack, price etc. So the ‘Dispense’ Tab actually references the Master Stockfile, ie cell A4 is actually ‘=MASTER STOCK SHEET’!B5.
The cumulative totals from the ‘Dispense’ tab are copied tothe ‘Master Stock Sheet’ Tab as the total quantities dispensed for eachmedication & these amounts are subtracted from the stock on hand to give theactual stock on hand for each medication.
I would really liketo add an additional 3 tabs to the workbook – one each for ‘Stock Received’, ‘ExpiredStock’ & ‘Stock Adjustment’. Each of these spread sheet tabs would ideallybe in exactly the same format as the ‘Dispense’ tab. So, for example, thecumulative totals from the ‘Stock Received’ tab would be added to the stock onhand in the ‘Master Stock Sheet’.
I have tried to add these spread sheet tabs to the workbook bycopying the ‘Dispense’ Tab but the code does not work for anything other thanthe ‘Dispense’ Sheet.
I hope this is a better explanation but let me know if youhave other queries.
So, the big question is – would it be difficult to get thecode to work for the 3 additional tabs? I have no idea what’s involved and if itstoo big a job to get the code working for the additional spread sheet tabs thenplease don’t worry.
Thank you very much for all the time you have spent on this thusfar. I really do appreciate it.

 

[Peter_SSs]

If I copy the Dispense tab, for me the code works exactly the same on the copied tab.

The cumulative totals from the ‘Dispense’ tab are copied tothe ‘Master Stock Sheet’ Tab

How?
Wouldn't that just be a formula (VLOOKUP or INDEX/MATCH) on the ‘Master Stock Sheet’ Tab? And so could you use a similar formula to get the totals from the new tabs too?

 

[peteprp]

If I copy the Dispense tab, for me the code works exactly the same on the copied tab.

OK. I'll have play with it and try again.

How?
Wouldn't that just be a formula (VLOOKUP or INDEX/MATCH) on the ‘Master Stock Sheet’ Tab? And so could you use a similar formula to get the totals from the new tabs too?

I did it the simple way - using '=DISPENSE STOCK'!D4 etc. I'll have to look into VLOOKUP or INDEX/MATCH as I'm not familiar with them.
I'll let you know how I go. Thanks again.

 

[Peter_SSs]

I did it the simple way - using '=DISPENSE STOCK'!D4 etc.

If your sheet layouts are identical, that should be all that is required.

 

[peteprp]

Well, thank you. I got the additional tabs/sheets to work perfectly. I've also had a resonable look at VLOOKUP & INDEX/MATCH but, up until now, I'm struggling to understand them. I'll do more homework on them. I'm now at the stage where the workbook is working really well, thanks to all your efforts. I would like to progress on a few ideas and may, if that's OK with you, make contact again next week. Thank you again for all your help.

 

[Peter_SSs]

Well, thank you. I got the additional tabs/sheets to work perfectly. I've also had a resonable look at VLOOKUP & INDEX/MATCH but, up until now, I'm struggling to understand them. I'll do more homework on them. I'm now at the stage where the workbook is working really well, thanks to all your efforts.

Good news. You are welcome.

I would like to progress on a few ideas and may, if that's OK with you, make contact again next week. Thank you again for all your help.

Unless the new questions are very closely related, you would be best to start a new thread. There won't be a lot of new readers of this thread now that it has >30 posts so you would be restricting the number of people who might help.

 

[peteprp]

Ok. Thanks for the advice.