Excel Workout 5 - Name Manager, Data Validation & Indirect Function

Welcome to Excel Workout #5!
Difficulty Level:

This week’s challenge is designed to test your knowledge on Name Manager, Data Validation and Indirect Function.

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.

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.


  1. 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.

  2. 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.

Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

This workout will be released on Monday April 10, 2023, and the author’s solution will be posted on Sunday April 16, 2023.

Challenge #5.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

HI @IlgarZarbaliyev

You have no file.


Check now, please

Quadri Atharu Challenge #5 - Solution.xlsx (8.3 MB)

  1. 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)
  2. 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.
  3. 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.
  4. It is noted that the names have created automatically. Considering the naming convention, the Names with spaces will be joined by underscore automatically.
  5. For the city (AH5:AH17) data validation, It will rely on the State Selection, Hence the need for the INDIRECT function.
  6. 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
1 Like

It’s not like I am against using the Indirect Function, but I don’t like that function.

We don’t need to use the name manager and the INDIRECT function now. You can use the power of the Cell Reference nature of the XLOOKUP function.

Formula used:

=LET(_c, XLOOKUP(AG5, $Y$4:$AD$4, $Y$5:$AD$17), _r, TAKE(_c, SUM(--(_c <> ""))), _r)

Use the data validation carefully. If you don’t select the active cell as AH5, it would give incorrect results.

Challenge #5.xlsx (8.3 MB)

1 Like

nice challenge
keep on coming with these nice ideas

great work


Challenge #5 solution.xlsx (8.4 MB)

1 Like

Interesting challenge.
Below is my solution.


Challenge #5 Solution Pietro.xlsx (8.3 MB)


Solution (on Excel for Mac Version 16.72)

  1. 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

  2. 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”

  3. 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:

     - 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

    f) Click on “OK”

1 Like

INDIRECT is one of the good functions:)

Another Excel muscle of a volatile function not very used.
Here is the file.
Challenge #5.xlsx (8.6 MB)


1 Like

@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.

1 Like

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.

1 Like

Challenge #5_Solution.xlsx (8.3 MB)

Hi Team,

Please find my Solution as per desired methods -

States Column Formula: =INDIRECT(“States”)

Cities Column Formula: =IF(AG5=“North Carolina”, INDIRECT(“NorthCarolina”),INDIRECT(AG5))

1 Like

Here is my file.


Challenge #5.xlsx (1.7 MB)

1 Like

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.

Here is my solution to Workout #5:

  • 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," “,”_"))

    Challenge #5 with Solution.xlsx (8.4 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

Created Named Ranges by manually selecting the Cities for each state.
Create a Named Range called States, by including all the headers

Data Validation for Cities:
(To adjust the formula for North Carolina)

Here’s my submission:
C5_NamedRanges&DataValidation_Homesh.xlsx (46.8 KB)

1 Like