Vlookup REF error

  • Thread starter Thread starter William Beard
  • Start date Start date
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
 
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.
 
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...
 
Back
Top