1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sparkline Lookup data from another sheet

Discussion in 'Ask an Excel Question' started by Tymko, Apr 10, 2018.

  1. Tymko

    Tymko New Member

    Messages:
    1
    Hello folks,
    I have searched google and these forums for a bit but have been unable to find an answer to my question.

    I am looking to insert a sparkline into 'Sheet 2' from data that is in 'Sheet 1'

    Sheet_2 is a dashboard which I am creating and I want the opportunity to be able to change the Item# in the column and have it update the sparkline accordingly

    I tried using the OFFSET function but it is producing an error for me 'Data source reference is not valid'.

    Any thoughts on how I can fix this?

    The Data Range that I am specifying when creating a sparkline is as follows:
    =OFFSET(Sheet_1!A2,MATCH(Sheet_2!A2,Sheet1!A:A,0),1,1,4)



    For example:

    Sheet_1 (this is the sheet with the data)

    Item..........1/2017.......... 2/2017.......... 3/2017.......... 4/2017
    15400020.. 1232............. 23928........... 23212........... 21323
    99402.......123423........... 4243636......... 4523423....... 234234
    10144.......13215...........456454654........46486...........456454
    9102905....545645.......2352323............23535.............235235



    Sheet_2 (this is the sheet with the dashboard)

    Item............. Sparkline
    10144
    99402


    Any help would be appreciated!!
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,615
    Hi ,

    If you can upload your workbook , please do so ; it will mean that those who wish to help can use the data in your workbook , instead of having to copy paste the data you have posted into a new workbook and then work with that data.

    Narayan
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    226
    As Narayan says, we really need to see a workbook.
    That said, I have a defined name "Item" which refers to
    = INDEX( Table1, selector, 0)
    working OK between sheets as the source of sparkline data. The user-specified index 'selector' could easily be replaced by a MATCH function.
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    Perhaps this works...

    Attached Files:

  5. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    226
    It worked OK for me.
    I have uploaded a variant that uses a Named formula rather than helper cells.

    Attached Files:

    GraH - Guido likes this.
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    726
    Hi Peter, I could not figure out what you meant in #3. With #5, now I do... Nice job!

Share This Page