Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Assistance with OFFSET Function please.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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?



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,030
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •