Graph-input from cell in spreadsheet

Hi all,

- is it possible to have cells in a spreadsheet that delivers input to a graph?  

Lets assume that you have graph that holds the following:
=SERIES('2013'!$A$186;'2013'!$TN$87:$UK$87;'2013'!$TN$186:$UK$186;1)

And part of this 'series' is to take input from a cell in the spreadsheet (in which the graph recides); assume further that a cell gives the following 'contribution' to the graph: $UK$ - and it is derived from a formula.

The formula which the cell that gives the input to the graph is the following;

(LEFT(RIGHT((CELL("address";INDEX('2013'!E91:AGA91;MATCH(9,99999999999999E+307;'2013'!E91:AGA91))));6);4))

In this instance it returns:  $UK$

How do I incorporate the formula into the graph-series? Is it doable?

Thanks.
UtredningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Is this related to your prior question, getting column letter of last column of data?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28640554.html

Thanks
Rob H
0
Mark BullockQA Engineer IIICommented:
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

Open in new window

0
UtredningAuthor Commented:
Mark Bullock - thanks - but could you please elaborate on your solution - like, could you demonstrate your solution in a spreadsheet? Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
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
0
UtredningAuthor Commented:
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?
0
Rob HensonFinance AnalystCommented:
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
0
UtredningAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
Right click on graph and select Edit Data Source.

Input box at the top, use =GG_NOK
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.