Relative references

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gcotterl - 16 Jun 2006 21:27 GMT

In the example below, how do I write a formula that will 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 desired 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

Reply to this Message

Bernard Liengme - 17 Jun 2006 00:29 GMT

Not sure what "relative reference" has to do with this problem that looks
like a homework assignment.
Try this in C1 and then copy it to the other cells
=LEFT(C1,FIND(" ",C1))&INDIRECT("B"&INT(ROW()/9)+1)&"
"&RIGHT(C1,LEN(C1)-FIND("*",SUBSTITUTE(C1," ","*",2)))
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> In the example below, how do I write a formula that will replace:
>
[quoted text clipped - 44 lines]
> 36
> 37 mule germ fly mule mouse fly

Reply to this Message

gcotterl - 17 Jun 2006 04:30 GMT

Hi Bernard,

Re: "relative reference"

I was looking for an expression that would "refer":

B1 to C1
B2 to C10
B3 to C19
B4 to C28
B5 to C37

Gary

> Not sure what "relative reference" has to do with this problem that looks
> like a homework assignment.
[quoted text clipped - 50 lines]
> > 36
> > 37 mule germ fly mule mouse fly

Reply to this Message

Ken Johnson - 17 Jun 2006 05:23 GMT

> Hi Bernard,
>
[quoted text clipped - 9 lines]
>
> Gary

Hi Gary,

I think Bernard meant to say "Try this in D1 etc" not "Try this in C1
etc"

Ken Johnson

Reply to this Message

Ken Johnson - 17 Jun 2006 01:01 GMT

> In the example below, how do I write a formula that will replace:
>
[quoted text clipped - 44 lines]
> 36
> 37 mule germ fly mule mouse fly

Hi,

Just an small improvement so that errors are not seen...

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

Ken Johnson

Reply to this Message