Quote:
Originally Posted by Spencer101
From your post above, your workbook seems to be quite flexible. Would a helper column or two be out of the question? If not, then perhaps a hidden column that extracts just the first 6 digits from the product codes and use that for the vlookup?
For example, based on your sample image, if you inserted a new column F between "Product Code" and "Standard Rate / Hour" and in F2 enter the formula =LEFT(E2,6) . This will then let you put a vlookup in cell C2 ( =VLOOKUP(B2,F2:G28,2,FALSE) in the case of your sample image but with the new hidden column F.) but it will mean the user will have to type in the preceding zeros to ensure it's a six digit number.
There are other ways of doing this, but would need an actual example workbook, rather than the image file, before I could sort one out.
|
Thank you, Spencer...I did think of that (and it does work), but I was trying to make this little snippet as idiot proof as possible, as the report that the 3-6 digits is gleaned from does not print the leading zeros. In that way, the user will only have to enter the numbers printed (sans the zeros) to get the result (I know, you would think grown people who work here would know that a code used in our process was 6 digits long, but alas). I can send you the workbook, if you have the time, and send me another contact point (I tried uploading the project to the forum, and it wouldn't accept anything but a .jpg/picture). Thanks again for your help.
Similar Threads
Threaded Mode
