IF(ISBLANK) returning #VALUE
 

I have these 2 formulas in a workbook in Excel 2003:

A)=IF(ISBLANK(deviceserial2),"",deviceserial2)

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

When named range deviceserial2 is blank, formula A displays nothing (as
intended) and formula B displays #VALUE. When the named range is completed
with a serial number, it again displays properly with formula A but displays
#VALUE again for formula B.

If I change formula B replacing the second instance of deviseserial2 with
the actual cell address it works fine except when there is no serial number
in the cell, instead of nothing it displays a zero.

I cannot see where the problem lies with this and amlooking for any pointers.

IF(ISBLANK) returning #VALUE
 

Is the named cell really blank? There is a difference between displaying
nothing and holding nothing.
Suggest a longer formula

=IF(ISNA(VLOOKUP(deviceserial2,'C:\CalibrationMast ers\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

XL2007 users can use IFERROR for a short formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Code Numpty" wrote in message
...
I have these 2 formulas in a workbook in Excel 2003:

A)=IF(ISBLANK(deviceserial2),"",deviceserial2)

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

When named range deviceserial2 is blank, formula A displays nothing (as
intended) and formula B displays #VALUE. When the named range is completed
with a serial number, it again displays properly with formula A but
displays
#VALUE again for formula B.

If I change formula B replacing the second instance of deviseserial2 with
the actual cell address it works fine except when there is no serial
number
in the cell, instead of nothing it displays a zero.

I cannot see where the problem lies with this and amlooking for any
pointers.

IF(ISBLANK) returning #VALUE
 

Hi Bernard. I have deleted the named range to make sure many times. I have
tried your formula (thank you) but this still displays #VALUE whether the
named range is empty or contains a serial number. The named range is in fact
2 cells merged but the same things works in other files just fine.

"Bernard Liengme" wrote:

Is the named cell really blank? There is a difference between displaying
nothing and holding nothing.
Suggest a longer formula

=IF(ISNA(VLOOKUP(deviceserial2,'C:\CalibrationMast ers\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

XL2007 users can use IFERROR for a short formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

IF(ISBLANK) returning #VALUE
 

Hi
try
)=IF(ISBLANK(deviceserial2),"",+isna(VLOOKUP(devic eserial2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)))

If this help you please rate it your left hand of this screen. Thank you


"Code Numpty" wrote:

I have these 2 formulas in a workbook in Excel 2003:

A)=IF(ISBLANK(deviceserial2),"",deviceserial2)

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

When named range deviceserial2 is blank, formula A displays nothing (as
intended) and formula B displays #VALUE. When the named range is completed
with a serial number, it again displays properly with formula A but displays
#VALUE again for formula B.

If I change formula B replacing the second instance of deviseserial2 with
the actual cell address it works fine except when there is no serial number
in the cell, instead of nothing it displays a zero.

I cannot see where the problem lies with this and amlooking for any pointers.

IF(ISBLANK) returning #VALUE
 

Hi Eduardo, That returns FALSE. I am going to have to check my vlookup data
again as I am suspicious about this, thanks.

"Eduardo" wrote:

Hi
try
)=IF(ISBLANK(deviceserial2),"",+isna(VLOOKUP(devic eserial2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)))

If this help you please rate it your left hand of this screen. Thank you


"Code Numpty" wrote:

I have these 2 formulas in a workbook in Excel 2003:

A)=IF(ISBLANK(deviceserial2),"",deviceserial2)

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

When named range deviceserial2 is blank, formula A displays nothing (as
intended) and formula B displays #VALUE. When the named range is completed
with a serial number, it again displays properly with formula A but displays
#VALUE again for formula B.

If I change formula B replacing the second instance of deviseserial2 with
the actual cell address it works fine except when there is no serial number
in the cell, instead of nothing it displays a zero.

I cannot see where the problem lies with this and amlooking for any pointers.

IF(ISBLANK) returning #VALUE
 

Hard for us to solve without your files. I suggest you debug stepwise.
Try just the VLOOKUP part with a non-merge cell and locate any problem
Then try with merged cell
Finally use the IF statement
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Code Numpty" wrote in message
...
Hi Bernard. I have deleted the named range to make sure many times. I have
tried your formula (thank you) but this still displays #VALUE whether the
named range is empty or contains a serial number. The named range is in
fact
2 cells merged but the same things works in other files just fine.

"Bernard Liengme" wrote:

Is the named cell really blank? There is a difference between displaying
nothing and holding nothing.
Suggest a longer formula

=IF(ISNA(VLOOKUP(deviceserial2,'C:\CalibrationMast ers\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)),"",VLOOKUP(deviceseri al2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

XL2007 users can use IFERROR for a short formula
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

IF(ISBLANK) returning #VALUE
 

I have worked a wat around this, although still mystified by the problem.

The formulas work exactly as they are supposed to if the named range is a
single cell only AND referred toin the formula by its cell address. It
appears to be using a named range that is causing the problems. :-(