SPARKLINE error when data-pull cell contains formula. Is there a workaround for this?

1
votes

I am trying to have the data cell for a =SPARKLINE bar graph pull data from another sheet. For some reason, =SPARKLINE doesn't recognise the cell's data if it's pulled using a formula. Is there any workaround for this, please?

I've tried pulling the data in multiple ways, pulling into different cells first from the same sheet. I've also tried pulling the data using =TEXT, but doesn't seem to recognize it.

Example:

In Cell A1 of Sheet2, I have the formula ='Sheet1'!I21, so Cell A1 on Sheet2 will display the same content as what is in Cell I21 on Sheet1.

On Sheet2, I have a SPARKLINE graph in Cell A2, with the code of 

=sparkline(A1,{"charttype","bar";"max",100;"color1","#1A73E8"})

in an attempt to pull the data from Cell A1.

I have alternatively tried 

=sparkline((text(A1,"#")),{"charttype","bar";"max",100;"color1","#1A73E8"})

The =SPARKLINE command doesn't appear to recognize the data in cell A1 on Sheet2, providing the error of

SPARKLINE requires more data points. Expected: 1, found: 0.

Say Cell I21 on Sheet1 has a value of "20". If I replace the formula on cell A1 of Sheet2, the =SPARKLINE chart will work fine. If I use the formula to pull the data, I get the error.

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

After playing around a little more, I was able to find a solution. Using the =VALUE formula seemed to fix this. So, instead of ='Sheet1'!I21, =value('Sheet1'!I21) worked just fine. Just in case anyone finds the same issue, I'll mark this as the answer.

0
votes
=sparkline(A1,{"charttype","bar";"max",100;"color1","#1A73E8"})

works, so you have some issue other than syntax. Maybe locale?

SO54291840 example

IMPORTRANGE won't necessarily break the above - Text format would however.