Index and Match?
 

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

Index and Match?
 

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

Index and Match?
 

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

Index and Match?
 

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

Index and Match?
 

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

Index and Match?
 

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

Index and Match?
 

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