|  | Changing Cell Reference in Formula | | |
| | | JimS |  |
|  Posted: Sat Aug 02, 2008 5:07 pm Post subject: Changing Cell Reference in Formula |  |
Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that? |
| |
| | | Dave Peterson | |
| | Posted: Sat Aug 02, 2008 5:52 pm Post subject: Re: Changing Cell Reference in Formula | |
Select at least two cells (click on this cell, and ctrl-click on an empty cell)
Then use a couple of Edit|Replace's.
JimS wrote:
| Quote: |
Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that?
|
--
Dave Peterson |
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Sat Aug 02, 2008 10:28 pm Post subject: Re: Changing Cell Reference in Formula | |
| |  | |
If, as I suspect, the only non-digits that would appear in A2 and B2 are the
fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
use this much shorter formula to do what your posted formula does...
=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2))-187)/4)
Of course this suffers from repeated use of the A2 and B2 references. The
following formula, while longer than the above formula but still much
shorter than yours, has the benefit that the A2 and B2 references are used
only once each...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR(190),".75")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189),".5"),CHAR(190),".75")
Rick
"JimS" <jimx22@msn.com> wrote in message
news:ovb994h0nv4rehsgm8ne176edo88o40pan@4ax.com...
| Quote: | Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that? |
|
| |
| | | JimS | |
| | Posted: Sun Aug 03, 2008 6:54 am Post subject: Re: Changing Cell Reference in Formula | |
| | | |
This is beautiful (the second one particularly).
This also clears up a VALUE error I was getting if my selected number
was less than 1, such as 1/2, 3/4, etc.
Very nice. Thanks so much.
On Sat, 2 Aug 2008 20:28:49 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
| Quote: | If, as I suspect, the only non-digits that would appear in A2 and B2 are the
fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
use this much shorter formula to do what your posted formula does...
=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2))-187)/4)
Of course this suffers from repeated use of the A2 and B2 references. The
following formula, while longer than the above formula but still much
shorter than yours, has the benefit that the A2 and B2 references are used
only once each...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR(190),".75")
-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189),".5"),CHAR(190),".75")
Rick
"JimS" <jimx22@msn.com> wrote in message
news:ovb994h0nv4rehsgm8ne176edo88o40pan@4ax.com...
Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that? |
|
| |
| | | Rick Rothstein (MVP - VB) | |
| | Posted: Sun Aug 03, 2008 7:16 am Post subject: Re: Changing Cell Reference in Formula | |
| | | |
You are quite welcome... I am glad you found the formulas useful. In case
you were wondering why I worded my response to you in the way I did, I only
just a couple of minutes ago saw your earlier thread where the formula you
posted in this thread was derived at. Once I worked out what you were
attempting to do with that formula, I then decided it looked too long. My
first solution was actually the SUBSTITUTE function one, but that looked too
long to me also, so I kept at it until I came up with the IF function
version. Being that I wasn't 100% sure what you were asking for in this
thread, I decided to post both formulas for your consideration.
Rick
"JimS" <jimx22@msn.com> wrote in message
news:lisa94tk4g9hkgscpac1o9mgjph2b9fhia@4ax.com...
| Quote: | This is beautiful (the second one particularly).
This also clears up a VALUE error I was getting if my selected number
was less than 1, such as 1/2, 3/4, etc.
Very nice. Thanks so much.
On Sat, 2 Aug 2008 20:28:49 -0400, "Rick Rothstein \(MVP - VB\)"
rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
If, as I suspect, the only non-digits that would appear in A2 and B2 are
the
fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
use this much shorter formula to do what your posted formula does...
=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2))-187)/4)
Of course this suffers from repeated use of the A2 and B2 references. The
following formula, while longer than the above formula but still much
shorter than yours, has the benefit that the A2 and B2 references are used
only once each...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR(190),".75")
-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189),".5"),CHAR(190),".75")
Rick
"JimS" <jimx22@msn.com> wrote in message
news:ovb994h0nv4rehsgm8ne176edo88o40pan@4ax.com...
Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that?
|
|
| |
| | | Pete_UK | |
| | Posted: Mon Aug 04, 2008 7:25 am Post subject: Re: Changing Cell Reference in Formula | |
Hi Jim,
another approach would be to put the original formula in C2, looking
at A2 and B2, in a new worksheet. Then <cut>/paste the formula into
the cell where you want it to appear in your final sheet. Then cut/
paste cell A2 to K16, and cut/paste cell B2 to K15, and the formula
will have adjusted itself - you can now copy the formula into your
destination sheet.
Hope this helps.
Pete
On Aug 2, 8:07 pm, JimS <jim...@msn.com> wrote:
| Quote: | Excel 2007
I've got this big long formula:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),1*LEFT(A2,LEN(A2)-1)
+(CODE(RIGHT(A2,1))-187)/4,A2)-IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),1*LEFT(B2,LEN(B2)-1)
+(CODE(RIGHT(B2,1))-187)/4,B2)
What I want to do is go through and change all of the A2 to k16, and
change all of the b2 to k15.
What is the easiest way to do that? |
|
| |
|
|
