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

NARAYANK991

Excel Ninja
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
 

Peter Bartholomew

Well-Known Member
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.
 

ns29

New Member
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

Peter Bartholomew

Well-Known Member
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

ns29

New Member
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?
 

Chihiro

Excel Ninja
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

Top