Steve you're probably the one only person on this board who can bail me out of this one.
I'm using Excel 2002. Everyday at my temp job, i handwrite on a notepad all the orders that we get and the number of stock options he/she is exercising along with the company name.
Ex: Jane Smith | 100 shares | HDI
HDI = Harley Davidson.
Current method is labour intensive, cumbersome and very error prone. I want to type that information into an excel sheet which i want in turn to interact with an existing Excel sheet. The latter is an Excel sht. named "Checks.xls". This is a document I use to cut the checks so after Jane Smiths' stock options have been exercised, we issue a check for the net proceeds. In addition, we issue two checks made out to "Harley Davidson FBO: Jane Smith"
None of this automated so it's very time consuming and error prone. Can you see where I'm going with this? If i type the following information into the daily order sheet:
Ex: Jane Smith | 100 shares | HDI
I want it to automatically populate the Checks.xls document w/ her name and the "Payable to:" column accordingly.
Check #1001| HDI| January 29, 2005| Jane Smith | $xxx.xx Check #1002| HDI| January 29, 2005| Harley Davidson FBO: Jane Smith | $xxx.xx Check #1002| HDI| January 29, 2005| Harley Davidson FBO: Jane Smith | $xxx.xx
None of this would be a big deal but for the fact there are close to a dozen different companies besides Harley Davidson and I'm giving a very simple watered down version of the lengthy verification process involved here. On a busy day, it can get very hectic and exhausting. I really would like to automate this as much as possible.
At the very least, i'd like you show me how I can have it so that if I type in "HDI" in the 2nd column it will automatically populate the 4th column with "Harley Davidson FBO:".
Using the "LOOKUP" function should do what you want without a custom VB script. What it does is reference specific data from another cell when ever you make a specific entry.
#2. "RE: Excel VBA" In response to _Chewy_ (Reply # 0) Sat Jan-29-05 07:26 PM by SteveYandl
As nightlyreader points out, you can do the last part with a lookup function, provided the table that relates abbreviations with company names is somewhere in the same workbook.
I'm gathering from the described scenario that you want your entries made in a workbook that is a separate workbook than checks.xls as opposed to a separate sheet. Here, you would need VBA to open the other workbook and read data or add data to it (unless you expect checks.xls to always be open when you're doing your work). You could either have a subroutine tied to the Worksheets_Change event or create a userform to enter data into your workbook (my choice). In addition to being more user friendly, the userform approach allows you to check that the entry makes sense and generate error messages if necessary before some cells get updated and others might not. Just have text boxes on your user form for data to be entered and an OK button to launch subroutines.
The required VBA isn't tough but there are a lot of details. That makes it hard to give meaningful examples to work from without more background on the workbook(s) and worksheets in those workbooks.