Dynamically Applying cascade Data Validation to columns

This one drove me crazy. Pivot table filters are very picky when it comes to numbers and dates. It wants the format EXACTLY as it is shown in the source data. Unfortunately, Excel wants to help you out, so when you select 1.900 as a drop down it converts it into a number 1.9 if you build pivot table using the data you get different results if you use 1.9 or 1.900 for a filter. Formatting the dropdown cell as text still yields 1.9. So the solution for this is "hard coded." I noticed that the format of the OD column is #.000 and of the Wt column #.00. Formatting these cells in code: TargetVal = format (target.value,"#.000") isn't enough you need to convert it into a string: TargetVal = format(target.value,"#.000")&"".

Sheesh!

Once I figured that out, I was able to proceed. I use pivot tables to do the heavy lifting.

The first pivot table has a unique listing of all the OD values. The second pivot table has a list of weights for the selected OD value and so on. I overlaid each pivot table result with a named dynamic range and used these for the dropdowns.

There is a change event on the CsgData page, so when you select an OD, it sets the filters of OD on the next 3 pivot tables. When you select a Wt it sets that value on the next two pivot tables and so on.

I think I got this working. The issue I would have is if the format for OD and Wt were inconsistent. I don't know of any way to determine how many zeros are supposed the be trailing after Excel helps me out by dropping them.

So I just hung up the phone and said, 'I wish I had said." I pressed on a bit after this posting.

I have a way of finding the format for a selection:
I select the value in the drop-down box and Excel will drop the trailing decimals. I then use Match to find the first occurence of this value in the data. I than use index to find the cell and run a UDF on it.

=GetFormat(INDEX(tbl_Src[OD],MATCH(t_DV[@[S_OD]],tbl_Src[OD],0),1))

Please Login or Register  to view this content.

In this case it yields 0.000.

You are correct. The validations only cascade from left to right.