Help Selecting A Function

[Leslie]

I have two worksheets in a single workbook. My fisrt worksheet contains
claim numbers and my second worksheet contains a link file listing all claim
entities and sorted by claim number. There are multiple claim entities per
claim. I am trying to figure out how I can use the vlookup to find all the
entities for a particular claim and then allow the user to select from the
vlookup which one they want to use.

worksheet 1
CLAIM NO
104068

worksheet 2
CLAIM NO ENTITY
2387 Smith, John
183778 All State
183778 Johnson, Alisa
104068 Farm Bureau
104068 Goodloe, Milford
104068 Thompson, Nancy

Using the above example, when the user enters 104068 on the first worksheet,
I need a function in the next cell to possibly show the user all the entities
from worksheet 2 that match that claim number and then let the user decide
which one of the entities to pull in.

 

[Pete_UK]

What is the maximum number of entries in sheet2 that you might have
for any one claim? 10? 20?

If these are then displayed in Sheet1 for the claim number entered,
what do you want to happen then? I don't understand what you mean by
"... let the user decide which one of the entities to pull in ..."

Pete

 

[Gaurav]

Assuming you are entering the claim number in Sheet1 cell A1.

In Sheet2, claim numbers in A1:A100 and names in B1:B100

enter this formula in Sheet1 B1

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet2!A$1:A$100,A$1),INDEX(Sheet2!B$1:B$100,SMALL(IF(Sheet2!A$1:A$100=A$1,ROW(Sheet2!B$1:B$100)-MIN(ROW(Sheet2!B$B:B$100))+1),ROWS(A$2:A2))),"")

Press CTRL+SHIFT+ENTER and not just ENTER. Drag it down according to the
maximum number of possible Entities.

This should help.

 

[Leslie]

I tried your formula, but it did not let me choose any record. It polulate
my cell with the first entity that matched my claim number.

Is it possible to code like a combo box in my cell with only the records
that match my claim number?

 

[Gaurav]

I dont know the code but this may be a workout....enter this formula in B1
and drag it all the way down..say till B50. then select C1 go to DATA |
VALIDATION | select LIST and then enter B1:B50 in the box. Press OK. Now
Hide column B.

Does that do what you want?

 

[Leslie]

I would do that, but that is not going to give me what I am after.
Using your suggestion, selecting say B1 to B50, I only want the records that
match my claim number. Table has multiple records per claim, on each claim
is a new entity name. I want to populate a list using data validation to
only return the entities with matching claim number.

 

[Gaurav]

That is what it will do.

with your claim number in A1, enter the formula i suggested in B1 and drag
it down. It will pull all the entities matching the claim number you enter
in A1 and then use the data validation to create the list.

(Drag the formula down according to the maximum possible entities per claim.
else you will see a lot of blank space in your drop down)

 

[Leslie]

you are right and it did work. How would this same validation be done using
"defined names" in 07. My entities table is a linked table on the as/400 so
it will change all the time. I have created names for my formulas and
everything work manually except the data validation.

my names are defined as such:
myclaimno = Table2[[#This Row],[Claim No]]
systemclaimno = Table_S1019200_CTCLM01[#All]
allentities = Table_S1019200_CTCFO01[#All]
entitylookup = VLOOKUP(myclaimno,allentities,myentities,false)

I have tried a couple of different sceniaros and none have work so far.
Data validation source is: =INDIRECT(entitylookup) Doesn't work
=entitylookup Doesn't work
entitylookup Doesn't work
Using the three sources above in conjunction with checking and unchecking
'ignore blanks', i'm not getting it to work.