named range help

[captainwicked]

I have created a value for a range which i reference in a macro that
creates a pivot table. We add new data each month and generate a new
table so we needed to keep the range of cells dynamic.

Here is the definition for the name i use:

=OFFSET('C:\Monthly Stat Project\[I&RC Monthly Stats Count
TEST.xls]MonthlyCount_data'!$A$1,0,0,COUNTA('C:\Monthly Stat
Project\[I&RC Monthly Stats Count
TEST.xls]MonthlyCount_data'!$A:$A),6)

What i want to do is remove the reference to the location of the
workbook "C:\Monthly Stat Project\[I&RC Monthly Stats Count TEST.xls]"

and instead have it just reference the current workbook. Is there a
way to do this?

something along the lines of: =OFFSET(CURRENT WORKBOOK
MonthlyCount_data'!$A$1,0,0,COUNTA(CURRENT WORKBOOK
MonthlyCount_data'!$A:$A),6)


Any help would be greatly appreciated.

Thanks

 

[bagger01]

there is a nice neat way of sorting this that I discovered some time ago which is now invaluable to myself.
assuming you have data in column A you need two rows free at the top ABOVE your headings.
In cell A1 type this:
=MAX(MATCH("*", $A$2:$A$10000, -1), MATCH(1E+306,A$A":A$A10000,1))
In cell A2 put the number 1
This formula will give you the row number of your last entry (it even takes into account any blanks and such like)
Now call this Maxrow(define as named range)
Now go into dinfing the named range again and type in the following
=offset(Sheet2!$A$3,0,0,Maxrow,1)
Now give this the name of the range you wish to use for your data purposes.
The above needs to be changed to suit your data range but normally the only thing that needs changing is the 1 after Maxrow, this is the number of columns you wish to add to the name.
Hope this is useful land makes sense. This way is foolproof as long as no-one removes the data in A1, A2 or your heading in A3.