Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. :-( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and ISBLANK | Excel Worksheet Functions | |||
Need correct IF(ISBLANK) formula when referencing whether one of twocells (OR) is blank, returning "no value" | Excel Worksheet Functions | |||
ISBLANK() | Excel Worksheet Functions | |||
isBlank() | Excel Worksheet Functions | |||
IF(ISBLANK) | Excel Worksheet Functions |