Data Validation - Ignoring numbers or skipping columns in Drop Down List - Excel

Question

I have 2 excel sheets in one excel file, which are:

summary and media

What I want to achieve is that

the media sheet can display a drop down menu from the data in summary sheet but at the same time, skip numbers/digits in summary sheet.

Here are the details

In summary sheet, it has following values

A1 = Home
B1 = 22
C1 = Media
D1 = 92
E1 = Reviews
F1 = 111
G1 = Contact
H1 = 84
I1,J1,K1, and so on....

In media sheet, I m using Data Validation tool to create a dropdown list

In short

In media sheet i only want drop down to display Home, Media, Reviews, Contact ETC(text only) and skip 22, 92 ,111, 84 ETC (digits/numbers)

So basically skipping every 2nd column.

Please help me with that

Answer 1

I suggest you to create a helper column where you write down this formula, assuming your data is in column A:

=IFERROR(OFFSET(A$1,SMALL(IF(ISTEXT($A$1:$A$8),ROW($A$1:$A$8)),ROW(A1))-1,),0)

Press Ctrl+Shift+Enter to run this formula, and drag down... Now go to the data validation option, select List, and in Source place this formula:

=OFFSET(D1,,,COUNTA(D1:D10)-COUNT(D1:D10),)

Press Ok you will see the drop down without Numbers. Hope that helps..