• 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...

  • 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

Tymko

New Member
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!!
 
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
 
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.
 
It worked OK for me.
I have uploaded a variant that uses a Named formula rather than helper cells.
Any chance this would work with a pivot table rather than a regular table? Sample pivot table attached. I would like to dynamically lookup an Item on a separate sheet and generate a sparkline for the 12 months of data without cluttering the new sheet. The raw data tab is for illustration only, my real pivot is fed from an ODBC connection. The underlying query is not really amenable to spitting out data into a table without extensive customization which then makes it more susceptible to breaking in unanticipated situations.
 

Attachments

  • Sparkline question.xlsx
    15 KB · Views: 12
I had forgotten all about this!
You can do the same thing as for the table but bear in mind that INDEX lookups are not robust when it comes to the normal pivot table transformations or even data refresh. Array entered GETPIVOTDATA is more robust but cannot provide the data for a sparkline without first writing it to a helper range (though this does not necessarily need to be visible to the user).
I have also included XLOOKUP because, in the latest versions of Excel, it should be used in place of any of the traditional lookup formulas.
 

Attachments

  • Sparkline question (PB).xlsx
    25.9 KB · Views: 24
I had forgotten all about this!
You can do the same thing as for the table but bear in mind that INDEX lookups are not robust when it comes to the normal pivot table transformations or even data refresh. Array entered GETPIVOTDATA is more robust but cannot provide the data for a sparkline without first writing it to a helper range (though this does not necessarily need to be visible to the user).
I have also included XLOOKUP because, in the latest versions of Excel, it should be used in place of any of the traditional lookup formulas.
Thank you for the lightning fast response and for your time!

The solution as it stands won't work for my situation as the pivotBody reference is static, the size of my pivot table changes based on the data returned from the query (different number of items each time). Any suggestions on how to work around that? One possible solution that comes to mind is if I know that the number of items doesn't exceed n and the number of columns (months in my example, always = 12), I could set the pivotBody to exactly that size of a range (n x 12).

Thoughts?
 
Here's one method. Assuming that row count changes, but not the header section (i.e. Row 3 & 4).

See attached. Refresh pivot (Right click on Sheet1!B1, and refresh). It will automatically add D as new item in dropdown. Change selection and it will update.

Note that, Sparkline without accompanying data and/or KPI narrative is bit useless.

If you need to make it dynamically obtain item list, you need separate named range for that, and change reference in MATCH function used in SparkRange to a cell.
 

Attachments

  • Sparkline question (PB).xlsx
    21.2 KB · Views: 30
Hi there,

I'm sort of a Excel beginner and wasn't apply to apply this solution to my problem.

I'm looking to return a sparkline on my dashboard view on sheet1 by looking up the client code and weekly trend range from the Data sheet.
A VLOOKUP to return the sparkline on my data sheet returned a 0

I've attached my workbook.

Can this be done?

Thanks!
 

Attachments

  • sparklinetest.xlsx
    12.1 KB · Views: 6
bjneuw
As written in Forum Rules
  • Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
...but in this time I tried to guess - what would You want to get?
Cell B3 comes from Data-sheet
other cells via VLookup from Sheet1 based A-column values.
>> if this no match, then open a new post.
 

Attachments

  • sparklinetest.xlsx
    13.9 KB · Views: 18
Thanks.
I added some more details to be more clear in my new post.

Thanks though!


My bad!
Your idea did work!

I can just shrink those new columns so they are not visible and even when I change the client code my sparkline updates.

thank you!
 
Last edited:
Back
Top