vlookup:suddenly doesn't work for half the rows in the same wk.bk

R

Rochelle

I have a vlookup retrieving info from another page in the same workbook. To
make things easy I copy the first row of formulas to the rows below and it
works up to a ceratin row, and the I get #N/A. The error description is
"Inconsistent formula" and the trace has a line from the lookup value to the
cell next to it and then a dotted line to another cell two rows up.
I have no clue why there is this difference - I have changed cell formats
both on the source page and in my report page; the formula is the same as the
rows above (they work, btw) so what is the problem with the rest of the rows?
Any comments or suggestions?
 
N

Niek Otten

Please post your initial formula and the first one giving #NA.
I guess that the reference to the table is relative instead of absolute,
something like A1:A100 instead of $A$1:$A$100.
 
D

Dave Peterson

Another option if the lookup table is on a sheet by itself (with nothing below
it)...

You can use the whole column:

=vlookup(a1,sheet2!a:e,3,false)
 
R

Rochelle

Hi,
You're right I haven't used absolutes.
I have a source page with information for a project within the same row (to
the right of the project number). Then the range of the whole sheet is named
Calculatie.
So the formula on the report sheet is
=if(A3=0;"";VLOOKUP(A3;calculatie;3;false))

This formula is the same for each cell below including the one containing
#N/A except of course the lookup value is different because it is a new row.

I considered the fact that the problem info may fall outside of the named
range, but I renamed it and re-enterd the VLOOKUP formula and still no joy.

So, any ideas?
Rochelle
 
Top