You may never need this…but I did recently and it’s just fun knowing that Excel can solve this!

### Requirement

Drop down list must contain column headers for columns that have currency datatype. If datatypes change the drop list should also change

Last week I would have said that it’s not possible….but it is possible!

I used it as part of a DSUM solution (choose currency column to sum).

### The Data

I used this sample data from data.world. 20 total columns and 399 rows.

### The Solution

#### Step 1 – Extract Datatype

CELL function extracts datatype from first row of data (assuming entire column is the same).

CELL function doesn’t seem to work as an array so I referenced each cell one by one.

What do the codes mean?

• a code starting with a “C” (cell G1) means currency
• a code starting with an “F” (cell A1) means number

#### Step 2 – Count Them

Initially I used an array in cell C4 to count datatypes starting with “C” but I simplified it to this:

=COUNTIF(\$A\$1:\$T\$1,C5&”*”)

#### Step 3 – Counter

I used dynamic array function SEQUENCE to create a counter in cell B6:

=SEQUENCE(C4,1,1,1)

It spills down only as far as necessary.

#### Step 4 – Column List

Below, the # in B6# is used to spill formula down alongside the counter. No need to drag formula down manually (it automatically extends).

=INDEX(‘blockbuster-top_ten_movies_per_’!\$A\$1:\$T\$1,SMALL(IF(LEFT(\$A\$1:\$T\$1,1)=C\$5,COLUMN(‘blockbuster-top_ten_movies_per_’!\$A\$1:\$T\$1),””),B6#))

#### Step 5 – Add Drop Down List

I used OFFSET inside data validation to create the list. CELL & OFFSET are both volatile functions.

=OFFSET(\$C\$6,0,0,\$C\$4,1)

#### Validate Solution

To double check the solution I also created a drop down list for column headers that have number format. Columns ‘adjusted’ and ‘worldwide_gross’ are formatted as numbers.

Note: if you change a column’s datatype (value in row 2 of sheet ‘blockbuster-top_ten_movies_per_’) you’ll need to press F9 key to refresh the calculation.

### Summary

After I downloaded the sample data I manually set each column to a specific datatype (as many of them were initially set to “G” for general).

As I mentioned, you may never need to do this but it was fun knowing that Excel can solve this with a few simple formulas. I used this as part of a solution involving DSUM function (hopefully my next post).

To think about: how can I use TYPE function?

Some are described as being a hopeless romantic. I’m not. I’m more of a hopeless Excel fan. I will never obtain true Excel nerdvana but I’ll pursue it forever 🙂

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.