How can I get around the DV drop down list limit

**concretetsunami** · 08-07-2009, 05:25 PM

Here is my problem.... in the attached spreadsheet I have multiple sheets. On the tab "Tech IDS", column A is named "Name" and column B is the tech ID that is associated with the name in column A. Column B is validated as List=Name. Now under the tab "CAL Form" under Column A I need to type the tech's name. Now under the tab "CAL SHEET TEMPLATE" the name that I typed corresponds with a given cell (i.e. B2='CAL Form'!A2). Notice though that I2 does not give a tech id b/c of DV dropdown list meeting its limit. Look at I68, Bob's id appears b/c he fits within the limits. My question is... how can I still use the list with more names or can I modify the formula (=INDEX(ID,MATCH(B68,Name,0))) to include another Match search?

**Chance2** · 08-07-2009, 05:57 PM

You could modify your List = Name to refer to:

Code:

=OFFSET('Tech IDs'!$A:$A,0,0,COUNTA('Tech IDs'!$A:$A),1)

Which would grow/shrink depending upon the number of Techs in column A.

**concretetsunami** · 08-07-2009, 06:01 PM

So would I just go to the Validation menu and change List=Name to List=OFFSET('Tech IDs'!$A:$A,0,0,COUNTA('Tech IDs'!$A:$A),1)?

**Chance2** · 08-07-2009, 06:35 PM

Under INSERT --> NAME --> DEFINE you should see your lists of "Names in workbook".

Find "Name" and select it, the REFERS TO box will have a formula.
Replace that formula with the one I gave you and ADD and CLOSE the dialog box.