#N/A
 

I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible

#N/A
 

=IF(ISNA(INDIRECT("B" &
VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))),"",INDIRECT("B" &
VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE)))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Mike" wrote:

I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible

#N/A
 

=INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))

That formula will return #N/A if the lookup value ROW()-4 can't be found in
the lookup table. So, try this...

=IF(COUNTIF(ROW()-4,CP$5:CP$65),INDIRECT("B"&VLOOKUP(ROW()-4,CP$5:CR$65,3,0)),"")

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible

#N/A
 

yes this worked except for CG5 it =0 can this be fixed

"Ms-Exl-Learner" wrote:


=IF(ISNA(INDIRECT("B" &
VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))),"",INDIRECT("B" &
VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE)))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Mike" wrote:

I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible

#N/A
 

no htis did not work there is an error in formula can it be updated

"T. Valko" wrote:

=INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))

That formula will return #N/A if the lookup value ROW()-4 can't be found in
the lookup table. So, try this...

=IF(COUNTIF(ROW()-4,CP$5:CP$65),INDIRECT("B"&VLOOKUP(ROW()-4,CP$5:CR$65,3,0)),"")

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible


.

#N/A
 

there is an error in formula can it be updated

Ooops!

My fault. In the COUNTIF function I have the arguments backwards.

Corrected version:

=IF(COUNTIF(CP$5:CP$65,ROW()-4),INDIRECT("B"&VLOOKUP(ROW()-4,CP$5:CR$65,3,0)),"")

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
no htis did not work there is an error in formula can it be updated

"T. Valko" wrote:

=INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))

That formula will return #N/A if the lookup value ROW()-4 can't be found
in
the lookup table. So, try this...

=IF(COUNTIF(ROW()-4,CP$5:CP$65),INDIRECT("B"&VLOOKUP(ROW()-4,CP$5:CR$65,3,0)),"")

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
I have this formula =INDIRECT("B" & VLOOKUP(ROW()-4,CP$5:CR$65,3,FALSE))
I would like these cells with this formula to read a blank cell instead
of
#N/A
These cells read #N/A only until data is entered in other cells is this
possible


.