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

Download my Excel file **here** (demo data from data.world).

**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

Official list from Microsoft (**link**).

**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?

**About Me**

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.

If you’re using the SEQUENCE function, then don’t you also have access to the FILTER function instead of having to use INDEX-SMALL-IF?

Hi David,

That’s a good point. I do have the FILTER function.

Thank you for visiting my blog!

Cheers,

Kevin