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
|