Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell references-Please Help! Please Help!
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell references-Please Help! Please Help!
Use the INDIRECT function with the range address you created through
concatenation: =SUM(INDIRECT(F3)) =AVERAGE(INDIRECT(F3)) Hope this helps, Hutch "James Lucero" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell references-Please Help! Please Help!
Hi
Use INDIRECT(F3) in your formula to refer to the cell reference in F3. Regards, Per "James Lucero" skrev i meddelelsen ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell references-Please Help! Please Help!
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" wrote: Use the INDIRECT function with the range address you created through concatenation: =SUM(INDIRECT(F3)) =AVERAGE(INDIRECT(F3)) Hope this helps, Hutch "James Lucero" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
cell references-Please Help! Please Help!
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 _ Whe=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/200...e-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 "James Lucero" wrote: 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" wrote: Use the INDIRECT function with the range address you created through concatenation: =SUM(INDIRECT(F3)) =AVERAGE(INDIRECT(F3)) Hope this helps, Hutch "James Lucero" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Displaying multiple cell references in 1 cell | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Changing Cell References To a Different Worksheet in the Same Cell | Excel Worksheet Functions | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |