Results 1 to 9 of 9
  1. #1
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    237
    Thanks
    23
    Thanked 8 Times in 8 Posts

    Excel 2016 Chart Dynamically Updating Data (Point) Label

    In an Excel chart data (point) label, I want to create a dynamic label that will update when the chart data is updated.

    I am using this formula:
    Code:
    LOOKUP(2,1/(C41:S41<>""),C41:S41)
    to select the right-most cell with data in a row.

    The next cell I want to look to for data is the row 1 or more rows above the cell the formula yields. Is there a way to do that and, if so, how can I do that?

    I can't use the variant of that formula on the row(s) above because every cell in that row range is filled.

    FYI: This is just part of the formula in a cell that I then use to populate the data label.
    Ray
    OS: All computers are Windows 10 Pro (1803)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,096
    Thanks
    167
    Thanked 836 Times in 761 Posts
    The next cell I want to look to for data is the row 1 or more rows above the cell the formula yields.
    Hi Ray,

    The first part with your lookup formula is very clear. The second part is a bit fuzzy. Can you elaborate more on that? Are you looking for data in the rows above and in the same column as the cell that the lookup returned? Can you post a sample?

    Thanks,
    Maud

  3. #3
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    237
    Thanks
    23
    Thanked 8 Times in 8 Posts
    In the attached file, I have highlighted the key cells.

    The formula currently selects the right-most cell's contents. I want to add after an "&" a formula that will look to that same cell, but, instead of choosing that cell's contents, look up 1, 2, 3 of more rows and choose that cell. For the first row, it would return "Item5 5".
    Attached Files Attached Files
    Ray
    OS: All computers are Windows 10 Pro (1803)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,096
    Thanks
    167
    Thanked 836 Times in 761 Posts
    Hi Ray,

    Place the following formula in cell K2 then copy down:

    =LOOKUP(2,1/(A2:J2<>""),A2:J2)&" "&MATCH(LOOKUP(2,1/(A2:J2<>""),A2:J2),A2:J2,0)

    HTH,
    Maud

    ray.png
    Last edited by Maudibe; 2019-02-09 at 14:18.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,096
    Thanks
    167
    Thanked 836 Times in 761 Posts
    Hi Ray,

    OK, it just clicked what you are trying to do. You don't want to append the column number rather the value of some cell in a specific row above it within the same column.

    Will take another look.

    Maud

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    3,096
    Thanks
    167
    Thanked 836 Times in 761 Posts
    Try this in cell K2 then copy down:

    =LOOKUP(2,1/(A2:J2<>""),A2:J2)&" "&INDEX($A$1:$J$4,1,MATCH(LOOKUP(2,1/(A2:J2<>""),A2:J2),A2:J2,0))

    Change the 1 in the formula to the row within the range you want to return.

    HTH,
    Maud

    ray2.png
    Last edited by Maudibe; 2019-02-10 at 09:26.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    WildcatRay (2019-02-10)

  8. #7
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    237
    Thanks
    23
    Thanked 8 Times in 8 Posts
    Yes. The table I will be using this on is the number of months something has been for sale. The actual figure "Item 1, Item 2, etc." is the value at X number of months in. Ultimately, I will end up with "Item 1 @ 3 months" or whatever the specific label will be.
    Ray
    OS: All computers are Windows 10 Pro (1803)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  9. #8
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    237
    Thanks
    23
    Thanked 8 Times in 8 Posts
    I have my formulas working except if the last filled cell is (still) zero. Now I will have to look that solution up.
    Ray
    OS: All computers are Windows 10 Pro (1803)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

  10. #9
    3 Star Lounger WildcatRay's Avatar
    Join Date
    Feb 2010
    Location
    Columbus, OH
    Posts
    237
    Thanks
    23
    Thanked 8 Times in 8 Posts
    Neither using isnumber nor not(isblank in my formula will in my mind properly recognize the number zero in the last populated cell and return it in my formula.
    Code:
    =A3&" "&A$2&CHAR(10)&TEXT(LOOKUP(2,1/(ISNUMBER(C80:AM80)),C80:AM80),"#,##0")&" PPM @ "&INDEX($C$76:$AM$84,1,MATCH(LOOKUP(2,1/(NOT(ISBLANK(C80:AM80))),C80:AM80),C80:AM80,0))&" Months"
    Any suggests are welcome.
    Ray
    OS: All computers are Windows 10 Pro (1803)
    Antivirus: Kaspersky; Anti-malware: SpywareBlaster, Malwarebytes, SuperAntiSpyware

Tags for this Thread

Posting Permissions

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