Vlookup REF error

W

William Beard

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
 
L

Leith Ross

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

--

Regards,
Nigel
[email protected]
Click to expand...

Hello William,

The worksheets are part of what is known as a "Collection Object"
These objects are special arrays. Each element of the collection exist
as a pair of values: a Key, and an Item. The key is string that is use
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 b
its sequential index value, i.e. 1,2,3, etc. or by the friendly nam
"Sheet1", "Sheet2", "Sheet3". The system reassigns the creation indice
whenever an object is deleted from the collection. Deleteing "Sheet2
will cause "Sheet3"'s creation index moves up from 3 to 2. If there wer
more sheets, every sheet after "Sheet2" would move up by 1. You would b
better of solving this problem with a VBA macro

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/
 
W

William Beard

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?

Hi

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

William Beard

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


Leith Ross said:
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/)
Click to expand...
 
Top