Creating a Dynamic Graph Using Text Cell References

**derekjwilson** · 04-01-2013, 10:55 AM

Good morning,

I am looking to create a graph based on a range between two cells.

The user defines the row of these cells, and then indicates the desired start and end range (ie. start column and end column) in a specific area.

Using this information, I have produced using the ADDRESS function two text cell references, which are located on sheet "References".

Results:

'Suburban (By District)'!$K$2 (Located in K4)
'Suburban (By District)'!$U$2 (Located in L4)

The sheet, row, and column are variable and determined by the user, so these locations will change depending on the user input. I want the graph to actively reflect these changes.

In any case, my assumption was that I could make the range = 'References'!$K$4:'References'!$L$4'. This does not work. I imagine I should use the INDIRECT function, but that returns the values of the aforementioned locations, not the locations themselves.

How do I create this range based on the text in K4 and L4?

Thanks for any help.

Regards,

DJW

**AndyLitch** · 04-01-2013, 11:13 AM

If I understand you correctly you'll be needing Offset and dynamic Named Ranges which can autosize to fit the dataset

**Andy Pope** · 04-01-2013, 11:16 AM

Named range xCHT_DATA
=INDEX(INDIRECT(References!$K$4),1,1):INDEX(INDIRECT(References!$L$4),1,1)

You will need to add additional single quote to start of cell otherwise it will be ignored.

K4: ''Suburban (By District)'!$K$2
L4: ''Suburban (By District)'!$U$2

**derekjwilson** · 04-01-2013, 11:48 AM

Hi Andy,

Thanks for the help. A quick question - how do I add the additional quotation mark? The text string is generated by a formula, so if I enter it, the cell turns the formula into a string.

The formula is:

=IF(VLOOKUP(Settings!$G$6,$B$2:$D$39,3, FALSE)="C","Suburban (By Class)","Suburban (By District)")

**Andy Pope** · 04-01-2013, 11:52 AM

Something like this

=IF(VLOOKUP(Settings!$G$6,$B$2:$D$39,3, FALSE)="C","''Suburban (By Class)","''Suburban (By District)")

Although your formula has no single quotes so I don't know where they are coming from in the first place.

**derekjwilson** · 04-01-2013, 12:45 PM

Ok - so this is what I am doing.

=IF(VLOOKUP(Settings!$G$6,$B$2:$D$39,3, FALSE)="C","'Suburban (By Class)","'Suburban (By District)")

[Result] >>> 'Suburban (By District)

=ADDRESS(I4,$I$21,,,$J$2) (J2 is the previous result)

'''Suburban (By District)'!$K$2

For some reason, when I add in the extra ' in the first formula, the second formula outputs three 's - one that it did already, and another from who knows where.

Essentially, 1 + 1 = 3.

No matter what I try, I always get ', or ''', or '''', but never '' - which is apparently what I need.

Thoughts?

**AndyLitch** · 04-01-2013, 12:50 PM

use ascii instead .... char(x)

**derekjwilson** · 04-01-2013, 01:04 PM

I'm having trouble understanding why it is so difficult to put together a range based on two cell locations produced by the address function.

Given that the ADDRESS function is specifically designed to return a cell reference, shouldn't it be usable to construct a range? And if not, how do I convert the text address into a cell reference?

**derekjwilson** · 04-01-2013, 01:06 PM

Or perhaps I can modify my function.

I have the following:

-Sheet
-Row
-Start Column
-End Column

How do I construct a range out of this, provided that the graph is on a sheet separate from the data?