Ian192
asked on
'VLOOKUP' not updating any new information added.
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)
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.
I am using vlookup from the second sheet to to find the part and price on sheet one (=VLOOKUP(G13,Sections,12)
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.
ASKER
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The formula in column H needs to be:
=VLOOKUP(G13,Sections,12,F
as the price list is not necessarily sorted in order.
=VLOOKUP(G13,Sections,12,F
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
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
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
Kanwaljit
ASKER
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
Ian might have got the answer, but I still lost. Will you please pull me out......:)
Kanwaljit
Kanwaljit,
Let me have another look.
Patrick
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
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
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!