Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
I have a sheet with a layout as such:
A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Hi,
Is there anything in A1? if so subtract one in your formula or start the offset from A1 instead of A2. =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Second possibility is that there is a cell with a spacebar in it or a formula that returns "". If this helps, please click the Yes button Cheers, Shane Devenshire "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
You have 2 questions.
1. A blank cell at the bottom of the range. You are starting in cell A2. As a guess you have some text in cell A1? Assuming that to be true when you do the CountA(A:A) you will count the text in cell A1 and end up with a rnage that is one row too long. to fix it just subract 1 from the count... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) 2. Your vlookup returns #Ref. VLookup looks into a range of cells that is x rows long by y columns wide. Your range is only 1 columbn wide, so when you as it to return the second column over it fails. Change you range to be 3 columns wide. =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,3) -- HTH... Jim Thomlinson "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Thanks to both of you geniuses! I thought I was going nuts with the blank line!
I thought in the offset that by starting in row2, that would have been enough to show that yes - I did have header text in row 1. Apparently both parts of the formula required it. The VLOOKUP also works now that the whole range is defined THANKS TONS to both of you! "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
I bet you have an empty cell in column A of that mastercontentslist worksheet.
Remember to look in hidden rows (by autofilter). If you have to keep that cell looking empty, you could use: ="" in that cell If you have to really keep it empty, you could subtract 1 in your formula: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) < AVG Joe wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Spoke too soon?
Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
One way would be to not use the vlookup... Since you are using a dynamic
named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
your definition of Itemlist is 1 column wide (the "1" at the end of the
OFFSET). Your VLOOKUP is trying to return the 2nd colulmn from this 1-column range, hence the #REF "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
First -thanks for everybody's help- but I think I need to rephrase the new
problem I declared the dynamic range for all 3 columns and the offset for text in row one - the lookup now works fine. What also happened is that I was using the named range in a data validation, so that the user could only select from the text itemsL Bed Table and so on... What has happened now is that the DV dropdown shows all three valuesL Bed 2.50 ea So in order to have the DV dropdown only contains the text items, I need to define a separate dynamic range for that column only, is that correct? "Jim Thomlinson" wrote: One way would be to not use the vlookup... Since you are using a dynamic named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
You could define a seperate named range and that would work just fine. You
could also use the formulas I posted in my last reply. Using that method you could get away with just one dynamic named range... -- HTH... Jim Thomlinson "< AVG Joe" wrote: First -thanks for everybody's help- but I think I need to rephrase the new problem I declared the dynamic range for all 3 columns and the offset for text in row one - the lookup now works fine. What also happened is that I was using the named range in a data validation, so that the user could only select from the text itemsL Bed Table and so on... What has happened now is that the DV dropdown shows all three valuesL Bed 2.50 ea So in order to have the DV dropdown only contains the text items, I need to define a separate dynamic range for that column only, is that correct? "Jim Thomlinson" wrote: One way would be to not use the vlookup... Since you are using a dynamic named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Thanks for the clarification, Jim. The new lookup returns the amount one row
after what was looked looked up. I thus changed $B$2 to $B:$B This seems to work. I guess the larger question now is: which way is "less volatile"? Two named ranges, & vlookup, or the two offsets? "Jim Thomlinson" wrote: You could define a seperate named range and that would work just fine. You could also use the formulas I posted in my last reply. Using that method you could get away with just one dynamic named range... -- HTH... Jim Thomlinson "< AVG Joe" wrote: First -thanks for everybody's help- but I think I need to rephrase the new problem I declared the dynamic range for all 3 columns and the offset for text in row one - the lookup now works fine. What also happened is that I was using the named range in a data validation, so that the user could only select from the text itemsL Bed Table and so on... What has happened now is that the DV dropdown shows all three valuesL Bed 2.50 ea So in order to have the DV dropdown only contains the text items, I need to define a separate dynamic range for that column only, is that correct? "Jim Thomlinson" wrote: One way would be to not use the vlookup... Since you are using a dynamic named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Volatile referes to a function that must re-calculate every time a
calculation runs. Now() is a good example. Each time a calculation runs it retrieves the most current time. Offset is a volatile function so it has a bit more overhead as it will be constantly recalculating. That is not a big deal if you only have few such formulas. If you have thousands of them then you may notice that your calculations take a long time to run. Since you are using offset to create the dynamic range any function that uses that range is by defualt volatile and has a bit more calc overhead. Throwing a second offset on top of the dynamic range does not force the function to calc twice. I personally do not use VLookup (almost never). VLookup is one of the most dangerous formulas that most people use. The reason is that it hard codes the number of columns over to look. If you insert a column into your source data then VLookup will be returning the wrong columns data. That one can be very difficult to debug. Using Index/Match or Offset as I have in this example the columns over is not hard coded and if a column is inserted then it will still return the correct value. -- HTH... Jim Thomlinson "< AVG Joe" wrote: Thanks for the clarification, Jim. The new lookup returns the amount one row after what was looked looked up. I thus changed $B$2 to $B:$B This seems to work. I guess the larger question now is: which way is "less volatile"? Two named ranges, & vlookup, or the two offsets? "Jim Thomlinson" wrote: You could define a seperate named range and that would work just fine. You could also use the formulas I posted in my last reply. Using that method you could get away with just one dynamic named range... -- HTH... Jim Thomlinson "< AVG Joe" wrote: First -thanks for everybody's help- but I think I need to rephrase the new problem I declared the dynamic range for all 3 columns and the offset for text in row one - the lookup now works fine. What also happened is that I was using the named range in a data validation, so that the user could only select from the text itemsL Bed Table and so on... What has happened now is that the DV dropdown shows all three valuesL Bed 2.50 ea So in order to have the DV dropdown only contains the text items, I need to define a separate dynamic range for that column only, is that correct? "Jim Thomlinson" wrote: One way would be to not use the vlookup... Since you are using a dynamic named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Jim,
Thanks for the explanation. Often things like this are very difficult to sort out in standard "help" mechanisms. This is actually quite fun, if you don't mind pulling a few hairs out! "Jim Thomlinson" wrote: Volatile referes to a function that must re-calculate every time a calculation runs. Now() is a good example. Each time a calculation runs it retrieves the most current time. Offset is a volatile function so it has a bit more overhead as it will be constantly recalculating. That is not a big deal if you only have few such formulas. If you have thousands of them then you may notice that your calculations take a long time to run. Since you are using offset to create the dynamic range any function that uses that range is by defualt volatile and has a bit more calc overhead. Throwing a second offset on top of the dynamic range does not force the function to calc twice. I personally do not use VLookup (almost never). VLookup is one of the most dangerous formulas that most people use. The reason is that it hard codes the number of columns over to look. If you insert a column into your source data then VLookup will be returning the wrong columns data. That one can be very difficult to debug. Using Index/Match or Offset as I have in this example the columns over is not hard coded and if a column is inserted then it will still return the correct value. -- HTH... Jim Thomlinson "< AVG Joe" wrote: Thanks for the clarification, Jim. The new lookup returns the amount one row after what was looked looked up. I thus changed $B$2 to $B:$B This seems to work. I guess the larger question now is: which way is "less volatile"? Two named ranges, & vlookup, or the two offsets? "Jim Thomlinson" wrote: You could define a seperate named range and that would work just fine. You could also use the formulas I posted in my last reply. Using that method you could get away with just one dynamic named range... -- HTH... Jim Thomlinson "< AVG Joe" wrote: First -thanks for everybody's help- but I think I need to rephrase the new problem I declared the dynamic range for all 3 columns and the offset for text in row one - the lookup now works fine. What also happened is that I was using the named range in a data validation, so that the user could only select from the text itemsL Bed Table and so on... What has happened now is that the DV dropdown shows all three valuesL Bed 2.50 ea So in order to have the DV dropdown only contains the text items, I need to define a separate dynamic range for that column only, is that correct? "Jim Thomlinson" wrote: One way would be to not use the vlookup... Since you are using a dynamic named range your funciton is already volatile so there is no harm in using offset again... You dynamic named range will be... =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A)-1,1) Your lookup formula will be =offset(MasterContentsList!$B$2, match(B12, ItemList,0), 0,1,1) -- HTH... Jim Thomlinson "< AVG Joe" wrote: Spoke too soon? Now the dynamic range works, but the DV list that uses shows all three rows? When I dropdown the list I see: Bed 2.50 ea et al. Does this mean i have to define a separate range for only the first column? "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
totally lost - named range and vlookup
Bob,
Thanks for your reply. I still had to read all three initial posts to understand it all. The best way for me is to see the formulas work. I think too often we (less mathmatically inclined) tend to confuse Excel sheets with database tables. "Bob Umlas, Excel MVP" wrote: your definition of Itemlist is 1 column wide (the "1" at the end of the OFFSET). Your VLOOKUP is trying to return the 2nd colulmn from this 1-column range, hence the #REF "< AVG Joe" wrote: I have a sheet with a layout as such: A B C Bed 2.50 ea Table 1.00 ea I have tried to specify a dynamic named range ("ItemList" )as so: =OFFSET(MasterContentsList!$A$2,0,0,COUNTA(MasterC ontentsList!$A:$A),1) The range always includes an empty cell at the bottom of the list. I have deleted everyting below to end, but no change? This is causing the DV list to start at the bottom with a blank... Secondly, when I try to use that named range in a VLOOKUP, to retrieve the amount, I get a #REF error. Here is the formula I am using for VLOOKUP: =IF(ISBLANK(B12),"",VLOOKUP(B12,ItemList,2,FALSE)) Are the errors related? What am I doing wrong |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP with a Named Range argument | Excel Worksheet Functions | |||
Totally Lost - Dates in huge table, Range finder | Excel Discussion (Misc queries) | |||
Vlookup in a named range | Excel Worksheet Functions | |||
Vlookup in large named range | Excel Worksheet Functions | |||
VLookup with concatinated named range | Excel Worksheet Functions |