PDA

View Full Version : Conditional IF Based on Cell Value



crissy_c
11-23-2012, 09:32 PM
Hi

I am a new to VBA..do need some help on my worksheet.

I am trying to create a macro that will run each time the value in column B & C change. e.g when i enter CBI in Col B & AUstralia in Col C, it will automatically run the IF formula i have for CBI Australia...and if i enter Clough and Australia, it will run the IF formula i have for Clough Australia...if i enter CBIC PNG it will run the IF formula i have for CBIC PNG, and lastly if i enter CBI Dubai it will run the formula for CBI Dubai..

any one help please???

I have attached my worksheet..thanks!!!

Teeroy
11-23-2012, 10:50 PM
Hi crissy_c,

Welcome to the forum. Please start a new thread for a new question, even though it's a similar topic.
The attached workbook should do what you want though you mention Dubai in your post and in your sample workbook there is only "OTHERS" so you may need some tweaking.
Please note that to activate from a worksheet event the code must begin from the worksheet code module, not a standard code module.
Good luck with the coding.

crissy_c
11-24-2012, 12:54 AM
Thanks much! i have no background in programming or coding...i am just a plain user of excel...you have made my life easier! : pray2:

Bob Phillips
11-24-2012, 06:10 AM
You can do it with a formula, no code needed


=IF($B18="CLOUGH",MAX($D18-5,0),IF($B18="CBI",$D18,0))
+SUMPRODUCT(--($E18:$I18>(IF($B18="CLOUGH",10,IF($B18="CBIC",8,7.6)))),
$E18:$I18-(IF($B18="CLOUGH",10,IF($B18="CBIC",8,IF($B18="CBI",INDEX({7.6,8},MATCH($C18,{"AUSTRALIA","OTHERS"},0))))))
)
+IF($B18="CLOUGH",MAX(0,$J18-10),$J18)

crissy_c
11-24-2012, 09:55 PM
i have figured it out, thanks heaps for your help!!

crissy_c
11-25-2012, 11:27 PM
Hi xld

I need your expertise in my attached sheet. I need to construct a formula that will calculate the no.of days between 2 dates (Travel In & Out column) with the criteria from the TAR reference column, can you help me construct it please?:( :doh:

thanks in advance!

Bob Phillips
11-26-2012, 02:43 AM
Can you give some examples, I don't understand the significance of TAR?

crissy_c
11-26-2012, 05:02 AM
Can you give some examples, I don't understand the significance of TAR?

i was thinking a formula could be made to calculate the no.of days (earliest and oldest) for certain trip (that is the TAR ref column), so for example, RJV0951, the no.of days from Jan.19 to Feb 26? (if that is possible?)

thanks

Bob Phillips
11-26-2012, 07:30 AM
This array formula does it

=MAX(IF($F$3:$F$19=$F3,$K$3:$K$19))-MIN(IF($F$3:$F$19=$F3,$J$3:$J$19))

but what should it show if depart r return dates are all N/A?

crissy_c
11-26-2012, 03:50 PM
those cells with N/A means there was only 1 trip booked and the return trip could be found in rows below, but will definitely have the same TAR ref...a bit impossible eh?:bug:

Bob Phillips
11-26-2012, 03:53 PM
Look at the first item, MJV0736, there is no return trip.

crissy_c
11-26-2012, 10:45 PM
yup,got yah, i am only after their R&R, thus there is another column (Trip LV)

Bob Phillips
11-27-2012, 02:58 AM
I am not sure now if the problem is solved or you need more. Can you clarify?

crissy_c
11-27-2012, 04:45 PM
yup, this case can be closed. thanks for your help:thumb