Morning (for me anyway)
I've got a problem with setting up dependant validation lists which use dynamic ranges.
I've nearly solved it with the brilliant information found on www.contextures.com, but i have an issue which i can't find an answer to anywhere.
I've followed the guide to a tee and the dependant validation works, my problem is that my 'Data' tab (Where the dynamic ranges are) is filled with formulas, pulling cells through from a central workbook. So, for example, 'Data' tab 'A1' has the formula:
=[Central Workbook.xls]Data'!A1)
This formula is replicated across A1:S30.
I've done this so i only need to update the Central Workbook, as i have many different workbooks which will need this page with the dynamic ranges.
As A1:S30 on the Data tab is filled with formula, the dependant validation dynamic range counts all the cells, as it see's the formula as data. I've tried ISBLANK, which removes the '0's and makes blank cells blank, but the dynamic range still see's the cells as having content.
I'm using this formula in the dependant validation column (typed in the validation window):
=OFFSET(INDIRECT(SUBSTITUTE($D3," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($D3," ","")&"Col"),-1),1)
As i said above, it works but it counts blank cells which contain formula.
I know that MATCH should sort it (instead of COUNTA?), as this is how i've set up the other dynamic ranges which do not need to be dependant .... unfortunately my knowledge isn't good enough to work it out ..
help please ...
Cheers, Kev
Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
You didn't state in a single sentence what your problem is. How to remove blanks from a range of values being used as the source of a dynamic validation list? Is that it?
If so, you would need to create a secondary list that used array formulas to only depict the non-blank values from the first list into the second list. Then make your dynamic range from the second list.
Warning, dynamic ranges usually cannot be used in conjunction with an INDIRECT() Data Validation function. They can be used directly in Data Validation, but not indirectly.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Ok, i'll try and explain it better.
I've attached two workbooks (Test and Central Workbook)
Test: The 'List' tab pulls an exact copy of the 'List' tab on the 'Central Workbook'
In a nutshell "How to remove blanks from a range of values being used as the source of a dynamic validation list? Is that it?" is correct.
Have a look at the formulas and ranges in my attachments, it's easier than explaining it i imagine.
Cheers
Like so:
=OFFSET(INDIRECT(SUBSTITUTE($B2," ","")),0,0,COUNTIF(INDIRECT(SUBSTITUTE($B2," ","")&"Col"), ">a"),1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks