[j.ruderman[_2_]]

I have a workbook containing 14 sheets all structured the same. Cells E8:E39
may contain the text "PS Rep". When that text appears I need to extract the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[Shane Devenshire[_2_]]

So what do you do if the text PS Rep appears on two sheets or more than once
in one sheet?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"j.ruderman" wrote:

I have a workbook containing 14 sheets all structured the same. Cells E8:E39
may contain the text "PS Rep". When that text appears I need to extract the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[Shane Devenshire[_2_]]

Hi,

If we assume that the entry can only occur once on any of the sheets you can
use something of the following form in 2007:

=IFERROR(VLOOKUP("PS Rep",Sheet2!E8:F38,2,FALSE),"")&IFERROR(VLOOKUP("P S
Rep",Sheet3!E8:F38,2,FALSE),"")

VLOOKUP doesn't work across 3D so you will need to concatenate a formula for
each sheet. In 2003 you could use:

=IF(ISNA(VLOOKUP("PS Rep",Sheet2!E8:F38,2,FALSE)),"",VLOOKUP("PS
Rep",Sheet2!E8:F38,2,FALSE))&IF(ISNA(VLOOKUP("PS
Rep",Sheet3!E8:F38,2,FALSE)),"",VLOOKUP("PS Rep",Sheet3!E8:F38,2,FALSE))

You could also write a custom VBA function.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"j.ruderman" wrote:

I have a workbook containing 14 sheets all structured the same. Cells E8:E39
may contain the text "PS Rep". When that text appears I need to extract the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[T. Valko]

Do your sheet names follow some kind of sequential naming pattern? Like
this:

Week1, Week2, Week3

June 2009, July 2009, August 2009

--
Biff
Microsoft Excel MVP


"j.ruderman" wrote in message
...
I have a workbook containing 14 sheets all structured the same. Cells
E8:E39
may contain the text "PS Rep". When that text appears I need to extract
the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[j.ruderman[_2_]]

That is where i'm running into problems. The text may, or may not, appear
mulitple times per sheet and in mulitple sheets.

"Shane Devenshire" wrote:

So what do you do if the text PS Rep appears on two sheets or more than once
in one sheet?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"j.ruderman" wrote:

I have a workbook containing 14 sheets all structured the same. Cells E8:E39
may contain the text "PS Rep". When that text appears I need to extract the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[j.ruderman[_2_]]

Somewhat I suppose...Su, Mo, and so on thru Sa, then the following 7 Sheets
are Su2, Mo2 and so on thru Sa2.

"T. Valko" wrote:

Do your sheet names follow some kind of sequential naming pattern? Like
this:

Week1, Week2, Week3

June 2009, July 2009, August 2009

--
Biff
Microsoft Excel MVP


"j.ruderman" wrote in message
...
I have a workbook containing 14 sheets all structured the same. Cells
E8:E39
may contain the text "PS Rep". When that text appears I need to extract
the
text from the adjacent Column F8:F39. I'm tring to do this in a seperate
sheet, for the entire workbook, in one column without spaces.

Thanks

[T. Valko]

I just read your other reply where you say there may be multiple matches and
they could be on more than one sheet.

I can't think of an "elegant" way to do what you want. You may have to do a
separate lookup/extraction for each sheet.

--
Biff
Microsoft Excel MVP


"j.ruderman" wrote in message
...
Somewhat I suppose...Su, Mo, and so on thru Sa, then the following 7
Sheets
are Su2, Mo2 and so on thru Sa2.

"T. Valko" wrote:

Do your sheet names follow some kind of sequential naming pattern? Like
this:

Week1, Week2, Week3

June 2009, July 2009, August 2009

--
Biff
Microsoft Excel MVP


"j.ruderman" wrote in message
...
I have a workbook containing 14 sheets all structured the same. Cells
E8:E39
may contain the text "PS Rep". When that text appears I need to extract
the
text from the adjacent Column F8:F39. I'm tring to do this in a
seperate
sheet, for the entire workbook, in one column without spaces.

Thanks