Data Validation Macro Modification for Excel
Closed
Newb
-
Oct 29, 2011 at 11:13 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2011 at 09:11 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 8, 2011 at 09:11 AM
Related:
- Data Validation Macro Modification for Excel
- Spell number in excel without macro - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Download macros for excel - Download - Spreadsheets
- Display two columns in data validation list but return only one - Guide
- Tmobile data check - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 1, 2011 at 11:22 AM
Nov 1, 2011 at 11:22 AM
Hi Newb,
For the use of two dropdown lists linked together, there is no coding necessary. Hopefully this will give you more freedom to adjust it to your needs.
A1: Main list
A2: a
A3: b
B1: List a
B2: aa
B3: aaa
C1: List b
C2: bb
C3: bbb
Create first dropdownlist:
A4: Date validation; List; formula: =$A$2:$A$3
Now select B2:B3 and goto the field to the left of the formula bar and type in a.
Now select C2:C3 and goto the field to the left of the formula bar and type in b.
Create second dropdownlist:
A5: Date validation; List; formula: =INDIRECT($A$4)
Best regards,
Trowa
For the use of two dropdown lists linked together, there is no coding necessary. Hopefully this will give you more freedom to adjust it to your needs.
A1: Main list
A2: a
A3: b
B1: List a
B2: aa
B3: aaa
C1: List b
C2: bb
C3: bbb
Create first dropdownlist:
A4: Date validation; List; formula: =$A$2:$A$3
Now select B2:B3 and goto the field to the left of the formula bar and type in a.
Now select C2:C3 and goto the field to the left of the formula bar and type in b.
Create second dropdownlist:
A5: Date validation; List; formula: =INDIRECT($A$4)
Best regards,
Trowa
Nov 6, 2011 at 11:44 AM
I agree. If I could avoid using a macro I would be able to customized much more easily. I have tried to implement the method you suggested however, the second drop down list evaluates to an error (a dialog box pops up saying something thing of that effect when I enter the INDIRECT formula in the field). The second drop down tab is still there, but the drop down list itself does not display any options.
From a high level view, I am assigning a name to the contents of each list. The data validation 1 (DV1) is calling x number of lists for display. DV2 is calling DV1 for information, but I am not seeing how it is doing that. Can you elaborate on how INDIRECT works?
Newb
Nov 8, 2011 at 09:11 AM
To understand how indirect works perform this test.
1. Put a value in A1 like "abc".
2. Put the cell reference you would like to use in A2. In this case the cell reference is A1, so put the value "A1" in cell A2.
3. Use cell A3 to input the formula: =Indirect(A2).
The formula will now look at cell A2, which contains the value "A1", thus displaying the value "abc" of cell A1.
Now you can change the names of the cells using the field to the left of the formula bar.
1. Select cell A2. Left of the formula field there is a field saying the name of the field is A2. You can change this to i.e. List1.
Cell A2 is still A2, so the formula will still work. But cell A2 is also called List1, so the formula in cell A3 can now be changed to =Indirect(List1) to yield the same result.
This technique is used for multiple data validation lists.
Your first data validation list contains a number of values, when these values are also named ranges (like List1), the indirect formula will display diffrent lists depending on the value selected from the first data validation list.
Hopefully this will shine some light on the issue for you to solve your query.
You can also consider the option to upload your file using a filesharing site like www.speedyshare.com for a more specific solution. If you do then make sure the extention of your file is .xls since I'm still using Excel 2003.
Best regards,
Trowa