Assistance with OFFSET Function please.

[Nobby]

I have named a range "MainDataTable" and this refers to a table of data that currently runs from A6 - L28.

I have defined the range as follows:

=OFFSET('Main Data Table'!$A$6:$L$28,0,0,COUNTA('Main Data Table'!$A:$L),1)

However, when I use Edit/GoTo, it just selects Col A, whereas it should select the whole range A:L

Any ideas where I am going wrong in my OFFSET function?

Cheers

Nobby

 

[Aladin Akyurek]

On 2002-05-02 01:03, Nobby wrote:
I have named a range "MainDataTable" and this refers to a table of data that currently runs from A6 - L28.

I have defined the range as follows:

=OFFSET('Main Data Table'!$A$6:$L$28,0,0,COUNTA('Main Data Table'!$A:$L),1)

However, when I use Edit/GoTo, it just selects Col A, whereas it should select the whole range A:L

Any ideas where I am going wrong in my OFFSET function?

Cheers

Nobby

Which column in A to L is of numeric type? Is that a column that also reflect the changes to the data area between A and L?

 

[Nobby]

The table records details of customer complaints, and the columns include customer name, account number, staff member dealing, nature of complaint by code etc. So whilst some of the data may be numerical, none of it is calculated.

Effectively, each new complaint will fill a complete row from A?;L?

The analysis of the table is done in pivot tables, which is why I need to be able to point directly to the dynamic range of Main Data.

Nobby

 

[Aladin Akyurek]

On 2002-05-02 01:38, Nobby wrote:
The table records details of customer complaints, and the columns include customer name, account number, staff member dealing, nature of complaint by code etc. So whilst some of the data may be numerical, none of it is calculated.

Effectively, each new complaint will fill a complete row from A?;L?

The analysis of the table is done in pivot tables, which is why I need to be able to point directly to the dynamic range of Main Data.

Nobby

Nobby,

Please try to be precise. In exchange, I'll tell you what is wrong with the formula that you used .

So, Are the account numbers true numbers? Are they in column B? If not, Do you have a date column, and if yes, which one is it?

Aladin

 

[Nobby]

Col A - Custom Format - mmm - Month date entered here
Col B - Custom Format - ddd-mmm-yyyy - the column is blank at the moment and will now be deleted from the sheet.
Col C contains the account number, which is a mixture of letters and numbers e.g S342ABC. This is formatted as General.
All other columns contain text and are formatted as General.

Does this help?

Nobby

 

[Aladin Akyurek]

On 2002-05-02 02:29, Nobby wrote:
Col A - Custom Format - mmm - Month date entered here
Col B - Custom Format - ddd-mmm-yyyy - the column is blank at the moment and will now be deleted from the sheet.
Col C contains the account number, which is a mixture of letters and numbers e.g S342ABC. This is formatted as General.
All other columns contain text and are formatted as General.

Does this help?

Nobby

OK.

I'll assume that the labels like Account Number, etc. are in row 6 and the real data start at row 7.

Activate Insert|Name|Define.
Enter Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

1) if column A is numeric, which means that =ISNUMBER(any-cell-in-A) returns TRUE,

=MATCH(9.99999999999999E+307,'Main Data Table'!$A:$A)

2) if column A is not numeric,

=MATCH(REPT("z",40),'Main Data Table'!$C:$C)

Note. It must be either (1) or (2).

Activate Add. (Don't leave yet the Define Name window.)

Enter PivotRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=Nrecs-(ROW('Main Data Table'!$A$6)-1)

I expressly called this one PivotRecs, because Pivot Tables must have a range which includes the row with the labels, that is, Pivot Tables needs to know the labels heading the data.

Activate Add. (Don't leave yet the Define Name window.)

Enter MainDataTable as name (a name that you already have) in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET('Main Data Table'!$A$6,0,0,PivotRecs,11)

Since you're going to delete column B from the current data area, the area will now cover A thru K. That's 11 columns, whence 11 in the formula. This number can also be computed dynamically if the sheet Main Data Table doesn't contain anything but the data you want to feed to the PivotTables. Anyway, if you change the number of consecutive columns, you'll need to adjust this number.

Activate OK.

You're done.

Back to the original formula, as I promised:

COUNTA is risky, because it's unable to count blanks which leads to an incorrect range.

It did not include every column: it uses 1 at the end.

It will not be able to include any row of data beyond row 28.

Aladin

 

[Drew]

Aladin,
I'm probably missing something here but why define PivotRecs?

Could it be done in just two steps?
=MATCH(REPT("z",40),'Main Data Table'!$C:$C)-1 (named Nrecs)
=OFFSET('Main Data Table'!$A$6,0,0,Nrecs,11)
The offset is anchored on the label $A$6.

Just Curious.
Thanks,
Drew

 

[Aladin Akyurek]

On 2002-05-02 07:14, Drew wrote:
Aladin,
I'm probably missing something here but why define PivotRecs?

Could it be done in just two steps?
=MATCH(REPT("z",40),'Main Data Table'!$C:$C)-1 (named Nrecs)
=OFFSET('Main Data Table'!$A$6,0,0,Nrecs,11)
The offset is anchored on the label $A$6.

Just Curious.
Thanks,
Drew

Drew,

That's a good question. You can find the answer by inserting new rows before the row where the data start. Type in a empty cell

=MainDataTable

Activate this cell, go to the Formula Bar, select the formula, hit F9. You'll see a constant array whose last few members are all zeroes indicating extraneous bits of data that does not belong to the MainDataTable.

Aladin
This message was edited by Aladin Akyurek on 2002-05-02 07:32

 

[Drew]

Aladin,
I see. Makes sense now. Great way to keep it truely dynamic.
Thanks for the explanation.
Drew