Relative references
 

B1 contains an "old" value; C1 contains its "new" value
B47 contains an "old" value; C2 contains its "new" value
B93 contains an "old" value; C3 contains its "new" value
B139 contains an "old" value; C4 contains its "new" value

How do write a formula to replace the respective "old" values with
their "new" values?

Relative references
 

Try this:

=INDIRECT("B"&1+46*(ROW()-1))

HTH,
Elkar


"GARY" wrote:

B1 contains an "old" value; C1 contains its "new" value
B47 contains an "old" value; C2 contains its "new" value
B93 contains an "old" value; C3 contains its "new" value
B139 contains an "old" value; C4 contains its "new" value

How do write a formula to replace the respective "old" values with
their "new" values?

Relative references
 

Hi Elkar,

Your formula doesn't refer to Col C.

gary

Relative references
 

Nope, it should be entered in Column C, then copied down.

"GARY" wrote:

Hi Elkar,

Your formula doesn't refer to Col C.

gary

Relative references
 

in VBA try this:

Sub test()
Dim r
For r = 0 To 9
Cells(r * 46 + 1, 2) = Cells(r + 1, 3)
Next
End Sub

Relative references
 

I thought it be best to provide an example of my spreadsheet.
(I've changed the rows and columns to fit the posting space)

I want to replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

Relative references
 

Ok, sorry, I misunderstood what you were looking for. Try this (based on the
example provided):

=LEFT(C1,FIND(" ",C1,1)-1)&" "&INDIRECT("B"&1+((ROW()-1)/9))&"
"&RIGHT(C1,LEN(C1)-FIND(" ",SUBSTITUTE(C1," ","",1),1)-1)

HTH,
Elkar


"GARY" wrote:

I thought it be best to provide an example of my spreadsheet.
(I've changed the rows and columns to fit the posting space)

I want to replace:

the middle word in C1 with the word in B1
the middle word in C10 with the word in B2
the middle word in C19 with the word in B3
the middle word in C28 with the word in B4
the middle word in C37 with the word in B5

(Column D contains the results)


B C D
1 pig cat dog horse cat pig horse
2 cow
3 pansy
4 rat
5 mouse
6
7
8
9
10 duck fish flea duck cow flea
11
12
13
14
15
16
17
18
19 mutt ant bee mutt pansy bee
20
21
22
23
24
25
26
27
28 hog puppy bug hog rat bug
29
30
31
32
33
34
35
36
37 mule germ fly mule mouse fly

Relative references
 

nice job Elkar :-)