Help with IF THEN formula across multiple sheets

**lindesley** · 04-12-2012, 04:49 PM

I have data in a column in my data sheet that I want to reference on several other sheets in the same workbook ONLY if it displays a certain character.
For example -
data sheet 1 cells are:
Smith (H)
Bob (Z)
Doe (R)

Sheet 2 needs to only display the cell if it has (H); sheet 3 only needs to display the cell if it has (Z); sheet 4 only needs to display if cell has (R).
I have excel 2010.
How the HECK do I write this formula? I know there's got to be a way to do it, I just have no idea how! I'm incredibly frustrated! Help!
Thanks.

**Søren Larsen** · 04-12-2012, 05:28 PM

Are you looking for something like this:

=IF(RIGHT(A1;3)="(H)",A1;"") in Sheet 2; assuming your data in Sheet 1 starts in A1.

Then just change the letter in the =IF() statement to fit the other sheets.

Or have misunderstood something?

**lindesley** · 04-12-2012, 06:26 PM

The formula didn't work, unfortunately.
It gave me a generic error message, and didn't refer back to the cell in sheet 1. Another suggestion?

**FDibbins** · 04-12-2012, 06:55 PM

it may be a punctuation error caused by different country formats? replace the ; with , and try again.

also, are you looking for (H), H, Smith (H) or Smith?

assuming your data is on sheet 1 A1:A3, and you want the answer on sheet2m A1, use...

=IF(ISERROR(FIND("(H)",Sheet1!A1,1)),"",Sheet1!A1)
adjust references as needed for other sheets.

you could reference (H) instead if hard-coding it if you want, that will give you more flexibility in your search, in whic case with (H) in B1 on sheet2, the formula would change to...

=IF(ISERROR(FIND(B1,Sheet1!A1,1)),"",Sheet1!A1)