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

0
votes

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

1
excelexcel-2007excel-2010vba
I m not an expert in excel.. but I tried to use many different method. Here one of them =OFFSET(summary!A1,(ROW()-1)*1,0) in media sheet. IT skip one row each time and get me every 2nd row. The problem is that this formula works fine on ROWs only and I dont know how to make it work for columns also after that how to put that formula in data validation tool to display the whole drop down list which are Home,Media,Reviews etc. – Omer Hameed

1 Answers

0
votes

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

enter image description here