Data Validation
Data validation in Excel is a feature that allows you to specify restrictions on the types of data that can be entered into a cell or a range of cells. It helps you to control the input and ensure that the data entered meets certain criteria, such as limiting the number of characters, values from a list, or a specific range of numbers.
Name Manager
Name Manager in Excel is a tool that allows you to manage and edit named ranges, formulas, tables, and other objects in your Excel workbook. It provides a central location to view, modify, and delete named ranges and other named objects.
Indirect Function
The INDIRECT function in Excel is a reference function that allows you to create a reference to a cell or range of cells indirectly, based on the text value of a string. This can be useful when you need to dynamically reference cells in your worksheet based on certain criteria or conditions.
Goals
Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.
Task
Make a list with six options - Arizona, North
Carolina, Colorado, Washington, Massachusetts
and Michigan - in the cells of column AG, which
corresponds to the States.
Make a list of values in the cells of column AH,
which corresponds to the city, taking into account
the value in the neighboring cell in column AG.
First, For the state 2021 Ranking table. I selected Y4:AD4 and named it on the Name box as TopStates (with no space following naming convention)
For the State Column in AG5:AG17 , Using list data validation. I used F3 to show named items, then I selected the Topstates earlier created in Step 1.
In order to create city ranges from State I used the shortcut ALT+M+C to (of the state - range Y4:AD17) from selection having higlighted the cell.
It is noted that the names have created automatically. Considering the naming convention, the Names with spaces will be joined by underscore automatically.
For the city (AH5:AH17) data validation, It will rely on the State Selection, Hence the need for the INDIRECT function.
However, there’s a tweak in the formula the earlier state -Topstates range names does not have underscore, the states with more than one names has space. So, there’s need to substitute the space with Underscore VALIDATION FORMULA =INDIRECT(SUBSTITUTE(AG5," ","_"))
Define Named Ranges for the list of states
a) Select the region “Y4:AD17”
b) From Command bar select “Formula | Create from Selection”
c) In the Pop-up window select “Top row” only
d) Result: From Command bar select “Formula | Name Manger”. You can see named lists created for each state
Create Data valiation for States selection
a) Select the region “AG5:AG17”
b) From Command bar select “Data | Data Validation…”
c) From the “Allow” drop down select “List”
d) Click on the entry field under the heading “Source” and select the region “Y4:AD4”
e) The entry field show “=$Y$4:$AD$4”
f) Click on “OK”
Create the Cities selection based on selected state
a) Select the region “AH5:AH17”
b) From Command bar select “Data | Data Validation…”
c) From the “Allow” drop down select “List”
d) Click on the entry field under the heading “Source” and enter the following formula: =INDEX(INDIRECT(SUBSTITUTE(AG5," ","_")),AF5)
Remarks:
- names of ranges can not have spaces
- in step 1 the name manager replaces " " with "_" (i.e. North Carolina's name space is "North_Carolina")
- SUBSTITUE is used to replace " " with "_" to be able to select the right named range in the INDIRECT function
- INDEX is used to the select the Nth city (based on column AF) in the named range for the selected state
@IlgarZarbaliyev, I agree that it is one of the best functions. But Modern Excel doesn’t require us to use it anymore in many instances.
I agree many won’t agree with me, but we need to come out of the old practices.
For instance, in the current dataset, I have to arrange the data first. Then I have to make named ranges for every column. What if there were 50 of them? It would be a too time-consuming process.
Instead, I will fetch data with Power Query.
Then I will use
= XLOOKUP(first match):XLOOKUP(last match)
I am saying this only for the 2 levels dependent drop-downs. What if I require 3 levels? Using INDIRECT would be a very lengthy process.
Agree.
However, The INDIRECT function in Excel is an important tool that can be used for a variety of purposes.
If you can solve this problem with a faster and easier method, that’s much better.
Thank you for participating in the Excel Challenge related to Name Manager, Data Validation and Indirect Function! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to perform conditional summation in Excel.
Select AG2:AG17 range
From Data Tools group, click on Data Validation on Data Tab
Select List from Allow Text Text Box
Select Y4:AD4 range
Click OK
Pressing Ctrl button select Y4:Y16, Z4:Z13, AA4:AA17,AB4:AB13, AC4:AC13 and AD4:AD11 ranges
Click on Create from Selection from Defined Names on Formulas tab
Check only Top Row and hit OK
Select AH5:AH17 range
From Data Tools group, click on Data Validation on Data Tab
Select List from Allow Text Text Box
Enter “=INDIRECT(SUBSTITUTE($AG8," “,”_"))”
Pressing Ctrl button select Y4:Y16, Z4:Z13, AA4:AA17, AB4:AB13, AC4:AC13 and AD4:AD11 ranges
Click on Create from Selection from Defined Names on Formulas tab
Check only Top Row and hit OK
Select AG5:AG17 range
From Data Tools group, click on Data Validation on Data Tab
Select List from Allow Text Text Box
Select Y4:AD4 range
Click OK
Select AH5:AH17 range
From Data Tools group, click on Data Validation on Data Tab
Select List from Allow Text Text Box
Enter “=INDIRECT(SUBSTITUTE($AG5," “,”_"))”
