lurchybold Posted August 18, 2011 Report Share Posted August 18, 2011 Hi Guys, anyone on here able to help me automate a couple excel documents that would make my life easier at work, TIA, Lurch. Quote Link to comment Share on other sites More sharing options...
jelharou Posted August 18, 2011 Report Share Posted August 18, 2011 One easy(ish) way is to actually do the steps manually once while you record a Macro: Tools > Macro > Record New Macro. Once you have it, you can then rerun on the new documents(s). Quote Link to comment Share on other sites More sharing options...
Marc Posted August 18, 2011 Report Share Posted August 18, 2011 I can certainly take a look for you if you let me know what you are trying to acheive. I write vba a fair bit with my integration to office products. Quote Link to comment Share on other sites More sharing options...
lurchybold Posted August 18, 2011 Author Report Share Posted August 18, 2011 Hi Marc, thanks for the responce, There are a couple of things the first is to do with our stock list/check, We have an excel sheet that is used for recording our stock check once a month, this is usually printed out and filled out by hand, hung on a nail in the office in the hope that someone will remember to order some stock once in a while. As you can guess we run out of stock on a regular basis. I would like to automate this so that the ‘Stock level’ column is filled in on a PC, when the stock level falls below the ‘Min stock quantity’ level the ‘Order quantity’ column is automatically filled in with an order amount that is needed (this would be the minimum order quantity). Would it also be possible to have an automated e-mail sent when an order quantity is highlighted, this would need to be sent to 3 members of staff to ensure that at least we know that they have been notified that an order needs to be placed. The e-mails would be sent to the person responsible for ordering his assistant and also to the MD. Hope not asking for too much, willing to be talked through the work involved but I am not very literate with this type of thing, Thanks for at least reading this for me Marc, Regards, Andy Quote Link to comment Share on other sites More sharing options...
Sparks Posted August 18, 2011 Report Share Posted August 18, 2011 a friend of mine wanted to do something very very similar to this, but we never got round to doing it. would be cool to see the outcome of this Quote Link to comment Share on other sites More sharing options...
lurchybold Posted August 18, 2011 Author Report Share Posted August 18, 2011 a friend of mine wanted to do something very very similar to this, but we never got round to doing it. would be cool to see the outcome of this That's what I like to see, a bit of encouragement, Cheers Sparks, Andy. Quote Link to comment Share on other sites More sharing options...
lurchybold Posted August 18, 2011 Author Report Share Posted August 18, 2011 One easy(ish) way is to actually do the steps manually once while you record a Macro: Tools > Macro > Record New Macro. Once you have it, you can then rerun on the new documents(s). Hi @jelharou, sorry didn't mean to ignor your reply, ~I didn't see it~ but thanks for the reply, needs a bit more than a recorded macro that's needed though, Cheers, Andy Quote Link to comment Share on other sites More sharing options...
Marc Posted August 18, 2011 Report Share Posted August 18, 2011 Hi Marc, thanks for the responce, There are a couple of things the first is to do with our stock list/check, We have an excel sheet that is used for recording our stock check once a month, this is usually printed out and filled out by hand, hung on a nail in the office in the hope that someone will remember to order some stock once in a while. As you can guess we run out of stock on a regular basis. I would like to automate this so that the ‘Stock level’ column is filled in on a PC, when the stock level falls below the ‘Min stock quantity’ level the ‘Order quantity’ column is automatically filled in with an order amount that is needed (this would be the minimum order quantity). Would it also be possible to have an automated e-mail sent when an order quantity is highlighted, this would need to be sent to 3 members of staff to ensure that at least we know that they have been notified that an order needs to be placed. The e-mails would be sent to the person responsible for ordering his assistant and also to the MD. Hope not asking for too much, willing to be talked through the work involved but I am not very literate with this type of thing, Thanks for at least reading this for me Marc, Regards, Andy Will have a look later m8 when I get back from work Quote Link to comment Share on other sites More sharing options...
Taiya Blue and Miko too Posted August 18, 2011 Report Share Posted August 18, 2011 I would like to automate this so that the ‘Stock level’ column is filled in on a PC, when the stock level falls below the ‘Min stock quantity’ level the ‘Order quantity’ column is automatically filled in with an order amount that is needed (this would be the minimum order quantity). this could be done easily with a formula... If your stock level number is in, for example, col A and first data in A2 and your order col is col B, and you wanted your min stock order to be 40 and it to be triggered at less than or equal to 25 remaining, in cell B2 you'd put.... =IF(A2<=25,"40","0") and then just copy it down the column (can't tell what I play with at work 90% of the time ) Would it also be possible to have an automated e-mail sent when an order quantity is highlighted, this would need to be sent to 3 members of staff to ensure that at least we know that they have been notified that an order needs to be placed. The e-mails would be sent to the person responsible for ordering his assistant and also to the MD. not sure on this one but perhaps with another if statement in col C and a macro which is triggered with a positive answer? Quote Link to comment Share on other sites More sharing options...
lurchybold Posted August 18, 2011 Author Report Share Posted August 18, 2011 Will have a look later m8 when I get back from work Cheers Marc. this could be done easily with a formula... If your stock level number is in, for example, col A and first data in A2 and your order col is col B, and you wanted your min stock order to be 40 and it to be triggered at less than or equal to 25 remaining, in cell B2 you'd put.... =IF(A2<=25,"40","0") and then just copy it down the column not sure on this one but perhaps with another if statement in col C and a macro which is triggered with a positive answer? Thanks MaisieMoo, will have a look at that, Regards, Andy. Quote Link to comment Share on other sites More sharing options...
Taiya Blue and Miko too Posted August 18, 2011 Report Share Posted August 18, 2011 Any help with formula's just shout - I analyse data from GP systems in excel every day so if's, concatenates and vlookups are my thing LOL! Quote Link to comment Share on other sites More sharing options...
jelharou Posted August 18, 2011 Report Share Posted August 18, 2011 Hi @jelharou, sorry didn't mean to ignor your reply, ~I didn't see it~ but thanks for the reply, needs a bit more than a recorded macro that's needed though, Cheers, Andy No prob. I understood from the later responses what you needed and figured that the others would send the formulas, which they did. Quote Link to comment Share on other sites More sharing options...
Removed #5 Posted August 18, 2011 Report Share Posted August 18, 2011 Quite a mixed crew we have here, an interesting lot of people! I'll have to put up my VB question when I get off this project and on to another (where the challenge lies). I'm a retired truck driver who writes VB apps in his spare time and is building his own house ... I'm bored! Quote Link to comment Share on other sites More sharing options...
Marc Posted August 18, 2011 Report Share Posted August 18, 2011 Any help with formula's just shout - I analyse data from GP systems in excel every day so if's, concatenates and vlookups are my thing LOL! Great pains? Lol that data structure is an annoyance at best lol Quote Link to comment Share on other sites More sharing options...
Taiya Blue and Miko too Posted August 18, 2011 Report Share Posted August 18, 2011 Great pains? Lol that data structure is an annoyance at best lol GP as in Doctors but then Great Pains might be appropriate LOL! I write educational materials and clinical audit projects for doc's to ensure they're recalling the right patients and they're on the right therapy regimens etc etc Been looking at 250k cholesterol patients today and 50k osteoporosis ones the other day. Use excel for most of the analysis side Quote Link to comment Share on other sites More sharing options...
Marc Posted August 18, 2011 Report Share Posted August 18, 2011 GP as in Doctors but then Great Pains might be appropriate LOL! I write educational materials and clinical audit projects for doc's to ensure they're recalling the right patients and they're on the right therapy regimens etc etc Been looking at 250k cholesterol patients today and 50k osteoporosis ones the other day. Use excel for most of the analysis side Ah I see, I thought you meant GP as in the microsoft dynamics program lol. Andy, have sent you a pm Quote Link to comment Share on other sites More sharing options...
Taiya Blue and Miko too Posted August 18, 2011 Report Share Posted August 18, 2011 Ah I see, I thought you meant GP as in the microsoft dynamics program lol. Andy, have sent you a pm LOL no, although we're just integrating dynamics into work getting shot of our home made intranet diary system and going whole hog dynamics for crm, diary and using sharepoint for the documentation - all fun so far! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.