GETTING RID OF #REF in VLOOKUP CELLS

  • Thread starter Thread starter ANDYGM
  • Start date Start date
A

ANDYGM

I have created a spreadsheet to calculate insurance premiums (se
previous post) using the VLOOKUP function. It is now working properly
but I have discovered a problem.

The calculation area consists of 20 lines, each of which holds on
calculation and they are totalled at the bottom of this area. However
blank lines result in "#REF" in the VLOOKUP cells. As a result, I can'
total either these columns or calculation columns which refer to them.

How can I get around this? Is there any way to induce a bank cell or
zero in the VLOOKUP columns?

And
 
Please post in the same thread, that way all the necessary info might be in
the same thread or at least refer to your formula,
I won't spend any time looking for your previous post(s)
--

Regards,

Peo Sjoblom


ANDYGM said:
I have created a spreadsheet to calculate insurance premiums (see
previous post) using the VLOOKUP function. It is now working properly,
but I have discovered a problem.

The calculation area consists of 20 lines, each of which holds one
calculation and they are totalled at the bottom of this area. However,
blank lines result in "#REF" in the VLOOKUP cells. As a result, I can't
total either these columns or calculation columns which refer to them.

How can I get around this? Is there any way to induce a bank cell or a
zero in the VLOOKUP columns?

Andy


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
Previous thread is not relevant to this one, hence not posted there.

Has anyone anything constructive to offer?

Andy
 
Previous thread is not relevant to this one, hence not posted there.

Has anyone anything constructive to offer?

Andy
 
Of course previous thread must be relevant since you refer to it in your
post, note that vlookup can't return
a #REF error by itself so I assume you have combined it with another
function that return which would make
your earlier post relevant. Try to learn something about NG and other forum
etiquette, that way you probably will
get more help..

http://www.cpearson.com/excel/newposte.htm



--

Regards,

Peo Sjoblom


ANDYGM said:
Previous thread is not relevant to this one, hence not posted there.

Has anyone anything constructive to offer?

Andy


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
#REF! error should only occur for VLOOKUPS where the number of columns across is greater than the number of columns inthe data area eg
=vlookup(A1,A1:B100,3,false)
would give a #REF! error as the data area is only 2 columns wide but the offset is for 3 columns

The only other reason for a #REF! would be that part of the lookup area has been deleted so the reference is no longer valid.....
 
Peo Sjoblom's bad manners are only exceeded by his/her bad manners.

Reference to previous post was merely to let people know that there had
been one. Had Peo actually taken the trouble to read my original post
in this thread,he/she would have realised that.

Cause of "#REF" is that there can sometimes be a blank cell in the
reference.

I have now solved the problem by incorporating an IF statement to
recognise the blank cell.

E.G. =IF(B4="","",VLOOKUP etc.)

Thanks to the person who responded constructively.

Andy
 
Back
Top