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).
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).
I used this sample data from data.world. 20 total columns and 399 rows.
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:
Step 3 – Counter
I used dynamic array function SEQUENCE to create a counter in cell B6:
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).
Step 5 – Add Drop Down List
I used OFFSET inside data validation to create the list. CELL & OFFSET are both volatile functions.
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.
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.
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?
That’s a good point. I do have the FILTER function.
Thank you for visiting my blog!