Hi Jeff, there's a bit of a description of dynamic range names on
my site (see "4 Sure-Fire Ways to Build Efficient Excel Reports").
Once you have your range name, select the Data Validation, select List from the Allow drop-down, and enter your range name for the source. Be sure to use =lstValues for the range name lstValues (for instance), not just lstValues.
Lookups (such as VLOOKUP or HLOOKUP) are great for small workbooks, but will bog down performance for larger workbooks. Use a combination of MATCH and INDEX instead of lookups in those cases.
An additional benefit of dynamic ranges used in conjunction with data validation is that the source data for the validated listed need not reside on the same worksheet, which allows for much more flexible and easier to maintain designs.
Post back if you have further questions.
Thanks Krunch
came up with this per your examples and works as stated.
=IF(MATCH(K31,DEALERLIST,0),IF(ISNA(K28),"",INDEX(DEALERRANGE,K28,2)))
Next on to dynamic ranges!
I've taught myself more than basic excel over the last couple years at work as time allows, and it all works, but I have no idea if I am doing it "right".
I've got many workbooks with thousands of VLOOKUPS (they all seem to save in 10-15 seconds. Will probably take about a full day for each workbook to update with ATCH/INDEX and test. But if you say it's worth it, I'll take the plunge.
And yes many formulas with double VLOOKUPS in each calulation)
Example:
=IF($G2="",0,(VLOOKUP($G2,BASERANGE,5,0)*$H2)+IF($I2="",0,(VLOOKUP($I2,BASERANGE,5,0))*$J2)+IF($K2="",0,(VLOOKUP($K2,BASERANGE,5,0))*$L2))
I also use IF/AND/OR alot, any better options for those?
Example:
=IF($F2="","",IF($F2="Oval",3.1416*(SQRT(2*((POWER($CE2,2)+(POWER($CF2,2))-((POWER(($CE2-$CF2),2)/2.2)))))),0))
=IF($M2="","",IF(OR($M2="Bevel",$M2="Waterfall"),BA2-1,IF($M2="Shaped",BA2-0.625,BA2)))
Thanks Again
Jeff
