If a person's name is found in a named range then execute formula

Copper Contributor

Hi, first post here, looking for some help....

 

Please see screenshot, could someone help me with the formula so values in F12, F13 and F14 display values that I'm after shown in C12, C13 and C14.

 

The formula I started off for E12, E13 and E14 is pasted below, but as the staff number grow it'd be very time consuming to update.

=IF(OR(A12="John 1",A12= "John 2"), B12/$A$8, IF(A12="John 4",B12/$B$8," "))

I'm trying to use Named Range to create FullTime and PartTime so in the future only need to update the range cells instead of having to update multiple cells, but I failed somewhere....

=IF(found(A12,FullTime), C12/$A$8, IF(found(A12,PartTime),C12/$B$8," "))

 

Many thanks.

 

2019-02-24_1906.png

2 Replies

That could be

=B12/$A$8*(COUNTIF(FullTime,A12)>0)+B12/$B$8*(COUNTIF(PartTime,A12)>0)

Even better use not named ranges but Excel tables.

 

Thanks @Sergei Baklan that works, much appreciated.