multi sheet lookup with multiple results
Hi, I am trying (unsuccessfully) to create a lookup sheet in a workbook. The data that the user will lookup is stored on 2 sheets within the workbook. They are; 1 - A Customer List that simply lists Company Name, Address, contact details etc. 2 - An enquiry List that lists any enquiries recieved from customers. There is a common field in the 2 lists of a unique customer number. I have encountered 2 problems that are confusing me at the moment. Problem 1 - How do I create a dropdown list on my new lookup sheet that shows The Company name (the way that a user will search) on a different sheet to the data? If I try and use Validation/List it tells me that the list has to be on the same sheet as the lookup cell. Problem 2 - Once the correct customer has been selected how do I get a list of all enquiries (there may be several) relating to them to appear on the lookup sheet. I have been dabbling with vlookup but without much success... -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=521041 |
multi sheet lookup with multiple results
Alec
For Problem 1: Give the range a name (Insert|Name|Define... or select the range, go to the Name Box -left of the formula bar- and type its name). Then, in the DV dialog box, for the range enter: =the name you have chosen Problem 2: This is a typical case for a filter, Autofilter or Advanced (Data|Filter). However, it can also be done with formulas: Assuming your enquiries occupy columns A:B, A:A contains the customer key and B contains the enquiry. Then, in your new sheet, assuming the cust.code has been selected in A1, and you want the enquiries to appear in B1:Bwhatever, you can use: in B1: =INDEX(Sheet1!B1:B1000,MATCH(1,(Sheet1!A1:A1000=A1 ),0) in B2: =IF(ISERROR(MATCH(1,(Sheet1!$A$1:$A$1000=A1)*(COUN TIF($B$1:B1,Sheet1!$A$1:$A$1000)=0),0)),"",INDEX(S heet1!$B$1:$B$1000,MATCH(1,(Sheet1!$A$1:$A$1000=A1 )*(COUNTIF($B$1:B1,Sheet1!$A$1:$A$1000)=0),0))) Notes: -These are both *array formulas*. They must be committed with Shift+Ctrl+Enter. -Change the ranges Sheet1!B1:B1000, Sheet1!A1:A1000 to whatever sizes needed. -Copy the formula in B2 long enough so that there will always be enough formula cells for your enquiries. HTH Kostis Vezerides |
All times are GMT +1. The time now is 11:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com