Graph-input from cell in spreadsheet

Is this related to your prior question, getting column letter of last column of data?

https://www.experts-exchange.com/questions/28640554/Column-letter-of-the-last-column-that-has-a-value.html

Thanks
Rob H

You can use the Formula function to get the formula from the cell, then change it as you like, then set the new formula.

Something like this:

myOldFormula = Range("A2").Formula
myNewFormulaString = whatever calculations you want to do based on myOldFormula
Range("A2").Formula = myNewFormulaString

Select allOpen in new window

Mark Bullock - thanks - but could you please elaborate on your solution - like, could you demonstrate your solution in a spreadsheet? Thanks.

Are you able to build the full string for the Series Range in a cell, the use of the column Reference above will just be part of it.

If so you can then use the INDIRECT function to pull from the cell that contains the string. For example a normal formula =G2 will return the contents of G2. Whereas =INDIRECT(G2) will return the contents of the range referred to in G2. So if G2 contains the string, (manually typed or formula driven) "Sheet1!$A$4:$G$12", the formula =INDIRECT (G2) will return the contents of "Sheet1!$A$4". However a Named Range with =INDIRECT(G2) will be the same as making the Named Range Refers To: =Sheet1!$A$4:$G$12. You can then use the Named Range in the Graph.

However, as I said in the original question, you can use the OFFSET function to create a range, without the need to build a string and then use INDIRECT. You can use the OFFSET function directly in the Name Manager and then use the Named Range in the graph.

Thanks
Rob H

Rob Henson - Thanks for your insight.

What I got is a result that is formula driven; per now it looks something like this:

'2013'!$A$186;'2013'!$TN$87:$UK$87;'2013'!$TN$186:$UK$186;1

- and this I'd like to put into the graph-formula/function:

=SERIES(......)

This is what I'd like to acheive. Is it doable?

Lets say you have that text string in cell A5, you can then create a named range in the Name Manager:

Range Name:   Graph_Data
Refers to:  =INDIRECT(A5)

In the Graph, you can then set the Series data to:
=Graph_Data

In that Series I am assuming
A186 is Series Name
TN87:UK87 is headers/x-axis values
TN186:UK186 are Y-axis values
What is the ;1 on the end?

Graphs can sometimes get a bit quirky if your data range is not continuous, ie in the scenario above TN87:UK186, but that would give you lots of surplus series (rows 88:185).

Would another option be to create a separate table which extracts the data required so that it is in a continuous 2 row by 25 column table (TN to UK = 24, +1 for header column). The graph can then be fixed to read that data area.

Thanks
Rob H

Appologies for being such a newbie; but I still can not get it working.

In cell: Q120 i got the following string:

'2013'!$A$186;'2013'!$TN$87:$UK$87;'2013'!$TN$186:$UK$186;1

In Name Manager; Name: GG_NOK
Refers to: =INDIRECT(Dashbord!$Q$120)

Then, when I click on the graph; and replaces the graph function/formula as it is originally written; =SERIES('2013'!$A$186;'2013'!$TN$87:$UK$87;'2013'!$TN$186:$UK$186;1)

And replace it with:

=SERIES(GG_NOK)

- then I get an error message: "You've entered too few arguments for this function."

What am I doing wrong here? Please advice.

Thanks.