Creating Dynamic Validation Ranges

Click & Solve » Creating Dynamic Validation Ranges

Click & Solve » Creating Dynamic Validation Ranges — Fri, 06 Mar 2009 02:23:17 GMT

PingBack from http://www.clickandsolve.com/?p=18503

re: Creating Dynamic Validation Ranges

Juan Cruz Soares Gache — Fri, 06 Mar 2009 16:08:09 GMT

We did this few weeks ago and worked perfectly well. The main issue was trying to change the value of the 'city' cell when the 'country' item is changed for another.

We couldn't find another way to do this than VBA.

Good work, fellows!

re: Creating Dynamic Validation Ranges

Ron M — Sat, 07 Mar 2009 04:31:13 GMT

Your attachment is an Excel 2007 file (xlsx) format. Can you make this available in .xls format for those of us who do not have Excel 2007?

re: Creating Dynamic Validation Ranges

Mic — Sun, 08 Mar 2009 21:50:52 GMT

Anothwer way could be using OFFSET, MATCH and INDEX formulas.

Not always optimum but useful.

1. Define DbRng as $G$3:$J$9

2.Use OFFSET(DbRng;1;MATCH(B13;INDEX(DbRng;1;);FALSE)-1;ROWS(DbRng)-1;1)

in Step 6. Datavalidation

re: Creating Dynamic Validation Ranges

sam — Mon, 09 Mar 2009 05:07:44 GMT

1) Ctrl+Shift+F3 = Create Names

2) Dont create Static Names, Use Dynamic names (Index/Counta())

3) Dont use Indirect, It is volatile and does not work with Dynamic names -Use Evaluate

re: Creating Dynamic Validation Ranges

Mike Rosenblum — Thu, 12 Mar 2009 00:09:34 GMT

Hey Dany,

Really nice solution. I've done this kind of thing before, but not using =Indirect() like this, which is really clean. I wish I thought of this myself...

Sam, I don't think that being 'volatile' applies here b/c validation conditions are only evaluated when the user attempts to change a value (or use the validation drop-down list). Defined names are not volatile the way that standard formula are; in fact, unless another formula on a worksheet references a defined name, it is never calculated at all, even if the defined name references something else that is recalculated.

re: Creating Dynamic Validation Ranges

sam — Fri, 13 Mar 2009 05:28:19 GMT

Mike.

If you define a dynamic Name using Offset/Counta then the name gets recalculated every time a event takes place which triggers a recalc

Index/Counta makes it non volatile.

Indirect doest not work with dynamic names (Its blind to both Index and Offset)

Hence the good old XLM Evaluate

re: Creating Dynamic Validation Ranges

jules — Thu, 02 Apr 2009 03:19:26 GMT

I tried it it worked oerfectly in rows b and c but not in other rows. what is the purpose of the cells b12 and c12

re: Creating Dynamic Validation Ranges

jules — Thu, 02 Apr 2009 03:25:33 GMT

I tried it it worked oerfectly in columns b and c but not in other columns. what is the purpose of the cells b12 and c12

re: Creating Dynamic Validation Ranges

General Ledger — Fri, 03 Apr 2009 05:18:46 GMT

I think having Country update upon any change in Region is important. The whole point of all this work is to limit (validate) the choice of Country based on the selection of Region. If you select a Region and Country, and then change the Region, your selection of Country is no longer valid. Your prior choice of Country should be cleared as soon as you change the Region. How can you make this happen?

I am not an expert so please be explicit.

re: Creating Dynamic Validation Ranges

General Ledger — Fri, 03 Apr 2009 05:19:04 GMT

I am not an expert so please be explicit.