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

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

Answers


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


Need Your Help

Saxon in Java: XSLT for CSV to XML

java xml xslt csv saxon

Mostly continued from this question: XSLT: CSV (or Flat File, or Plain Text) to XML

is there a way to prevent others from changing data in a row in mongodb

python mongodb pymongo

I'm designing an application using mongodb and python and I need some way to read a row then based on its contents update it but I don't know of a way to make sure than another process isn't access...