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