I'm using a formula

=VLOOKUP(C10,Labour!$A$3:$L$12,HLOOKUP(A10,Labour! $B$1:$L$2,2,FALSE),FALSE)

To find a value in a table from two drop down menus. If the are no
values in the drop down menus (ie C10 or A10) then the cell reads #N/A.
This is messing up the rest of my spreadsheet so I need it to be a
blank cell if A10 or C10 are blank or overwritten.

Any help gratefully appreciated.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"matt_the_brum" wrote in message
news:matt_the_brum.2byuk5_1154611833.7638@excelfor um-nospam.com...
|
| I'm using a formula
|
| =VLOOKUP(C10,Labour!$A$3:$L$12,HLOOKUP(A10,Labour! $B$1:$L$2,2,FALSE),FALSE)
|
| To find a value in a table from two drop down menus. If the are no
| values in the drop down menus (ie C10 or A10) then the cell reads #N/A.
| This is messing up the rest of my spreadsheet so I need it to be a
| blank cell if A10 or C10 are blank or overwritten.
|
| Any help gratefully appreciated.
|
|
| --
| matt_the_brum
| ------------------------------------------------------------------------
| matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
| View this thread: http://www.excelforum.com/showthread...hreadid=567885
|

hi man,

this maybe help you

=IF(ISNA(VLOOKUP(...)),,VLOOKUP(....))


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Thanks guys, thats working. Unfortunately its now upsetting something
else on the spreadsheet. I'll probably be back later with similar
questions.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

hi,

you should use this fx pls
=if(isna(vlookup(...)),,vlookup(....))

Do not use quote, just zero, then you format it to -


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my functio),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Right then, I've used

IF(ISNA(my function),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

The only way C1 would return an error is if either A1 or B1 contained that
error. Sum ignores text and "" (or even " ") I for one don't understand why
you are having the problem you describe.
--
Kevin Vaughn


"matt_the_brum" wrote:


Right then, I've used

IF(ISNA(my function),"",my function)

which is returning the reqired blank cell.

But when I use this blank cell as part of a formula in another cell I
get an error.

eg.
A1 has =IF(ISNA(my function),"",my function)
B1 has a value entered into it
C1 has =SUM(A1:B1)

C1 returns a value if A1 is true. C1 returns an error if A1 returns
the "".


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885

Thanks.
Taking the "" out of my formula got rid of all the errors.


--
matt_the_brum
------------------------------------------------------------------------
matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
View this thread: http://www.excelforum.com/showthread...hreadid=567885