cell references-Please Help! Please Help!

[James Lucero]

In a cell, I use the concatenate function to join the row and column of
another particular cell.

How can I now use that resulting cell reference in let's say another formula

e.g. A1 has the value $B2 and A2 has the value $B$3
cell F3 has the following =concatenate(A1,":"A2)

if Cell F3 is clicked it now shows the value $B$2:$B$3 in the cell BUT the
formula bar shows F3

Thereby if I used F3 in another formula, Excel would give error message #VALUE

Please Help

 

[Tom Hutchins]

Use the INDIRECT function with the range address you created through
concatenation:

=SUM(INDIRECT(F3))
=AVERAGE(INDIRECT(F3))

Hope this helps,

Hutch

 

[Per Jessen]

Hi

Use

INDIRECT(F3)

in your formula to refer to the cell reference in F3.

Regards,
Per

 

[James Lucero]

Thank you very much Mr Hutchins. Your solution worked for the scenario
provided, however that was not quite the scenario I wanted to solve. I
appologize but I thought asking for use in a formula would give me the
example that I needed. What I actually want to do is use the range address
as a series value in an excel chart. I would really like your assistance.

Thank you very Much

 

[Tom Hutchins]

Are you trying to create a way to easily change the data range for a chart? I
don't think that will work as you intend. I can create a chart by putting
=INDIRECT(F3) as the data source, but the chart doesn't store the data source
range that way - it stores the actual range of cells referred to in F3. If I
record a macro while creating the chart this way, the VBA code has the actual
cell range, not =INDIRECT(F3).
Maybe one of the Excel MVPs knows how to make it work, and we can both
learn. I don't do much charting.

You could create a chart using a (mostly recorded) macro and have it use the
range specified in A1 and A2. Here is a simple example:

Sub MakeChart()
Dim Rng1 As String, Rng2 As String
'Get the cell addresses stored in A1 and A2
Rng1 = Sheets("Sheet1").Range("A1").Value
Rng2 = Sheets("Sheet1").Range("A2").Value
'Create a chart
Charts.Add
ActiveChart.ChartType = xlColumnClustered
'Use Rng1:Rng2 as the data source
ActiveChart.SetSourceData _
Source:=Sheets("Sheet1").Range(Rng1 & ":" & Rng2), _
PlotBy:=xlRows
ActiveChart.Location _
Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
End Sub

If you delete the chart, change the cell addreses in A1 and/or A2, and
re-run the macro, a new chart will be created which reflects the changes. I
don't know what kind of chart you want to build, but you may be able to
record yourself creating the chart, then edit the recorded VBA code to use
the range specified in A1 and A2. Basically, you would include every line
from the example above which refers to Rng1 or Rng2. Replace the recorded
ActiveChart.SetSourceData statement with the one from the example above.

I am always glad to help if you have problems. If you are new to macros,
this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

BTW, Jon Peltier is the man when it comes to Excel charts. His site is full
of great tips, examples, and instructions.

Hope this helps,

Hutch