Processing
 

How do I put this long formula as my data validation list?

17/09/2016 22:03#1

Joined at: 4 months ago

Post: 1

Thank: 0

Thanked: 0

How do I put this long formula as my data validation list?

=IF(P2="SUML01", SUML01, IF(P2="SUML02", SUML02, IF(P2="SUML03", SUML03, IF(P2="SUML04", SUML04, IF(P2="SUML05", SUML05, IF(P2="SUML06", SUML06, IF(P2="SUML07", SUML07, IF(P2="SUML08", SUML08, IF(P2="SUML09", SUML09, IF(P2="SUML10", SUML10, IF(P2="SUML11", SUML11, IF(P2="SUML12", SUML12, IF(P2="SUML13", SUML13, IF(P2="NAAL01", NAAL01, IF(P2="NAAL02", NAAL02, IF(P2="EMAL01", EMAL01, IF(P2="ACTL1", ACTL1, IF(P2="ACTL2", ACTL2, IF(P2="OUTL01", OUTL01, IF(P2="OUTL02", OUTL02, IF(P2="OUTL03", OUTL03, IF(P2="OUTL04", OUTL04, IF(P2="OUTL05", OUTL05, IF(P2="OUTL06", OUTL06, IF(P2="OUTL07", OUTL07, IF(P2="OUTL08", OUTL08, IF(P2="RELL01", RELL01, IF(P2="RELL02", RELL02, IF(P2="SLAL01", SLAL03,""))))))))))))))))))))))))))))

I tried typing it manually or copy pasting it but the data validation list didn't allow it.
Note that I am using Excel 2016 Pro Plus.

18/09/2016 09:16Top#2

Shai Rado

Member

Joined at: 6 months ago

Post: 3

Thank: 0

Thanked: 0

In case that the last IF(P2="SLAL01", SLAL03, is not an error, you can use the following formula inside your Data Validation List Formula bar:

=IF($P$2="SLAL1",SLAL3,INDIRECT($P$2)) 

See image below how I've writte it into P4 cell:

The selection of Cell P4 once P2 is SLAL01 (the Name Range SLAL03)

The selection of Cell P4 once P2 is SUML01 (the Name Range SUML01)

In case the last IF(P2="SLAL01", SLAL03, is a Typo, and you meant IF(P2="SLAL01", SLAL01, then the formula is simplier:

 =INDIRECT($P$2) 
Similar articles

Excel More conditions in one formula?

4 months ago - Reply: 2 - Views: 171

Get Workbook reference from Open File dialog

4 months ago - Reply: 2 - Views: 128

Add Calculated Field To Pivot Table

4 months ago - Reply: 0 - Views: 5

Nested IF Functions in Excel not working

4 months ago - Reply: 3 - Views: 163

Formulating Date in Excel 2013

4 months ago - Reply: 2 - Views: 88

VB6 extracts in Excel 2013

5 months ago - Reply: 2 - Views: 153

Excel 2013 : Pull Matching Data from Column

5 months ago - Reply: 1 - Views: 19

excel search and copy in/from another file

5 months ago - Reply: 0 - Views: 1

Buggy PivotTable.ColumnRange

5 months ago - Reply: 0 - Views: 4

Excel static conditional formatting area

5 months ago - Reply: 0 - Views: 8

vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re vé máy bay vé máy bay giá rẻ ve may bay ve may bay gia re