Tom Ogilvy wrote:
I think you would need to build a contiguous list (no blank cells) in another
range to do this. As I recall, Named ranges with complex formulas don't work
well in data validation.

you can use a formula like

=IF(COUNTA($F$1:$F$50)=ROW(),OFFSET($F$1,SMALL(IF ($F$1:$F$50<"",ROW($F$1:$F$50)),ROW())-1,0),"")

to get a list with no blanks.

This is an array formula and would be entered with Ctrl+Shift+enter rather
than just enter. then drag fill down the column (entered in G1 and filled
down in the example)

Then your dynamic range would be

Name: List1

Refersto: =Offset(Sheet1!$G$1,0,0,counta(Sheet1!$G$1:$G$50), 1)

where G1 and down is where you formula is placed.


Tom,
Thanks it works! Now I have to see where and how to use it. I was
having problems with it until I reread your post and noticed the
Ctrl+Shift=Enter that I missed before. This part I did not understand.
Now that I have it working I am going to see if I can have the list
change as I change budget years. Since each budget has different line
items. I currently use
=SUMIF(INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&"'!A :A"),$B11,INDIRECT("'Budget"&RIGHT(YEAR($I$1),2)&" '!D:D"))
to get the money and expenses for that year..now if I can make the range
of items change to reflect that budget year would be nice, but not
necessary.

Thanks
Harry