Named Ranges

I was reading http://www.ozgrid.com/Excel/DynamicRanges.htm to learn excel 2007 Named Ranges. It says,

"5:Expand Down Based on Another Cell Value
    Put the number 10 in cell B1 first then:
In the Refers to box type: =OFFSET($A$1,0,0,$B$1,1)
Now change the number in cell B1 and the range will change accordingly".

I can't get this to work. I want the Named Range to select cells in column A where there is a value in Column B.

Can anybody help?
Mr_ShawAsked:
Who is Participating?
 
barry houdiniCommented:
Try using this version, assuming you have data in B from B1 without gaps

=OFFSET($A$1,0,0,COUNTA($B:$B),1)

Excel should add sheet names for you

reagrds, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.