I have three named ranges in Column B.
The range data is always numbers.
I am using =OFFSET('Sheet1'!$B3,0,0,COUNTA(Sheet1!!$B3:$B24),-1) to define
Range BFP
I am using =OFFSET('Sheet1'!$B35,0,0,COUNTA(Sheet1!!$B35:$B44 ),-1) to define
Range OHAC
I am using =OFFSET('Sheet1'!$B50,0,0,COUNTA(Sheet1!!$B50:$B65 ),-1) to define
Range MSE
My problem is that when I insert a new row in say the BFP range, the OHAC &
MSE range kept the same starting rows and were off by one row. I removed the
$ in from of the row numbers, but then it added like 10 more rows to the
range.

How do I fix this so that when I add a row in any of the ranges, all the
ranges will stay correct?
Each of the ranges does have a label in Column A one row before the range
actually starts. These will always be one row above the starting range of
numbers. Maybe do a search for the label and start the range one row below?
But then how to end the range? I can create the range in VBA if you have
suggestions? Thanks again!

Thanks!

hi,
these are not named ranges. look up naming ranges in xl help on how to
define a named range. formulas do not name ranges. they just reference a
group of cells on the sheet and i think that may be your problem. if BFP does
not appear in the name box left of the formula bar, then it's not a named
range.
to name range BFP... highlight the range B3:B24. ckick Insertnamedefine.
enter BFPclick addclick ok. the name BFP will now appear in the name box.
you can add or delete rows to BFP. the named range will expand or shrink as
to add or delete rows. any named ranged below BFP will shift up or down as
you add or delete rows. to count BFP. use this formula....=counta(BFP). you
can put it anywhere because it's not referencing a group of cells specified
in a formula. it is referenced a defined named range and the fomula will
always reference BFP regardless of how many row you add and i think your
named ranges will now do what you want.

hope this clairfied things.
Regards
FSt1

"David" wrote:

I have three named ranges in Column B.
The range data is always numbers.
I am using =OFFSET('Sheet1'!$B3,0,0,COUNTA(Sheet1!!$B3:$B24),-1) to define
Range BFP
I am using =OFFSET('Sheet1'!$B35,0,0,COUNTA(Sheet1!!$B35:$B44 ),-1) to define
Range OHAC
I am using =OFFSET('Sheet1'!$B50,0,0,COUNTA(Sheet1!!$B50:$B65 ),-1) to define
Range MSE
My problem is that when I insert a new row in say the BFP range, the OHAC &
MSE range kept the same starting rows and were off by one row. I removed the
$ in from of the row numbers, but then it added like 10 more rows to the
range.

How do I fix this so that when I add a row in any of the ranges, all the
ranges will stay correct?
Each of the ranges does have a label in Column A one row before the range
actually starts. These will always be one row above the starting range of
numbers. Maybe do a search for the label and start the range one row below?
But then how to end the range? I can create the range in VBA if you have
suggestions? Thanks again!

Thanks!

Looks like I was trying to make it too complicated. Although not technically
named so you can see them in the dropdown box, they were dynamic ranges...but
I tried it you way, got what I needed, and THANK YOU VERY MUCH!!!


"FSt1" wrote:

hi,
these are not named ranges. look up naming ranges in xl help on how to
define a named range. formulas do not name ranges. they just reference a
group of cells on the sheet and i think that may be your problem. if BFP does
not appear in the name box left of the formula bar, then it's not a named
range.
to name range BFP... highlight the range B3:B24. ckick Insertnamedefine.
enter BFPclick addclick ok. the name BFP will now appear in the name box.
you can add or delete rows to BFP. the named range will expand or shrink as
to add or delete rows. any named ranged below BFP will shift up or down as
you add or delete rows. to count BFP. use this formula....=counta(BFP). you
can put it anywhere because it's not referencing a group of cells specified
in a formula. it is referenced a defined named range and the fomula will
always reference BFP regardless of how many row you add and i think your
named ranges will now do what you want.

hope this clairfied things.
Regards
FSt1

"David" wrote:

I have three named ranges in Column B.
The range data is always numbers.
I am using =OFFSET('Sheet1'!$B3,0,0,COUNTA(Sheet1!!$B3:$B24),-1) to define
Range BFP
I am using =OFFSET('Sheet1'!$B35,0,0,COUNTA(Sheet1!!$B35:$B44 ),-1) to define
Range OHAC
I am using =OFFSET('Sheet1'!$B50,0,0,COUNTA(Sheet1!!$B50:$B65 ),-1) to define
Range MSE
My problem is that when I insert a new row in say the BFP range, the OHAC &
MSE range kept the same starting rows and were off by one row. I removed the
$ in from of the row numbers, but then it added like 10 more rows to the
range.

How do I fix this so that when I add a row in any of the ranges, all the
ranges will stay correct?
Each of the ranges does have a label in Column A one row before the range
actually starts. These will always be one row above the starting range of
numbers. Maybe do a search for the label and start the range one row below?
But then how to end the range? I can create the range in VBA if you have
suggestions? Thanks again!

Thanks!