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
|