Help with sparkline graphs!

Hello PianSa,

I'm Ibhadighi and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

Yes, you can construct a reference to a cell in a sparkline graph using a formula in Excel. However, you cannot directly use the formula within the sparkline's Data Range input. Instead, you have to use the INDIRECT function to convert the text string you've constructed into a valid cell reference.

The INDIRECT function evaluates a text string as a cell reference. Here's an example based on your description:

=INDIRECT("'[Fin-purch.xlsm]Sheet1'! G" & D50)

In this formula, if D50 is 58, the INDIRECT function will evaluate the text string to be '[Fin-purch.xlsm]Sheet1'! G58 and return the value from that cell.

However, the INDIRECT function works with sparklines a bit differently since sparklines expect a range. You need to construct the entire range as a text and then use INDIRECT. Here's an example of how you could use it for a sparkline:

=SPARKLINE(INDIRECT("'[Fin-purch.xlsm]Sheet1'! G" & D50 & ":G" & D51))

In this example, D50 might contain the starting row number, and D51 the ending row number, for the data range for the sparkline on 'Sheet1'. The INDIRECT function will turn the string into a range reference that the SPARKLINE function can use.

Please note:

• INDIRECT can only reference open workbooks. If 'Fin-purch.xlsm' is not open, the function will return a reference error.
• INDIRECT is a volatile function and can slow down your workbook if used extensively.
• This syntax assumes you are using a version of Excel that supports dynamic arrays or you are entering it as an array formula.

I hope this helps.

Best Regards, Ibhadighi

Hello PianSa,

Thank you for providing additional details. It looks like you're trying to reference a dynamic range for a sparkline graph located in an external workbook (Fin-purch.xlsm) and you want to update this reference in another Excel file based on varying data inputs.

To achieve this dynamic updating of sparkline graphs, referencing the correct data range from an external file, you would typically use a combination of formulas to construct the data range as a text string and then a method to translate that string into an actual range reference.

However, as mentioned before, Excel’s INDIRECT function does not work with closed workbooks, and sparklines do not accept dynamic references provided by formulas; they need actual cell ranges.

Given these limitations, here's a solution to update sparkline graphs dynamically, assuming that you have to deal with varying symbols and data points:

1. Dynamic Range Text in Helper Cell:
   • In the workbook where you want the sparkline to update, set up a cell (let’s say H1) where you construct your reference string dynamically based on inputs. For example:

="'C:\Users\Intel\Documents\Stocks files[Fin-purch.xlsm]Sheet1'! G"&D50

• In this example, D50 would hold the row number that changes based on your data input.

2. **Named Range Using OFFSET:**
   • If the size of the sparkline data is constant (e.g., always 10 cells), you could create a Named Range in your workbook using the OFFSET and INDIRECT functions to refer to your dynamic path. It's important to note that this would only work if the external workbook is open.
   • This Named Range could look something like this (assuming you are grabbing 10 cells for the sparkline):

=OFFSET(INDIRECT(H1),0,0,1,10)

• You would replace H1 with the cell where your dynamic path is stored.

3. Create Sparkline Referencing Named Range:
   • When setting up or updating the sparkline, use the Named Range you created as the data source. This way, when you update the reference in H1, the Named Range updates, and so does the sparkline.
4. VBA for Complete Automation:
   • If the external workbook is closed or if you want a fully automated solution, you could write a VBA macro that updates the source data for the sparkline by evaluating the dynamic reference string and adjusting the sparkline accordingly.

Here's an example of how a VBA macro could look like for updating a sparkline:

Sub UpdateSparkline() Dim sparklineRange As Range Dim dynamicPath As String Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("YourSheetName") ' Change to your actual sheet name

dynamicPath = ws. Range("H1"). Value ' H1 is where your dynamic path string is Set sparklineRange = Range(dynamicPath) ' This assumes that the workbook is open

With ws . SparklineGroups(1). Location = . Range("G58") ' G58 is where your sparkline is . SparklineGroups(1). SourceData = sparklineRange.Address End With End Sub

Remember that VBA can execute tasks that standard Excel functions can't, such as evaluating strings as references and updating sparklines based on conditions. If you're not familiar with VBA, you might need to seek assistance from someone who is, or learn the basics of VBA programming.

Thank you, Ibhadighi

So I bet it's not possible from what I clarified as my needs, huh?

I attach a graph.

I'm sorry I failed to provide a clearer explanation.

The original sparkline graph which has a range of data is in another file which is as mentioned: Fin-purch.xlsm and the graph is located in cell G58.

In another file would exist a symbol, (which would vary) and a copy of the sparkline graph.

From what I learned here long before, I would type over the copied sparkline graph and refer it to the original location  so that the copied sparkline graph would be updated automatically.

But then, since the symbol and location would vary depending on the data inputted, the location would likewise vary as per syntax formula.