Vlookup REF error
 

I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list sheet
Row 5 of the Presentation sheet shows data from 4 in the new list sheet

I do not understand why the row for the old 3 is still retained in the
Presentation sheet since I am getting the name as well as the data from the
new list sheet.

I hope you can comprehend this. I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Vlookup REF error
 

Hi

As always show us your code or formulas, for further comments.

---
Per

On 18 Jan., 07:58, "William Beard" wrote:
I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. *One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list sheet
Row 5 of the Presentation sheet shows data from 4 in the new list sheet

I do not understand why the row for the old 3 is still retained in the
Presentation sheet since I am getting the name as well as the data from the
new list sheet.

I hope you can comprehend this. *I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Vlookup REF error
 

Sounds like a formula error, but cannot diagnose without seeing it!

--

Regards,
Nigel




"William Beard" wrote in message
...
I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list sheet
Row 5 of the Presentation sheet shows data from 4 in the new list sheet

I do not understand why the row for the old 3 is still retained in the
Presentation sheet since I am getting the name as well as the data from
the new list sheet.

I hope you can comprehend this. I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Vlookup REF error
 

Nigel;187281 Wrote:
Sounds like a formula error, but cannot diagnose without seeing it!

--

Regards,
Nigel




"William Beard" wrote in message
...
I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list
sheet
Row 5 of the Presentation sheet shows data from 4 in the new list
sheet

I do not understand why the row for the old 3 is still retained in
the
Presentation sheet since I am getting the name as well as the data
from
the new list sheet.

I hope you can comprehend this. I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Hello William,

The worksheets are part of what is known as a "Collection Object".
These objects are special arrays. Each element of the collection exists
as a pair of values: a Key, and an Item. The key is string that is used
as a friendly identifier. The item can be any system object, string,
number, or date. The pair can be retrieved one of two ways, either by
its sequential index value, i.e. 1,2,3, etc. or by the friendly name
"Sheet1", "Sheet2", "Sheet3". The system reassigns the creation indices
whenever an object is deleted from the collection. Deleteing "Sheet2"
will cause "Sheet3"'s creation index moves up from 3 to 2. If there were
more sheets, every sheet after "Sheet2" would move up by 1. You would be
better of solving this problem with a VBA macro.


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51644

Vlookup REF error
 

Thanks, Per. I am unfamiliar with the protocols here.

I have three sheets.
All the formulas are in the ThisWeek sheet.
The current weeks data source is in DATA sheet.
From B6 in the ThisWeek sheet, a name is retrieved via =DATA!A3
From D6 in the ThisWeek sheet, data is retrieved via
=VLOOKUP(B6,DATA!A3:I25,7,FALSE)

If the original second name is missing and what was the third name is now
the second name, this results:
In the ThisWeek sheet B7 =WCG!#REF!

I hope this is what you require?

"Per Jessen" wrote in message
...
Hi

As always show us your code or formulas, for further comments.

---
Per

On 18 Jan., 07:58, "William Beard" wrote:
I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list sheet
Row 5 of the Presentation sheet shows data from 4 in the new list sheet

I do not understand why the row for the old 3 is still retained in the
Presentation sheet since I am getting the name as well as the data from
the
new list sheet.

I hope you can comprehend this. I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Vlookup REF error
 

Thanks, Leith. but, I do not know VBA macro.


"Leith Ross" wrote in message
...

Nigel;187281 Wrote:
Sounds like a formula error, but cannot diagnose without seeing it!

--

Regards,
Nigel




"William Beard" wrote in message
...
I am putting together an excel application using vlookup.
I have a list sheet of names that I am searching for related data.
I transfer the data from the list sheet, into a Presentation sheet.
My problem occurs when a name is removed from the list sheet.
I haven't tried adding a name, yet. One hurdle at a time.

Given list sheet names 1,2,3,4,5
I transfer data to a Presentation sheet for names 1,2,3,4,5
Now, remove/delete name 3 from the list sheet
I now have names 1,2,3,4 in the list sheet
However, I am getting a Ref error in row 3 of the Presentation sheet
Row 4 of the Presentation sheet shows data from 3 in the new list
sheet
Row 5 of the Presentation sheet shows data from 4 in the new list
sheet

I do not understand why the row for the old 3 is still retained in
the
Presentation sheet since I am getting the name as well as the data
from
the new list sheet.

I hope you can comprehend this. I can sure use a solution.
If you need clarity on anything, I'll do my best to provide it.

Thanks in Advance
William

Hello William,

The worksheets are part of what is known as a "Collection Object".
These objects are special arrays. Each element of the collection exists
as a pair of values: a Key, and an Item. The key is string that is used
as a friendly identifier. The item can be any system object, string,
number, or date. The pair can be retrieved one of two ways, either by
its sequential index value, i.e. 1,2,3, etc. or by the friendly name
"Sheet1", "Sheet2", "Sheet3". The system reassigns the creation indices
whenever an object is deleted from the collection. Deleteing "Sheet2"
will cause "Sheet3"'s creation index moves up from 3 to 2. If there were
more sheets, every sheet after "Sheet2" would move up by 1. You would be
better of solving this problem with a VBA macro.


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=51644