Lookup value using search validation

d-alan · Apr 1st, 2002, 03:04 PM

Formula needed?
1. I have a worksheet that is populated with invoicing information. (who, date, model, qty,$, etc…) called “saleslog” which is added to everyday.
2. Another sheet called “salesform” I would like to populate with information from “saleslog” .
3. “Salesform” is already set-up except for qty of each item ordered, who and date.
4. THE PROBLEM: In “saleslog” there are multiple rows for each order, representing each model ordered but they will have the same who and date, but on different dates they may have the same model.
5. I have set-up the “salesform” so it has the correct date and who but have not been able to fiqure out the formula to use to find qty to match model. As qty will always be within the same range as who and date.

How do I validate the vlookup so that it will search only the correct range of “saleslog”. Or is vlookup the correct function?
Any help or suggestion would be appreciate as this has taken up most of today trying different functions.
I will be doing this in vba, but would just like to get the formula working on the worksheet.
Thanks.
-D

d-alan · Apr 1st, 2002, 05:11 PM

I gave in and used an autofilter to sort the data, copy to new sheet and then run vlookup.
It works fine, however It is slow, and since this information is coming off of a userform it is populating a row of "saleslog" then sorting by the criteria and then populating another row until all are done.
My new question
How do you have one macro finish before another starts? I think this would speed up the process.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 1st, 2002, 03:04 PM	#1
d-alan Board Regular Join Date: Mar 2002 Posts: 119	Formula needed? 1. I have a worksheet that is populated with invoicing information. (who, date, model, qty,$, etc…) called “saleslog” which is added to everyday. 2. Another sheet called “salesform” I would like to populate with information from “saleslog” . 3. “Salesform” is already set-up except for qty of each item ordered, who and date. 4. THE PROBLEM: In “saleslog” there are multiple rows for each order, representing each model ordered but they will have the same who and date, but on different dates they may have the same model. 5. I have set-up the “salesform” so it has the correct date and who but have not been able to fiqure out the formula to use to find qty to match model. As qty will always be within the same range as who and date. How do I validate the vlookup so that it will search only the correct range of “saleslog”. Or is vlookup the correct function? Any help or suggestion would be appreciate as this has taken up most of today trying different functions. I will be doing this in vba, but would just like to get the formula working on the worksheet. Thanks. -D

Apr 1st, 2002, 05:11 PM	#2
d-alan Board Regular Join Date: Mar 2002 Posts: 119	I gave in and used an autofilter to sort the data, copy to new sheet and then run vlookup. It works fine, however It is slow, and since this information is coming off of a userform it is populating a row of "saleslog" then sorting by the criteria and then populating another row until all are done. My new question How do you have one macro finish before another starts? I think this would speed up the process.