Cell value based on multiple conditions

[jomili]

I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

I appreciate any help I can get.

[StephenR]

Irrespective of approach, does this formula produce the results you want?

=IF(COUNTA(A2:B2)=0,0,IF(F2="CAN",20,IF(OR(F2="FAS",F2="FAR"),10)))

[jomili]

Stephen,

Yes! Your formula works perfectly! Thank you!

Now ideally I'd like to incorporate this into the macro I'm already running, whereby I select the data you've seen from another workbook and paste it into the worksheet example you saw. I would like the formula to derive the allowable hours and then paste those values in to column G. Any ideas how to accomplish that piece?

[StephenR]

This will put the formula in G with code and then replace with values- not sure I follow what else you want to do.

Please Login or Register  to view this content.

[JBeaucaire]

Use the recorder to record you putting the formula into the correct cell at the correct time, let the formula do the math for you. Makes the macro a lot easier to design.

[jomili]

Stephen,

You deserve your title of forum guru. I think you've given me everything I could every ask for in terms of this little function, and I thank you from the bottom of my heart. If there's any way i can ever repay the favor just let me know.

Thank you!

[StephenR]

My pleasure, glad it worked.

JB makes a good point - the macro recorder is your friend.

[jomili]

JB,
Thanks for your comment. I try to use the macro recorder every time I can, and it's helped me out lots. Thanks for suggesting it.