IF(ISBLANK) returning #VALUE

C

Code Numpty

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

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

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial2,'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.
 
B

Bernard Liengme

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:\CalibrationMasters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)),"",VLOOKUP(deviceserial2,'C:\Calibration
Masters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE))

XL2007 users can use IFERROR for a short formula
best wishes
 
C

Code Numpty

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.
 
E

Eduardo

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

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


Code Numpty said:
I have these 2 formulas in a workbook in Excel 2003:

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

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial2,'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.
Click to expand...
 
C

Code Numpty

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

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

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


Code Numpty said:
I have these 2 formulas in a workbook in Excel 2003:

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

B)=IF(ISBLANK(deviceserial2),"",VLOOKUP(deviceserial2,'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.
Click to expand...
Click to expand...
 
B

Bernard Liengme

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 said:
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 said:
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:\CalibrationMasters\[Calibration_Devices.xls]Sheet1'!$A$4:$E$31,5,FALSE)),"",VLOOKUP(deviceserial2,'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
Click to expand...
Click to expand...
 
C

Code Numpty

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. :-(
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP difficulty sorting data? 8
if isblank question 5
Need correct IF(ISBLANK) formula when referencing whether one of twocells (OR) is blank, returning " 3
Testing a cell value for a specific weekday. 3
Return an absolutely empty cell ... but not "" 3
IF(ISBLANK) with other criteria 5
IF(ISBLANK for multiple cells A1:N1 6
IF and ISBLANK Functions 1

Top