Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TRJ TRJ is offline
external usenet poster
 
Posts: 2
Default Excel 2002 VLOOKUP Weird

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?



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Excel 2002 VLOOKUP Weird

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?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TRJ TRJ is offline
external usenet poster
 
Posts: 2
Default Excel 2002 VLOOKUP Weird

=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?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Excel 2002 VLOOKUP Weird

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?





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ TJ is offline
external usenet poster
 
Posts: 4
Default Excel 2002 VLOOKUP Weird

Thaks for your thoughts but as I explained in original post this all worked
until I received a new "master" list from my client. It no works in a
retarded and unpredictable manner.

Please reread the detailed list of steps I took and provide any additional
thoughts you might have.

If there were a way to attach th eworkbook, I would.


"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?









  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Excel 2002 VLOOKUP Weird

We do NOT condone attaching workbooks here but you may send ME a copy if you
like.

--
Don Guillett
SalesAid Software

"TJ" wrote in message
...
Thaks for your thoughts but as I explained in original post this all
worked
until I received a new "master" list from my client. It no works in a
retarded and unpredictable manner.

Please reread the detailed list of steps I took and provide any additional
thoughts you might have.

If there were a way to attach th eworkbook, I would.


"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?









  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Excel 2002 VLOOKUP Weird

TRJ wrote...
=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??

....

If this is returning #N/A, then the first thing to check would be the
return result from the formula

=COUNTIF(INDEX(Master,0,1),A25)

This returns how many instances of the value in cell A25 there are in
the first column of the range named Master. If this formula returns
zero, there are no instances. If it returns a positive number, then
the only way your VLOOKUP formula would return #N/A would be due to
referenced cell in Master containing the value #N/A.

If the COUNTIF formula returns zero, then does A25 contain a numeric
or text value? If A25 should contain numeric values, next thing to try
is

=SUMPRODUCT(COUNTIF(INDEX(Master,0,1),"<"&(A25+{-0.1;0.1})),{-1;1})

This returns the count of values in the first column of Master that
are withing -/+0.1 of the value in cell A25. Use something other than
{-0.1;0.1} if your A25 and first column of Master values could be
fractional values. If this formula returns a positive number, then
you're dealing with fractional values that aren't exactly equal. If
all values should be whole numbers, change your VLOOKUP formula to

=INDEX(Master,MATCH(ROUND(A25,0),ROUND(INDEX(Maste r,0,1),0),0),3)

On the other hand, if A25 and the first column in Master should
contain text values, then trailing spaces are the usual reason for
VLOOKUP failing. Just in case Master comes from an HTML table, try
this formula.

=SUMPRODUCT(--(RIGHT(INDEX(Master,0,1),1)=CHAR({32;160})))

If this formula returns a positive number, then trailing space
characters are likely causing the problem. The easiest way to deal
with them would be to use the first blank column on either side of the
Master range to hold the array formula

=TRIM(SUBSTITUTE(INDEX(Master,0,1),CHAR(160)," "))

Copy the column containing this array formula, select the first column
in Master, paste special as values, then clear the column containing
the array formula. This will have replaced all HTML nonbreaking spaces
(decimal character code 160) with plain ASCII spaces, then deleted any
leading or trailing spaces and replaced all instances of multiple
spaces with single spaces. At this point, you should be able to use

=VLOOKUP(TRIM(A25),Master,3,0)

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default Excel 2002 VLOOKUP Weird

"Harlan Grove" wrote...
....
=SUMPRODUCT(--(RIGHT(INDEX(Master,0,1),1)=CHAR({32;160})))

....

Make that

=SUMPRODUCT(--(RIGHT(INDEX(Master,0,1),1)=CHAR({32,160})))

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ TJ is offline
external usenet poster
 
Posts: 4
Default Excel 2002 VLOOKUP Weird

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?







  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ TJ is offline
external usenet poster
 
Posts: 4
Default Excel 2002 VLOOKUP Weird

=VLOOKUP(A3,Master,3,FALSE)
Where A3 contains text and the sought row in the lookup table is also text.

"Dave F" wrote in message
...
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?







  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,124
Default Excel 2002 VLOOKUP Weird

Workbook sent to me privately.

As I mentioned in my original answer, "check your definition of master". You
did not include ALL of the rows so you weren't getting hits on "z". I added
a title row (do NOT delete) and defined a name which is self adjusting using
the count of column A. You may now add or delete (all but the top row) rows,
as desired. I also made a couple of formatting changes and did a freeze pane
to see the title row at all times.

master (still NOT a good idea to name the same as the worksheet name)
=OFFSET(Master!$A$1,1,0,COUNTA(Master!$A:$A)-1,20)

--
Don Guillett
SalesAid Software

"TJ" wrote in message
...
=VLOOKUP(A3,Master,3,FALSE)
Where A3 contains text and the sought row in the lookup table is also
text.

"Dave F" wrote in message
...
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?







  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
TJ TJ is offline
external usenet poster
 
Posts: 4
Default Excel 2002 VLOOKUP Weird

PROBLEM SOLVED.

I did not realize that a "Name" was created. I went back and looked at my
ORIGINAL build of this system (that worked fine) and found
"=Master!$A$2:$L$857" which fully encompased the lookup table data.

Upon revising the definition of Master (Insert Name Define click on
Master and edit to contain all rows) the problem is eliminated.

Many thanks to Don for his EXPERT knowledge of Excel.


"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?







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste Table from Word in Excel - 2000 -vs- 2002 PeteW Excel Discussion (Misc queries) 3 April 27th 06 07:36 AM
Cells User Select Locked after upgrade to Excel 2002 TWilson Excel Discussion (Misc queries) 1 August 5th 05 12:22 PM
Excel 97 vs. Excel 2002 Pdek Excel Discussion (Misc queries) 10 May 10th 05 06:21 AM
Displaying charts from excel 2003 in excel 2002? nick_h Excel Discussion (Misc queries) 0 May 9th 05 02:41 AM
Macro in Excel 2002 to save a workbook to a FTP location Lloyd Excel Discussion (Misc queries) 0 December 21st 04 03:49 PM


All times are GMT +1. The time now is 11:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"