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

 

[nashmach]

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

You could use a vlookup formula.

I take it in sheet two, you just want the withdrawl period.

So you use date of admin + vlookup(product, columns with product and withdrawl period, column with periods, false)

Remember to put the columns in with the product first.

Otherwise you could use an offset and match function.

 

[John_F]

nash

columns in sheet one are:

Date of Admin----Drug----Withhold days milk----Withhold days meat----End of Milk Withhold----End of Meat Withhold.

The first column is formated to be a date, the two last columns contain the formula to add the number of days in the 'withhold days _____' column to the 'date of admin' coloumn



columns in sheet two are:

Date of Admin----Name of Medicine----Qty Used----Animal ID----End Milk Withhold----End Meat Withhold----VET----Supplier----Batch No----Expiry----Area (Tick)----Comments.

the name of medicine column in sheet 2 contains a drop list from the medicines listed in sheet 1.

can you explain the vlookup function better, dummys guide here

should i create two tables, one for milk and one for meat to use the formula properly, i think having the dropdown list in sheet two may be a problem, but would be ideal to work after.

 

[nashmach]

nash

columns in sheet one are:

Date of Admin----Drug----Withhold days milk----Withhold days meat----End of Milk Withhold----End of Meat Withhold.

The first column is formated to be a date, the two last columns contain the formula to add the number of days in the 'withhold days _____' column to the 'date of admin' coloumn



columns in sheet two are:

Date of Admin----Name of Medicine----Qty Used----Animal ID----End Milk Withhold----End Meat Withhold----VET----Supplier----Batch No----Expiry----Area (Tick)----Comments.

the name of medicine column in sheet 2 contains a drop list from the medicines listed in sheet 1.

can you explain the vlookup function better, dummys guide here

should i create two tables, one for milk and one for meat to use the formula properly, i think having the dropdown list in sheet two may be a problem, but would be ideal to work after.

No bother John.

All the vlookup formula does is take your value - lets say Albex, it will then look at a table and return the first row with Albex in it, once it has that it will return the column you want out of the list so the withdrawl period is returned.

No need for two seperate columns for that.

What I would do is use the number of days rather than focusing on the admin date.

So in your case for the above you can use the following.

= vlookup(Product, B:F in sheet one i.e. the product to withdrawl periods, 4 = milk 5 = meat, false)

The dropdown may be an issue but try that first.

 

[drew]

nash



columns in sheet one are:



Date of Admin----Drug----Withhold days milk----Withhold days meat----End of Milk Withhold----End of Meat Withhold.



The first column is formated to be a date, the two last columns contain the formula to add the number of days in the 'withhold days _____' column to the 'date of admin' coloumn







columns in sheet two are:



Date of Admin----Name of Medicine----Qty Used----Animal ID----End Milk Withhold----End Meat Withhold----VET----Supplier----Batch No----Expiry----Area (Tick)----Comments.



the name of medicine column in sheet 2 contains a drop list from the medicines listed in sheet 1.



can you explain the vlookup function better, dummys guide here



should i create two tables, one for milk and one for meat to use the formula properly, i think having the dropdown list in sheet two may be a problem, but would be ideal to work after.

Would it not be as simple to just leave it all on one sheet? Like all the columns on first page are also on second so would it not be as simple to just leave it all on one page, and then your not flicking back and forth the hole time?

 

[nashmach]

Would it not be as simple to just leave it all on one sheet? Like all the columns on first page are also on second so would it not be as simple to just leave it all on one page, and then your not flicking back and forth the hole time?

I'd tend to agree but I would put the products and their respective expiry dates on a seperate tab and do as I say with the vlookup then.

Should be quite simple then.

 

[drew]

I'd tend to agree but I would put the products and their respective expiry dates on a seperate tab and do as I say with the vlookup then.



Should be quite simple then.

Yeah that would make more sense alright! There'd nearly have to be a seperate sheet to do that anyways wouldn't there? It's a good 2years since or more since i played around with that sort of stuff in excel, bout 4year ago id have written the coding to make it into a proper pop up input form in my sleep!

 

[nashmach]

Yeah that would make more sense alright! There'd nearly have to be a seperate sheet to do that anyways wouldn't there? It's a good 2years since or more since i played around with that sort of stuff in excel, bout 4year ago id have written the coding to make it into a proper pop up input form in my sleep!

You could have it to one side but at least that way it makes it easier to modify and also to add new products. :001_smile:

Only issue would be if the withdrawl dates changed on products in the future

 

[drew]

You could have it to one side but at least that way it makes it easier to modify and also to add new products. :001_smile:



Only issue would be if the withdrawl dates changed on products in the future

Yeah much easier to leave the options for drop down menus on a seperate page.


And your second point is very important to! Could easily catch you out, i know mastermectin withdrawal got longer by another week i think in the last year or so!

 

[John_F]

sorted, a lad on boards knows his excel!!

thanks lads

 

[nashmach]

sorted, a lad on boards knows his excel!!

thanks lads

Much different to our approach John?

 

[John_F]

http://www.boards.ie/vbulletin/showpost.php?p=90178183&postcount=4

similar enough, still using vlookup

really bord bia should have a file available to download for farmers to use instead of printing of thousands of books to fill out, we aren't all in the stone age, at least i think

 

[nashmach]

http://www.boards.ie/vbulletin/showpost.php?p=90178183&postcount=4

similar enough, still using vlookup

really bord bia should have a file available to download for farmers to use instead of printing of thousands of books to fill out, we aren't all in the stone age, at least i think

Yep that is the same [emoji106]

Absolutely but sure they would want you to download some Farm Software then whereas most of this can be done in an hour on Excel.

Even the Teagasc Profit Monitor spreadsheets are not difficult.