I work for a charitable organization and I am developing a spreadsheet to keep a track of the medicines we dispense to the patients who visit our free clinic. I have uploaded my spreadsheet so that you can see what I have done. There is a Master Sheet which contains data of the medicines we use in our clinic. We enter the medicines dispensed each day in the "Daily Drug Dispensation Chart". I have used VlookUp formulas so that when the Brand Name is selected in the first column in the "Daily Drug Dispensation Chart", the Generic Name, Batch No. and Expiry Date of that medicine is autofilled in the other columns from the Master Sheet.
Now the problem: the Brand Name and Generic Name do not change in the Master Sheet. However, once one batch of a particular Brand is finished, we have to change the Batch No. and Expiry Date in the Master Sheet. But as soon as this is done, the previous entries of that Brand Name get auto-updated to the new Batch No. and Exp. Date. We want that this should not happen. Is there some VBA code I can use which will still achieve this autofill from the Master Sheet, but then the values do not get auto-updated, whenever the Batch No. and Exp. Date are updated in the master sheet?