Data Validation: Multiple Formulas required in 1 cell?

[ET Timor]

Hi All,

This is my First post for MSOAH. A pleasure to be here.

I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:

DAYS: CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK” (References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)” (References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)” (References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)” (References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)” (References 3 Days) = “USD 50.00”

I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?

So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?

E.G. Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”

The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)",X29,IF(T26="SATURDAY_(DEP)",X30)))))

I would be so incredibly grateful if somebody could help me (in laymen’s terms)??

Sincerest Regards and thanx,

ET

[JeanRage]

Hi,

Ideally, you should post a sample worksheet ...

HTH

[ET Timor]

Thanx for the advice JR.

See attached sample worksheet. Hope it makes some sense..?

[NBVC]

Try:

=VLOOKUP(J10,$M$9:$N$13,2,0)

[ET Timor]

NBVC, thanx.

I can see that this does work (its far closer than I’ve ever been before), but the formula only works once (immediately after imputing the formula itself).

Subsequent to that however, if I select a different drop-down option (in the same cell I just placed the formula in), nothing happens?

I need use this spreadsheet once a week. Of course each week will bring different attributes (but is always based on the standard drop down options)?

I’m grateful for the advice.

ET

[NBVC]

Go to Tools|Options and in the Calculation tab, make sure that the Automatic radio button is selected (not the Manual).

[ET Timor]

Yip; that did it!! SOLVED!!!!

What has taken you a few minutes has cost me 2 months of head-aches and stress!! Why did I not do this ages ago? I'm totally speechless...

NBVC; I'm exceedingly grateful and sincerely appreciative for your kind help and assistance. Thank you very, very much indeed.

Warmest regards,

ET

[deadlyduck]

Welcome to the brilliant Excelforum.com ET Timor- I'm also a greatly satisfied user (and have had NBVC- amongst MANY others- to thank on occasion too!)

Make sure to mark your problem as solved.

[ET Timor]

Thanx DeadlyDuck! I feel utterly relieved and totally euphoric to say the least. Go figure!?

I’ve been trying to do just that. I'm know I saw the "Solved" icon somewhere...