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

B

BloodyShrapnel

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
 
H

Harlan Grove

BloodyShrapnel wrote...
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.
Click to expand...

Use two worksheeets in your master workbook. The first would contain
your current formulas complete with #REF! errors. The second would pull
data from the first using formulas like

G9:
=IF(ISERROR(FirstSheet!G9),"",FirstSheet!G9)
 
T

tuph

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 :)
 
Top