Excel Data Validations

[SaM]

I have created a form in excel that users fill out to enter change requests
with various data. One of my columns has drop down lists that are specific to
a hidden sheet that pulls categories and reasons...

If it is this category, it lists this specific list of reasons...

If the user changes its mind on the category, how can I get the reason
selected removed from the cell then they click on a different category.
Currently that specific reason stays in the cell regardless that a different
category has been selected, until they click on a new reason - is there a way
to clear the reason cell when the user selects a different category?

 

[Rich Locus]

Hello SaM:
From your post, I couldn't tell if both the Category and the Reason were
combo boxes.

But, let's assume that both are combo boxes.

When the category experiences a "Change Event", then clear out the Reason
combo box.

Here's an example.

The user in this application clicks a fabric style, then they select a
fabric color based on the style.

When they click the style, I clear out the color as follows:

Private Sub cmbStyle_Change()

' ********************************************************
' The User Selected a Style From The Drop Down
' ********************************************************
If cmbStyle.Text = "" Or cmbStyle.Value = "" Then
Exit Sub
End If

strSelectedStyle = cmbStyle.Text
Me.cmbColor.Value = ""

Notice how I set the color combo box to null.
If this answers your questions, please click the "Answered" button.

And if I misunderstood your question, please let me know.

 

[GS]

SaM was thinking very hard :

I have created a form in excel that users fill out to enter change requests
with various data. One of my columns has drop down lists that are specific to
a hidden sheet that pulls categories and reasons...

If it is this category, it lists this specific list of reasons...

If the user changes its mind on the category, how can I get the reason
selected removed from the cell then they click on a different category.
Currently that specific reason stays in the cell regardless that a different
category has been selected, until they click on a new reason - is there a way
to clear the reason cell when the user selects a different category?

I do something similar with Expense Categories, Expense Subcategories,
where the value selected in the Expense Categories column determines
what displays in the Expense Subcategories dropdown. If the value in
the latter isn't found in the ExpenseCategories range after the
category is changed then ConditionalFormatting shades the subcategory
cell red. This is how I cue the user that something is wrong about the
action they just did, and so hints to now select the correct
subcategory associated with the category change. I found this to be a
lot more intuitive than to set up an event handler to monitor input
activity; -thay just use up resources and are generally a performance
hit if in a Change event. Since you already have the dropdowns working,
the CF is all you need if this satisfies your needs instead of a VBA
solution. Here's the formula I use in CF:
=InvalidSubcategory

Here's the defined name RefersTo for that:

=AND(Expenses!ExpenseCategory<>"",UPPER(Expenses!ExpenseCategory)<>"OTHER
EXPENSE",ISERROR(HLOOKUP(Expenses!ExpenseSubCategory,INDIRECT(SUBSTITUTE(Expenses!ExpenseCategory,"
","")),1,FALSE)))

Note that the only reason I use the SUBSTITUTE() function is because my
defined name ranges are all concatenated proper case derivitives of the
account name, and so the spaces are removed. (eg: "Office Supplies" as
an account display name is "OfficeSupplies" as the defined name for the
range that contains its subcategories. Otherwise, INDIRECT() will fail
if it tries to process the category with a space (or any other invalid
defined name characters).

The ISERROR() function handles if the subcategory doesn't belong to the
selected category, which if the case is what the HLOOKUP() function
will return.

The 'Other Expense' category doesn't have subcategories, so if it's
selected while something is in the subcategory cell I want it to turn
red as a cue to clear that cell.

And, of course, the Expense Category must already have been selected
for the subcategory dropdown to populate. How this works is as soon as
the user selects an expense category, the subcategories cell turns red
to cue the user that this is the next value to select.

HTH
Garry

 

[GS]

After serious thinking Rich Locus wrote :

Hello SaM:
From your post, I couldn't tell if both the Category and the Reason were
combo boxes.

But, let's assume that both are combo boxes.

When the category experiences a "Change Event", then clear out the Reason
combo box.

Here's an example.

The user in this application clicks a fabric style, then they select a
fabric color based on the style.

When they click the style, I clear out the color as follows:

Private Sub cmbStyle_Change()

' ********************************************************
' The User Selected a Style From The Drop Down
' ********************************************************
If cmbStyle.Text = "" Or cmbStyle.Value = "" Then
Exit Sub
End If

strSelectedStyle = cmbStyle.Text
Me.cmbColor.Value = ""

Notice how I set the color combo box to null.
If this answers your questions, please click the "Answered" button.

And if I misunderstood your question, please let me know.

I may be wrong but.., I think SaM was referring to a form on a
spreadsheet since he's refering to columns and asking about Data
Validation. I provided a worksheet DV solution in place of a VBA Change
event. Perhaps you could also suggest optional code in case it's a DV
dropdown in a cell.

regards,
Garry

 

[SaM]

Rich,

Both the cells with the Category & Reason have their own drop down list
created by going to Data -> Validation and in the Category Cell, the
validation is:
=Category
In the Reason Cell, the criteria in the Validation is a statement of:
=IF($B$65="Construction", Construction, IF($B$65="Design/Eng.", Design_Eng,
IF($B$65="Vendor", Vendor, IF($B$65="General Delays", General_Delays,
IF($B$65="Project Scope", Project_Scope, IF($B$65="Turnaround", Turnaround,
Pick_Category))))))

So I am not sure your solution would work in this case.

 

[SaM]

Garry,

I think that your solution would fit with this, I am somewhat skilled in
Excel, but not advanced, can you tell me how I can fit your:
~~~~~~~~~~~~~~~~
=InvalidSubcategory

Here's the defined name RefersTo for that:

=AND(Expenses!ExpenseCategory<>"",UPPER(Expenses!ExpenseCategory)<>"OTHER
EXPENSE",ISERROR(HLOOKUP(Expenses!ExpenseSubCategory,INDIRECT(SUBSTITUTE(Expenses!ExpenseCategory,"
","")),1,FALSE)))
~~~~~~~~~~~~~~~~~~~~~~~

In with my:
~~~~~~~~~~~~~~~~~~~~~~~~~~~
=IF($B$65="Construction", Construction, IF($B$65="Design/Eng.", Design_Eng,
IF($B$65="Vendor", Vendor, IF($B$65="General Delays", General_Delays,
IF($B$65="Project Scope", Project_Scope, IF($B$65="Turnaround", Turnaround,
Pick_Category))))))
~~~~~~~~~~~~~~~~~~~~~~~~~~~

Or would this go into my Category drop down list that simply states:
=Category

Thanks for your help!

 

[Rich Locus]

Sam:
If you question is just about formulas and not VBA code in Excel VBA forms,
it would probably be better to post it in the Formulas section of the forum.
Hope you find a solution.

 

[GS]

Hi Susan,
I apologize for the misuse of gender reference!

As Rich states, this isn't really a VBA topic but since we're here I'll
try to explain how to duplicate what I have done so you can better
relate to how to fit the concept into your project. If we need further
discussion I suggest we do that outside this NG (ie: via email? [-I
don't visit any other Excel forums]).

Firstly, I'd like to comment on your DV formula:
1/The resulting cell contents are hard-coded into your formula, and so
doesn't offer much flexibility if the list changes over time. This will
require that you update your DV accordingly.

The difference between this formula and mine is that mine refers to
defined name ranges, whereby any changes are automatically reflected in
the DV dropdowns. I recommend you do similar if you want your project
to be low-maintenance and flexible with change.
/1

2/The other difference about my formula is that it refers to the cell
to its left by defined name, NOT address. This is a column-absolute,
row-relative defined name that refers to a specific cell on the
worksheet that is located in the active cell's row under that column.
How it works is any cell in any row that uses the defined name
"ExpenseCategory" is always going to reference the cell in the same row
only under that specific column.

Also note that the range name is prefaced with the worksheet name
followed by '!', which indicates that the scope of the defined name is
local (sheet-level). This means it has unique meaning only to the
worksheet it's defined on, and so the same named range on the Summary
worksheet doesn't confuse it with its local defined name range
"ExpenseCategory".
/2

3/Your formula is being used in DV. Mine is being used in
ConditionalFormatting. What it's doing is looking at the value in the
ExpenseCategory column and determining if the contents in the
ExpenseSubcategory column (where this CF is applied) belong to that
expense category. If not, it shades the cell and font red to let the
user know something isn't right about the subcategory contents. Thus
the defined name "InvalidSubcategory" for the formula used.
/3

*/What's nice about using defined name formulas is that if it needs to
be revised you can do it in one place and all cells that use it will
automatically update. -Lots easier than having to redo the formula in
lots of cells<g>.
/*

Ok, now the fun part! At this point you should almost know what to
expect, but let's step through it:

a. All the columns in the UsedRange have headings.
b. Many of the headings are also the defined name used to refer to
those columns. For example: Expense Category is the heading for the
named range "ExpenseCategory". Its RefersTo is '=$F1', making it
column-absolute, row-relative. (The $ symbol prefaces the column only)

Next to it is the Expense Subcategory column which is named
"ExpenseSubcategory". It's RefersTo is '=$G1'.

At this point you should be starting to get an idea of my naming
convention, which should help you to better understand how to make a CF
formula that'll work for you. The key is what I explained in my
previous post: remove the space in the displayed value to get the
defined name of that cells position or the range reference we want. In
like manner, I use the same concept to name my global ranges that I use
in the DV dropdowns. So then, the DV formula for cells in my Expense
Category column is '=ExpenseCategories'. The DV formula for the cells
in my Expense Subcategories column is:

=INDIRECT(SUBSTITUTE(ExpenseCategory," ",""))

What it does is it looks at the value in the ExpenseCategory column,
removes any spaces, then returns the contents of the range named with
the result. So if the user selected 'Office Supplies' from the Expense
Category dropdown, the Expense Subcategory dropdown will automatically
update with a list of that category's members.

c. The global (workbook-level) ranges used in DV are located on a
hidden sheet named "Lists". This sheet contains all the 'system' ranges
for the project. How it works is similar to how the local names work as
described earlier. What's key to this is how the sheet is structured.
Here's the basic concept:

1. Expense categories are listed in column 'A' in a fully absolute
range locally (sheet-level) named "Expense_Categories", which includes
its heading and a dummy 'end' row which enables me to add/remove
categories without screwing things up. Its heading has the local
defined name "ExpenseCategory_Hdr". The global defined name used in the
DV on Sheets("Expenses") and on Sheets("Summary") is
"ExpenseCategories" as mentioned earlier, and its RefersTo is:

=OFFSET(Lists!ExpenseCategory_Hdr,1,0,COUNTA(Lists!Expense_Categories)-2,1)

What it does is it defines its address as starting one row below the
heading, and sizes it to be the number of rows in the fully absolute
range minus the heading and the dummy 'end' row. IOW, everything
between the heading and last row of Expense_Categories.

d. The subcategories are handled differently in that they are fully
absolute ranges, but they don't have a dummy 'end' cell. These run
horizontal to the Expense Categories, and each expense category is the
heading for the subcategories (members) list beside it.

The same naming convention is used here. Each expense category display
name is used to define the global range names of the subcategories. For
example: The expense category named 'Office Supplies' has a defined
name range for its members of "OfficeSupplies". It's RefersTo is:
'=$B$24:$D$24'.

The last cell in each subcategory is used in place of the dummy 'end'
cell used to define the Expense_Categories range. It's a default used
in every subcategory, named by formula:
=ExpenseCategory&": Other"

Here's an example where another sheet is using that same defined name
we used on several other sheets already. It might be confusing at first
but once you start using defined names for ranges and formulas it gets
easier as you go. Here's an overview of the naming convention I use,
which I hope will be helpful to you.

Expense_Categories, ExpenseCategory_Hdr
These use an underscore because they defined fully absolute ranges that
refer to locations. (Follows same concept as Excel's Print_Area)

ExpenseCategories, OfficeSupplies
Plural form is used to refer to lists.

ExpenseCategory
Singular form is used to define a column position on a worksheet.

uiProgRows, ptrCellAbove, vTax1_Rate
Lowercase prefixes are used to define various elements of a project:
ui refers to a stored setting that the sheet uses at runtime.
ptr refers to a position pointer for fully relative defined names.
v refers to a stored value. (akin to a constant used in code)


And this is finally how the DV and CF are set up to work together as
described. It's a lot to digest but if you take it one step at a time
you should manage it just fine. I'll be here if you need help along the
way.

HTH
Garry