Indirect dropdown not working because of the Offset formula?

I am stuck with getting my indirect dropdown list to work. I have excel 2010.

I have 2 sheets.

Sheet 1 is titled: Setup
Sheet 2 is titled: Detections

In Sheet 1 are the following:

Rows A672-A781 I defined as Species_category

In its defined name are:

Name: Species_category
Refers to: =OFFSET(Setup!$A$672,0,0,COUNTA(Setup!$A$672:$A$781),1)

The list within Species_category will serve as the dropdown list in cell D7 in a sheet titled: Detections
This list is composed of the following: Whale, Dolphin, Turtle, Fish, Pinniped, Crustacean, Bird, Sirenia, Other

The range per defined name are in the following cells in the sheet titled: Setup

Cells B672-B781= Whale
Cells E672-E781= Dolphin
Cells H672-H781= Pinniped
Cells A784-A837= Turtle
Cells B784-B837= Fish
Cells E784-E837= Crustacean
Cells H784-H837= Sirenia
Cells B840-B950= Birds
Cells E840-E950= Other

The reference per defined name is the same offset formula with their exact cell numbers. For example, for Whale it is =OFFSET(Setup!$B$672,0,0,COUNTA(Setup!$B$672:$B$781),1)

My primary dropdown list in Detections cell D7 works following this formula under Data Validation:

Allow: List
Source: =Species_category

However, my indirect dropdown list in Detections cell E7 is not working. I typed in the formula under Data Validation:

Allow: List
Source: =Indirect(D7)

I am stuck and tried many other formulas and none seem to work. Is it because of my Offset rule? Should I change the formatting somwehere to make this indirect dropdown list work and yet not make blank cells appear in my dropdown list? I want my dropdown list to be dynamic incase additional data needs to be added yet I don't want blank cells to appear.

I've attached a copy of my excel workbook here so that you can understand what I am talking about and can see my formulas and know exactly what the situation is.

Welcome to the forum.

Please update your forum profile to say Excel 2010. Do this NOW.

The workbook attached to post #1 has some strange behaviors so I copied the data to another workbook.
1. Rearrange the species headers into a horizontal arrangement
2. Select the values in each column of species data by pressing the Ctrl, Shift and the down arrow keys
3. Press Ctrl, Shift and the F3 keys and when prompted choose to use the top row as the defined name
Note that if you make adjustments to the data in a species column you can use steps 2 and 3 to quickly change the values that will show up in the dropdown.
Let us know if you have any questions.

Try the attached it goes some way towards been dynamically self-maintaining.
The table headers are used to populate the categories and the sub-categories are determined by the two 'helper' named ranges (CatColNum & SubCatCol)
Open up the 'Name Manager' you will see the interlinking formula - as these are 'structured table' references these dynamically expand/contract with addition/subtraction of rows and columns from the table.
Similarly the data entry table DV expands with the addition of rows (put a single character in 'A3' tab to 'B3' and back to 'A3' your table has expanded and the DV has also).

VBA offering:

Please Login or Register  to view this content.

Creates a DV list for any cell selected in Column A: see attached

code in sheet "Detections" : right-click on tab >> "View Code" >> Copy/paste above code

My bad, in the CatCol I used a range reference, the technique only works dynamically if the 'structured table' references are used - I have altered it to #Data in the new uploaded file.

You're welcome and thank for the rep.

Glad to have helped - thanks for the added rep point.