Dynamic Ranges - Missing last entry

[rojj]

I have created a dynamic range for an excel 2000 spreadsheet that I
reference by using a Data Validation List...

The dynamic range almost works, except that is leaves whichever is my
last entry to the list off, of my "drop-down" box... So anything I add
to the list whether is is several or a couple, will show up in my
drop-down box except the very last entry....

My list that I am creating the dynamic range from runs from cell

A3 thru A20

The formula for the dynamic range I have is:
=OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A:$A))

Can anyone recognize something that might be wrong with my formula, or
do you know of a website that could possibly help??

 

[Bernie Deitrick]

rojj,

Make sure that you don't have any blank cells or (hidden rows that are
blank) in the block of cells in column A.

HTH,
Bernie
MS Excel MVP

 

[rojj]

Yeah I looked into that and I did have a blank cell in there, I took i
out and it worked..... However, now Can I create a dynamic range for
few lists....

I have 3 lists on a the same sheet though that I want to create
dynamic range for .... The dynamic range I created for my 1st lis
works, but the other two give me an error message: "The Sourc
Currently evaluates to an error. Do you wish to continue?"

The formula I used for the other 2 lists is the same as the one tha
works for my 1st list, the only thing different is that the other tw
lists reference their appropriate Columns.. But I get this message...

Do I need the lists on seperate sheets, or do I need to change m
formula around to get the other two lists to work as well?

The formula I used for the "working" dynamic range is
=OFFSET(Sheet2!$A$4,0,0,COUNTA(Sheet2!$A:$A))
-for the othe r2 lists I just changed to column reference from "A" t
"C" and "E" which hold my other lists..

Any help is greatly appreciated

 

[Bernie Deitrick]

Sorry,

I can't replicate your error: I'm able to make as many dynamic range
names as I want.

The only thing I can recommend is to make sure your formulas are
correct.

HTH,
Bernie
MS Excel MVP

 

[rojj]

I will look closer at my formulas though...

But basically I should be able to use the same formula and just chang
the column reference to whichever column holds my other lists, correct

 

[Bernie Deitrick]

That's correct: just make sure that you change all three instances of
the column letter....

HTH,
Bernie
MS Excel MVP

 

[rojj]

Thank you, I finaly got it to work how I want it to...