IF statement returning #VALUE error

Hi guys,

should hopefully be last request for a little while

(Benishiryo, if you end up in this thread, you're probably going to recognise a lot of your handiwork)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Both of these codes return an error. When evaluating the formula, the error occurs with the 'MRC' defined name, or the 'Sheet3!G1:G129' (they contain the exact same data, I was just checking if the defined name was causing the issue)

It works if I target a single cell, for example

Please Login or Register  to view this content.

or make it balls obvious for the formula

Please Login or Register  to view this content.

So I think the IF statement doesn't like my range for some reason.

Please help? my project is almost done, this is the last hurdle! :D

EDIT:

In case it wasn't apparent, I want the formula to check the value of E230, and if it matches any values in the range/defined name, I want the cell value to state Mgr. If it doesn't match, I want it to run through the Leave_types list, and if match, duplicate the data from E230 into E231 (where this formula resides). If no MRC and no Leave_Type match, I want it enter the value "free" into the cell E231.

Try like this...

=IF(COUNTIF(Sheet3!G1:G129,E$230),"Mgr",IF(COUNTIF(Leave_Types,E230),E230,"Free"))

=IF(E$230="MRC","Mgr",IF(COUNTIF(Leave_Types,E230),E230,"Free"))

I'd be embarressed for not realising that if I wasn't so damned happy it works. thanks mate!

EDIT:

spoke to soon. Its not working quite right...

Please Login or Register  to view this content.

This is incorrectly changing codes to "Mgr". As in, its checking a code, and its NOT in that range, and its changing it to "Mgr".

Please Login or Register  to view this content.

This is incorrectly showing cells as Free, as in, its checkin a code, and it IS in that range, and its not changing it to "Mgr"

Glad it helps you and thanks for the feedback and rep

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.