Share via

Create Sparklines based on a formula

Anonymous
2016-04-12T11:56:59+00:00

Hi all,

I have a table of data in which each row is a different dates data. 8 columns of different stock levels on that date.

I want to show a spark line (or graph) of the change in stock since last week. I have tried to enter a formula into the Sparkline data range but it gives the error "Data source reference is not valid"

Any ideas how I can do this? I would rather not create another table next to it

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-04-12T15:00:19+00:00

Sparklines can only reference ranges, so you will need to create another table that includes formulas to pull the latest data so that the sparkline will update.

For example, if your table is in A:I, then this formula:

=VLOOKUP(LARGE($A:$A,8-ROW(A1)),$A:$I,COLUMN(A1),FALSE)

copied down for 7 rows and across for 9 columns - will pull the latest data and allow the sparklines to update.

Was this answer helpful?

2 people found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest