Here's a stripped down version of worksheet "Master":
MATERIAL COMMON NAME LATIN NAME
WT-1 Wedelia Wedelia
trilobata
ZP Coontie Fern Zamia pumila
ZPE Wandering Jew Zebrina pendula
Here's a "formula view" from worksheet "Project"
(recall Columns A & B here are entered manually (basically)
MATERIAL QTY COMMON NAME
LATIN NAME
WT-1 2248 =VLOOKUP(A2,Master,3,FALSE)
=VLOOKUP(A2,Master,4,FALSE)
ZP 91 =VLOOKUP(A3,Master,3,FALSE)
=VLOOKUP(A3,Master,4,FALSE)
ZPE 573 =VLOOKUP(A4,Master,3,FALSE)
=VLOOKUP(A4,Master,4,FALSE)
Here's a "normal view" from worksheet "Project"
MATERIAL QTY COMMON NAME LATIN NAME
WT-1 2248 Wedelia
Wedelia trilobata
ZP 91 #N/A
#N/A
ZPE 573 #N/A
#N/A
I've spent literally hours running TrimAll macros, learning about CHAR(160),
nonnreaking spaces, COPY from wkbk cell and paste, values to the
corresponding cell (e.g. ZP in Master to ZP in Project)...................
The only remaining clues I can offer a
1) there are 900 rows in Master and 52 in Project;
2) all values of MATERIAL that begin with the letter "Z" (the last 17 rows
in Master) fail.
This is killing me !!!!!!!!!!!!!!!!!!!!!
This appears to be a HUGE defect in Microsoft Excel?
Thanks in advance from "Freezing in Florida" - it 50F right now ;-)
"Don Guillett" wrote in message
...
Your formula has a reference to master. This would need to be defined to
the
lookup range desired. Check
editnamesdefinemaster
If you have a workbook named master and/or a worksheet named master you
should name your range something else. After checking all of that with no
resolution to the problem, you may send a workbook to my address below
along
with a copy of this message and any/all details.
--
Don Guillett
SalesAid Software
"TRJ" wrote in message
...
=VLOOKUP(A25,Master,3,FALSE) - pretty simple. As I described in GREAT
detail,
this has all worked previously (standard formula) but something has
gotten
corrupted??
At Step #8 (my original post) certain rows are #N/A. After "breaking"
link
to old worksheet, those rows are fine and new rows are #N/A.
"Dave F" wrote:
Post the formula you're using.
As to attaching files: most people will not open such attachments due
to
concerns about viruses. And most people prefer that discussion be kept
within the discussion groups so that other people who have similar
issues
may
use the discussion group archives as reference.
Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
"TRJ" wrote:
The attached Excel 2002 workbook contains 2 sheets:
(I'm new here and could not see how to attach the xls file. How do I
attach
or can I email to a respondent?)
Master - Is our Master Plant List;
Project - For creating project specific Plant List based upon filling
in
Column A & B.
History:
1) Workbook with only Master worksheet received from client;
2) Added Project Worksheet and formulas in columns C-M;
3) Enter data in columns A and B - all is well.
4) Receive new Master from client (unsorted rows).
5) Copy contents of old Project worksheet to new Project worksheet in
new
workbook (Ctrl-C, Ctrl-V);
6) Extract data from application (AutoCAD) consisting of multiple
rows
of
"material, quantity" (Column A, Column B) to text file;
7) Open txt file as new file with Excel and create pivot table to
summarize
data;
8) Copy-Clip data from pivot table to Cell A2 in Project worksheet.
In the attached workbook, on the Project worksheet you see rows of
red
text.
After step #8 above, columns C-M of these rows were #N/A.
I then did Edit Links Break (the sheet was still linked to the
original
workbook (step #1 - #3).
At this point, the rows that are red, went from #N/A to their correct
values
and the rows that are blue are now #N/A. ZP & ZPE both appear in the
lookup
table.
I've spent a few hours Googling this and have read ALL about "hidden
characters" and "phantom links" but I have been unable to resolve
this
problem.
Thanks in advance.
Randy Jones
Palm Coast, FL
Can anyone tell me how to fix this problem?