Anyone good with microsoft excel?

John_F

Hi, for the bord bia and dept animal remedies you should have the date the end of milk and meat withholding is. so i came up with a brainwave trying to help a relative out with it, first plan was to have a sheet on excel and enter the date of administration of the drug, then a colmun for each drug, then a column for milk withholding days and another for meat days. the next column would be the end date of the withholding period the formula would be =Date of first administration + column for milk withholding days. it works as said in the first attachment.

then the plan was to use that info to actually record the drug entries by date as in the bord bia book or dept blue book on another sheet , i.e. sheet 2. I have gotten as far to have a column in sheet 2 with a drop down list of the drugs from sheet 1.

where i am stuck now is the milk and meat withholding days on sheet 2. the plan would be to have it calculated dependant on the drug selected in the drop down list from sheet 1 , and then add this figure (i.e. the number of days) to the date of administration on sheet 2.

can anyone help?? if it sounds complicated please tell me lol

gk5000

If you want to type the reference to the worksheet cell, include the sheet name followed by an exclamation point, for example, Sheet1!F2

onyerbikepat

It's the HLOOKUP function you want. I think.

Taltos

OK - think I got it.
I am ignoring the dates in sheet 1.
Instead I am just using columns
C - Drug name
D - Days Milk Withholding
E - Days Meat Withholding
Also - rename your sheet1 - remove the & sign and just type in and e.g. Drug and Withhold as & can cause Excel to freak out.

Now the formulas for sheet two are
Goto F4 and paste this in exactly when you rename your 1st sheet
=IF($B4<>"",$B4+VLOOKUP($C4,'Drug and Withhold'!$C:$E,2,FALSE),"")

Goto G4 and past this in exactly
=IF($B4<>"",$B4+VLOOKUP($C4,'Drug and Withhold'!$C:$E,3,FALSE),"")

What this will do is if you have no date then nothing will show up.
If you have a Date - it will look for the drug in sheet1, take the date entered in sheet2 and add the appropriate days.
However - if you have a drug missing in Sheet1 you will get #N/A as your result.

Here is an example of the same drug used on two different dates in sheet2
Date of Admin|Name of Medicine|Qty Used|Animal ID|End Milk Withhold|End Meat Withhold

30/04/2014|Albex|5||03/05/2014|14/05/2014

25/04/2014|Albex|10||28/04/2014|09/05/2014

John_F

Taltos wrote: »

OK - think I got it.
I am ignoring the dates in sheet 1.
Instead I am just using columns
C - Drug name
D - Days Milk Withholding
E - Days Meat Withholding
Also - rename your sheet1 - remove the & sign and just type in and e.g. Drug and Withhold as & can cause Excel to freak out.

Now the formulas for sheet two are
Goto F4 and paste this in exactly when you rename your 1st sheet
=IF($B4<>"",$B4+VLOOKUP($C4,'Drug and Withhold'!$C:$E,2,FALSE),"")

Goto G4 and past this in exactly
=IF($B4<>"",$B4+VLOOKUP($C4,'Drug and Withhold'!$C:$E,3,FALSE),"")

What this will do is if you have no date then nothing will show up.
If you have a Date - it will look for the drug in sheet1, take the date entered in sheet2 and add the appropriate days.
However - if you have a drug missing in Sheet1 you will get #N/A as your result.

Here is an example of the same drug used on two different dates in sheet2
Date of Admin|Name of Medicine|Qty Used|Animal ID|End Milk Withhold|End Meat Withhold

30/04/2014|Albex|5||03/05/2014|14/05/2014

25/04/2014|Albex|10||28/04/2014|09/05/2014

that works 100% thanks taltos!!! :cool:

Timmaay

Taltos has it solved for ya, only thing I'd say is Index(match) does the same job as vlookup but is alot more flexible, your not stuck to consecutive columns.