Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with unwanted R1C1 References
In Excel i have done a group change with a simple formula (a1*b1 in cell c1)
and it comes up with the reference +RC[-1]*RC[-2] in all active sheets apart from the one i am using. I have tried to stop it, the help file only advises me to turn off R1C1 in options - it already is! Please help as i have to do this many times to over 40 worksheets! Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with unwanted R1C1 References
It's interesting to see the inners of excel, huh?
When you enter a formula, excel "sees/parses" it as R1C1 reference style--but it displays the formula the way you want according to that setting. Your problem is that in the other sheets, C1 is formatted as Text. Change the format to General (or Number or whatever you want except Text) and you'll be ok. Ps. In excel, most people start the formula with an equal sign (not + like in Lotus 123). Tony Black wrote: In Excel i have done a group change with a simple formula (a1*b1 in cell c1) and it comes up with the reference +RC[-1]*RC[-2] in all active sheets apart from the one i am using. I have tried to stop it, the help file only advises me to turn off R1C1 in options - it already is! Please help as i have to do this many times to over 40 worksheets! Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with unwanted R1C1 References
Hi Tony I don't if this can help I am not sure, but if you want to change the string "R3C4" to the string "D3", then here is one way to do it. Sub Example() '// Convert string R3C4 to string D3 Dim Row, Col, NewAddress, sStr sStr = "R3C4" With Application Row = Val(Mid(sStr, 2)) Col = Val(Mid(sStr, .Search("C", sStr) + 1)) End With NewAddress = Cells(Row, Col).Address _ (RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1) End Sub -- jetted ------------------------------------------------------------------------ jetted's Profile: http://www.excelforum.com/member.php...o&userid=17532 View this thread: http://www.excelforum.com/showthread...hreadid=564339 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-increment data source cell references when copying charts | Excel Discussion (Misc queries) | |||
How to auto-increment data source cell references when copying cha | Charts and Charting in Excel | |||
Changing cell references in formulas to names and back again. | Excel Discussion (Misc queries) | |||
Cell Reference's when Pasting | Excel Discussion (Misc queries) | |||
Automatically Changing Cell Reference's when Pasting in Excel | Excel Discussion (Misc queries) |