+ Reply to Thread
Results 1 to 15 of 15

offset not functioning

  1. #1
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    offset not functioning

    Hello

    I have the offset with counta to show only active entries but for some reason on my graph, it is still showing blank entries

    basically, im showing data for states, some states have 6 cities, some have 4. the graph looks fine when it has 6 cities showing, but when its 4 it messes up and shows a 0 on the graph. here is my main offset formula

    =OFFSET(Reference!$A$19,0,0,MAX(1,COUNTA(Reference!$A$19:$A$26)-COUNT(Reference!$A$19:$A$26)),1)

    i have attached a sample workbook, if someone can pls help me out on this..ive been stuck on this for a whole day and half :S.
    Attached Files Attached Files
    Last edited by jw01; 03-11-2011 at 03:35 PM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: offset not functioning

    Hi,

    You could change your range definition to this

    =OFFSET(Reference!$A$19,0,0,MAX(1,SUMPRODUCT(NOT(Reference!$A$19:$A$25="")*1)-COUNT(Reference!$A$19:$A$25)),1)

    You'll also have to change the sales phase series data to the appropriate named range - currently it's fixed.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: offset not functioning

    Or maybe use one of the columns with values

    CHTLABEL:
    =OFFSET(Reference!$A$19,0,0,MAX(1,COUNT(Reference!$B$19:$B$25)),1)
    Cheers
    Andy
    www.andypope.info

  4. #4
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello

    when i select i.e. canada east or west where we only have 4 cities in total, it's still showing two 0 (extra spaces) on the graph.

    my CHTLABEL is what you mentioned, but its still showing 0s...any suggestions?
    thx u so much

  5. #5
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    if i hide the rows i.e. if i select my canada west region...the range is A19:A23....so if i hide the extra to rows below it, the graph removes the 0s and shows only the data i want...but if i select a region that whose range is a19:a25, bc its hidden..it doesn show

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: offset not functioning

    Did you read sweep's comment about the data series that has fixed range references.

    Hiding rows will remove the information from the chart is the Plot Visible cells only option is set.

  7. #7
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello

    I didnt quite follow when he said fixed. im assuming he meant to not make the series data for sales absolute? i.e.

    sales phase
    series name:=Reference!$C$18
    series value: ='Master LVO Template (North America Summary - P2 2011) V.1.1.xls'!CHTLABEL

    is that what he meant?..sorry for the confusion and thxs for you rhelp.

  8. #8
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello

    would you be able to post an example from my sample that i provided...id b very grateful, thx you!

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: offset not functioning

    In your example this is the series formula for Sales Phase is

    =SERIES(Reference!$C$18,Reference!$A$19:$A$25,Reference!$C$19:$C$25,2)

    where as it should be

    =SERIES(Reference!$C$18,Reference!CHTLABEL,Reference!CHTSALES,2)

  10. #10
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello

    I have made my CHTLABEL defined as yours
    =OFFSET(Reference!$A$19,0,0,MAX(1,COUNT(Reference!$B$19:$B$25)),1)

    and for my sales, on my current graph...this is what it shows
    sales:
    series name:=Reference!$C$18
    series values:=Reference!$C$19:$C$25

    when i change the series name to:
    =SERIES(Reference!$C$18,Reference!CHTLABEL,Reference!CHTSALES,2)

    it does not show the sales portion. sorry but im so confused :S

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: offset not functioning

    See attached
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello
    i have attached the most recent workbook that im working under. the file is passwrd protected, however you can run the macro to unprotect all sheets.....

    i really apprecaite the help. its just unreal and confusing that the 0 wont go! thx you
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello Andy
    thx you for your prompt response, however in your workshet, its only showing one city.

    i would prefer if the all the cities/region appear on the graph, like i have it intially (minus the 0 or blank that is appearing on the graph). thx you for your input and kindly let me know. thxs

  14. #14
    Forum Contributor
    Join Date
    11-26-2010
    Location
    usa
    MS-Off Ver
    Office 365
    Posts
    1,224

    Re: offset not functioning

    Hello ANdy

    thxs for your help. i had to use excel 03 and managed to change the series formula. thx you again!!!!!!!
    Last edited by jw01; 03-11-2011 at 03:35 PM.

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: offset not functioning

    in xl2007 it is best to use the Select data dialog to alter named range references.
    Some times when using the formula bar to alter series formula it will not allow you to exit edit mode and retain changes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1