Avoiding Errors When Attempting To Retrieve Data From A Non-existant Worksheet
 

Hi,

I am working on a Workbook database which will be referring to multiple other workbooks with multiple worksheets. Every workbook is a buyer, and every worksheet is an item, named after the item number. The one cell (A45) within the worksheet is the quantity which I want in the master workbook. My problem is, I want to have a list of all items possible in my master workbook, and retrieve the quantity cell from each worksheet, but not all workbooks have all items, leaving me with cells which would be refering to non-existant worksheets. I get the "#REF!" and its causing me a lot of grief. Can someone suggest a solution? Or even another easier way to do so.

Thanks in advance,

Nick

Avoiding Errors When Attempting To Retrieve Data From A Non-existant Worksheet
 

You can add IF(ERROR.TYPE=4),"",.... (normal formula) to your formula to
return a blank if the error type in the source cell is #REF!. Look up
ERROR.TYPE in help for more information about which digits apply to
which ERROT.TYPEs.

The formula I use this in is
=IF(ERROR.TYPE(VLOOKUP($A26,Worksheet!$C$2:$H$45,2 ,FALSE)=4),"",VLOOKUP($A26,Worksheet!$C$2:$H$45,2, FALSE))

Hope this helps :)


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=525939