Greetings excel gurus!
I'm having trouble getting Excel to return expected results. I must be using the wrong syntax, and can't figure it out.
Named range 1: Fiscal_Yr; rows A1-A1000 and the values in that range use data validation list ranging from 16 to 40.
Named range 2: In_Plan; rows B1-B1000 and the values in that range use data validation list, only with "Y" or "N" as allowable values.
I try to use the following formulas, and they don't work:
=IF(AND(Fiscal_Yr=16,In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid"
=IF(AND(Fiscal_Yr="16",In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid" ... for this one I've tried to make sure the format of column A is number and B is Text or General
=IF(AND(Fiscal_Yr,"=16",In_Plan,"=Y"),"Re-Validate","Valid") ... with cell A1 clearly set to 17, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate" ... for this one I think the comma after the named range is the problem, I'm surprised a value is returned at all
Seems the only way I can get the functionality intended is to use cell references in the formula to make the function return intended results.
=IF(AND(A1=16,B1="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate"
I'm having trouble getting Excel to return expected results. I must be using the wrong syntax, and can't figure it out.
Named range 1: Fiscal_Yr; rows A1-A1000 and the values in that range use data validation list ranging from 16 to 40.
Named range 2: In_Plan; rows B1-B1000 and the values in that range use data validation list, only with "Y" or "N" as allowable values.
I try to use the following formulas, and they don't work:
=IF(AND(Fiscal_Yr=16,In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid"
=IF(AND(Fiscal_Yr="16",In_Plan="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as FALSE or "Valid" ... for this one I've tried to make sure the format of column A is number and B is Text or General
=IF(AND(Fiscal_Yr,"=16",In_Plan,"=Y"),"Re-Validate","Valid") ... with cell A1 clearly set to 17, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate" ... for this one I think the comma after the named range is the problem, I'm surprised a value is returned at all
Seems the only way I can get the functionality intended is to use cell references in the formula to make the function return intended results.
=IF(AND(A1=16,B1="Y"),"Re-Validate","Valid") ... cell A1 clearly set to 16, cell B1 clearly set to Y, and it returns as TRUE or "Re-Validate"
Last edited: