[Mike]

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Smith W T DO DO W
PT 60 PT 61/1
Doe T PT 61/1 PT 60 PT 61/1 DO DO
W
Jones PT 60 PT 61/1 W T PT 60 PT
61/1 W

PT 60 XXXX XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX XXXX

[macropod[_2_]]

Hi Mike,

It's not possible to test cells containing text with field coding and cell references. You would need to bookmark each string (not
the whole cell) for the test. Even then, the ability to test the contents of each bookmark is severely limited and there really is
no practical way (even if there is a feasible way) of doing what you want via field coding. What you want to do is better done via a
macro or in Excel (you could embed an Excel worksheet in your document) via, say, an Index/Match combination.

--
Cheers
macropod
[Microsoft MVP - Word]


"Mike" wrote in message ...
I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Smith W T DO DO W
PT 60 PT 61/1
Doe T PT 61/1 PT 60 PT 61/1 DO DO
W
Jones PT 60 PT 61/1 W T PT 60 PT
61/1 W

PT 60 XXXX XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX XXXX

[macropod[_2_]]

Hi Mike,

It's not possible to test cells containing text with field coding and cell references. You would need to bookmark each string (not
the whole cell) for the test. Even then, the ability to test the contents of each bookmark is severely limited and there really is
no practical way (even if there is a feasible way) of doing what you want via field coding. What you want to do is better done via a
macro or in Excel (you could embed an Excel worksheet in your document) via, say, an Index/Match combination.

--
Cheers
macropod
[Microsoft MVP - Word]


"Mike" wrote in message ...
I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Smith W T DO DO W
PT 60 PT 61/1
Doe T PT 61/1 PT 60 PT 61/1 DO DO
W
Jones PT 60 PT 61/1 W T PT 60 PT
61/1 W

PT 60 XXXX XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX XXXX

[Mike]

Thanks,
I'll post it in Excel discussion group.
Mike

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Smith W T DO DO W
PT 60 PT 61/1
Doe T PT 61/1 PT 60 PT 61/1 DO DO
W
Jones PT 60 PT 61/1 W T PT 60 PT
61/1 W

PT 60 XXXX XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX XXXX

[Mike]

Thanks,
I'll post it in Excel discussion group.
Mike

"Mike" wrote:

I am trying to find a formula to search for text in a range of cells that if
found it would return data in another cell. For instance, Retun Jones in the
Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and
Jones in the SA 61/1 XXXX cell on Monday.

Name Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Smith W T DO DO W
PT 60 PT 61/1
Doe T PT 61/1 PT 60 PT 61/1 DO DO
W
Jones PT 60 PT 61/1 W T PT 60 PT
61/1 W

PT 60 XXXX XXXX XXXX XXXX XXXX XXXX XXXX
PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX XXXX