We help IT Professionals succeed at work.

'VLOOKUP' not updating any new information added.

Ian192
Ian192 asked
on
892 Views
Last Modified: 2008-02-01
I am working on a workbook with two worksheets at present (One with cost prices and the second with formulas).
I am using vlookup from the second sheet to to find the part and price on sheet one (=VLOOKUP(G13,Sections,12) for example.
This works fine for all information entered before i created the Table Array 'Sections'. Now i am adding more parts but for some reason the formula cannot see the new parts and prices entered and is just leaving me with an empty cell instead of the amount it is suppose to show. I have tried refreshing  the worksheet and still nothing.

Comment
Watch Question

Commented:
Hi Ian192,

Are you sure that "Sections" is referring to the entire table?
Any chance you could upload the workbook for us to take a peek? (try www.ee-stuff.com)

Cheers!

Author

Commented:
https://filedb.experts-exchange.com/incoming/ee-stuff/2120-Quotation-Buildup--Ian-Only-.zip

Example in workbook sheet CS013 was added before table array was added and is working as you can see.
Parts added after are CS163, CS206, GK036, WP02, WP03, NC033XSLF

Cheers in advance
Hi,
It seems the range named "sections" is an absolute range and not a dynamic one. Try using offset to create a dynamic range as follows.
Suppose yours data range starts in A1 on Sheet1
Then in
Insert->Name->Define
Use in
--Names in Workbook-----Sections
--Refers to-----------------=offset(Sheet1!$A$1,,,counta(Sheet1!$A:$A),counta(Sheet1!$1:$1))

Now whenever you add some new data starting from Column A, the range will automatically expand and range "Sections" will include them automatically.

HTH
Regards
Kanwaljit
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
The formula in column H needs to be:

=VLOOKUP(G13,Sections,12,FALSE)

as the price list is not necessarily sorted in order.
Hi Justdownloaded the file. Seems very strange. Let me check
seems reloaded the page centuries ago......:(
Hi Patrick,
You are right, the formula does works, but I couldn't see any duplicate values there. Also there can't be any largest or smallest values in case of lookup value column being a text column (like column A here in "Prices" sheet). What is the reason ?
Kanwaljit
In other words, how there can be an approximate match in case of text values....?
and Ian, it is not a good thing to enter the data by leaving some blank rows and columns in between. That is very likely to invite trouble...
kanwal_no1,

FALSE is confusing in that is copes with unsorted lists as well as exact matches - at the same time. Of course with a text match there are no approximate matches but the list will probably not be sorted so I put in FALSE to cover that eventuality.

Patrick
Thanks Patrick, but I still couldn't see how the vlookup can return a zero despite the lookup value being present row. There is something strange there. I entered CS206 in cell A3 and still no change in results. What is the mystry ?
Kanwaljit

Author

Commented:
Excellant cheers guys =Prices!$A$3:$L$65536 worked for me :)... i am a total novice but normally get by  with the basics...
Ian192 - Thanks for the grade. - Patrick
Hi Patrick,
Ian might have got the answer, but I still lost. Will you please pull me out......:)
Kanwaljit
Kanwaljit,

Let me have another look.

Patrick
Kanwaljit,

It was because the range named 'Sections' needed to cover the range Prices!$A$3:$L$65536. But then I think you knew that. :))

Patrick
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.