Dynamic Named Ranges

[HProutt]

I am trying to create a dynamic named range (actually a series of them) and have a summary table above them for easy viewing. For example my data starts in row 13 and my summary table goes from rows 4:10. My data goes from O13:O35 and is named "Variable1" my named range is defined as:

Variable1=OFFSET(Data1!$O$13,0,0,COUNTA(Data1!$O:$O),1)

In O4 I have the formula =min(Variable1) and in O5 I have =max(Variable1). The error I am getting is a circular reference, although I thought I was telling my range to start at O13 and go down as long as there is something in the cells.

Does anyone have a better formula for creating a dynamic range that won't give me a circular reference?

 

[AnotherHiggins]

The problem causing the circular reference is:
[tab]COUNTA(Data1!$O:$O)
is part of the Named Range Variable1, and a cell in column O refers to the Named Range Variable1.

Change COUNTA(Data1!$O:$O) to something like
[tab][COLOR=blue white]COUNTA(Data1!$O13:$O65536)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

.... Alternately, you could count another column that doesn't include summary data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[HProutt]

If that is the case, then I only need to use the countA part of the formula to name the range, the offset function that I am using to define the starting point of my range becomes moot, correct?

 

[SkipVought]

COUNTA returns the number of cells with data in the given range. However, if you have data outside the list or table range, within the range in COUNTA, including headings, you must account for that in order to calculate the number of rows of data within your list or table.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]