vlookup working sometimes, sometimes not
 

Excel 97, multiple worksheets. 1st worksheet is a list of contractors used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data, no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving cell?

vlookup working sometimes, sometimes not
 

Sounds like the "receiving cell" is formatted as text. Edit--Clear--All
while that cell is selected, format it as general and try the formula again.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"maintchief" wrote in message
...
Excel 97, multiple worksheets. 1st worksheet is a list of contractors
used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data,
no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving
cell?

vlookup working sometimes, sometimes not
 

Maybe your workbook is getting corrupted, if not make sure the format in all
cells with the formulas are general and not text, then force a calculation
by replacing all equal signs with equal sign

find what = replace with =

replace all

if this is occurring more often then not maybe you should copy over data to
a new workbook and finally (so you don't have to edit the formulas when
changing lookup values) it's better to use a cell as a lookup value and then
change the criteria in that cell instead of typing things like "Carpets
national"

--
Regards,

Peo Sjoblom

Portland, Oregon




"maintchief" wrote in message
...
Excel 97, multiple worksheets. 1st worksheet is a list of contractors
used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data,
no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving
cell?

vlookup working sometimes, sometimes not
 

While some of the problem cells were not formatted as text when I posted,
they were originally. Following Anne's advice of clearing is fixing my
problem cells. Thank you very much.

"Anne Troy" wrote:

Sounds like the "receiving cell" is formatted as text. Edit--Clear--All
while that cell is selected, format it as general and try the formula again.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"maintchief" wrote in message
...
Excel 97, multiple worksheets. 1st worksheet is a list of contractors
used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No data,
no
error.
when I click on the 'fn' on the toolbar I see each argument shown with the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving
cell?

vlookup working sometimes, sometimes not
 

You're welcome! :)
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"maintchief" wrote in message
...
While some of the problem cells were not formatted as text when I posted,
they were originally. Following Anne's advice of clearing is fixing my
problem cells. Thank you very much.

"Anne Troy" wrote:

Sounds like the "receiving cell" is formatted as text. Edit--Clear--All
while that cell is selected, format it as general and try the formula
again.
************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"maintchief" wrote in message
...
Excel 97, multiple worksheets. 1st worksheet is a list of contractors
used
at work listed by their category, i.e. 'Fire Alarm Maintenance', 'PTAC
Units', 'Dectron', etc. plus their phone numbers.
I use vlookup in sheets following sheet 1 to look in sheet 1 and load
the
contractor name and phone numbers.
My first couple of sheets work fine with something like this:
'=VLOOKUP("Carpets national",PhoneList!$A$4:$G$33,4,FALSE)'.
In this example the National carpet contractor's phone number will
display
in the sheet that this function is used in.
In a later sheet I use:
=vlookup("Dectron",PhoneList!$A$4:$G$33,4,false).
All I get in the receiving cell is the visual of the function. No
data,
no
error.
when I click on the 'fn' on the toolbar I see each argument shown with
the
proper result shown.
Any ideas on why the proper result is not being posted in the receiving
cell?