Formula Has Stopped Working

  • Thread starter Thread starter David M
  • Start date Start date
D

David M

WOW!! I am completely stumped!! Here is the formula in
question:

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",MID(VLOOKUP
(L4,PMDBSTATS,12,FALSE),1,10))

Now this works until I try to parse columns beyond #13. I
have tried the following:

1. copied to another cell within the named range...result:
returns an integer:

2. copied the exact formula into another cell..result:
returns duplicate information(thats good)...

3. okay created a test sheet with two columns, defined a
name copied the formula to an empty cell, result: returned
and integer....

4. lastly, I manually stepped through the formula,manually
choose the columns to parse, told excel to retun the data
in the 3rd column...result: returned an integer....

So it appears for some reason it has stopped working...if
all this sounds confusing?? Can anyone offer an idea???

Thanx so much..

David
 
Hi David
I think I already gave you a possible answer that you got
the serial of a date returned.
Please give the following information:
- your expected result from VLOOKUP
- the returned result
 
In this instance, the name rage is NOT the issue...all the
data IS contained in the named range.....I need someone to
look beyond this as being the issue...

D
 
YES!! You are correct I got the serial number instaed of
the DATE...I am looking for the DATE....now when I try to
format the cell to a DATE ...it still give the serial
number.....I have tested this out in several ways with no
luck....:(
 
Hi
if you get a date serail value formating should do. If you
like. email me your file and I'll have a look at it

How did you try to format the cell?
 
WOW!! I am completely stumped!! Here is the formula in
question:

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",MID(VLOOKUP
(L4,PMDBSTATS,12,FALSE),1,10))

Now this works until I try to parse columns beyond #13. I
have tried the following:

1. copied to another cell within the named range...result:
returns an integer:

2. copied the exact formula into another cell..result:
returns duplicate information(thats good)...

3. okay created a test sheet with two columns, defined a
name copied the formula to an empty cell, result: returned
and integer....

4. lastly, I manually stepped through the formula,manually
choose the columns to parse, told excel to retun the data
in the 3rd column...result: returned an integer....
Click to expand...


What is the "strange number" being returned?
Are you expecting a date?

If that is the case, then it is likely that the number being returned
represents that date. However, since the MID function returns a string (text),
formatting the cell as a date will have no effect on the display.

Try putting a double unary in front of the MID function (to convert the
string to a number), and format the cell as a date.

=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,13,FALSE)),"",--MID(VLOOKUP
(L4,PMDBSTATS,13,FALSE),1,10))


--ron
 
Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....

;(
 
Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....
Click to expand...

And what happened when you formatted the cell as a date, as I suggested?


--ron
 
Hmm...thanx for the tip....putting a double unary in front
of MID still returns the serial number for the date being
asked for....
Click to expand...

As I mentioned, you need to ALSO format the cell as a date. Just putting the
double unary only converts the result to a number. The number will then get
displayed depending on the format of the cell.


--ron
 
Hi
after looking again at your formula: Remove the MID function:
=IF(ISERROR(VLOOKUP(L4,PMDBSTATS,12,FALSE)),"",VLOOKUP(L4,PMDBSTATS,12,
FALSE))

As you want to return a date: no need for MID. and format this cell as
date and it should work
 
Hi Ron
even better would be to remove the MID function call as a date is
expected
Click to expand...

Not having seen his worksheet, I thought perhaps the contents of the cell
included some trailing text after the date. Although I would have used a LEFT
function were that the case.

But if all that is in the cell is a date, then the MID is superfluous, possibly
causing part of the problem. Or not if the contents of the cell is TEXT.


--ron
 
Back
Top