Named Dynamic Range Excel 97

[Menessis2]

I have a bunch of data that will keep changing. Each named range is arranged in columns on a separate tab. I have gotten far enough that I can add data to it ok but don't know how to set up the range in the first place. IE a named Dynamic Range???? So far I just have the range set up by naming it and using a pre defined number of rows. Dont want to leave it like that. Some day sooner or later it will be a problem and I would like to learn how to do it anyways.
I'm using Excel 97 so I can't use the built in method on naming a dynamic range that I have read about.

Thanks
Menessis

 

[jim may]

To use a dynamic range name against a single column listing with its header in cell C1...
In the Range Name manager you will see the following:
Name: test
In the formula textbox:
=OFFSET($C$2,0,0,COUNTA($C:$C)-1,1)

After entering the $C$2, the 0 informs excel to move 0 rows, the the next 0 informs excel to move 0 columns, the counta function returns the count of the non- blank cells - the depth of the rows of the range - less 1 (-1) to exclude the header; then finally the last 1 informs excel to make the range 1 column wide

As you continue to enter data in cells c8,c9, c10 the Range name test will continue to cover from C2 to c8, c9 and c10...


Hope that helps...

 

[Menessis2]

Hi Jim....Thanks for the imfo! I was lost as far as what to use for the "formula". I haven't tried it yet...but I will let you know.



Thanks
Menessis

 

[Menessis2]

Hey Jim that works great. I think I have all the info I need to go ahead and finish the project once and for all

Thanks again
Menessis