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.