If Formulas

[uum6212]

Hello everyone, I'm new to this website and need some assistance. My "IF" formulas are too long and won't work due to nesting? This is the formula I have came up with. =IF(ISNUMBER(SEARCH("12",SHEET1!A1)),"730A-8P",IF(ISNUMBER(SEARCH("8",SHEET1!A1)),"",))) but if I don't have a 12 or an 8 I just want the cell sheet2 a1 to match the text sheet1!a1. Thank you for you help.

 

[Skybluekid]

Welcome to the board

Try

=IF(ISNUMBER(SEARCH("12",Sheet1!A1)),"730A-8P",IF(ISNUMBER(SEARCH("8",Sheet1!A1)),"",Sheet2!A1)))

 

[Snakehips]

UUM6212,

Try....
Formula in Sheet2 A1

Excel 2007

	A
1	NG-333

<COLGROUP><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula A1 =IF(ISNUMBER(SEARCH("12",Sheet1!A1)),"730A-8P",IF(ISNUMBER(SEARCH("8",Sheet1!A1)),"",Sheet1!A1)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[uum6212]

Thanks for the ideas but already tried these things and they didn't work. I appreciate the help though. Let me see if I can explain it better.

Sheet 1 Cell A1: the following numbers/text can be in this cell. 12, 8, 7a-330p, 730a-330p, 730a-8p, 12p-830p, 1130a-8p, 1130a-12a, 330p-12a, 745p-745a, 1145a-745a, D, AL.

I want Sheet 2 Cell A1 to reflect the following.
=IF(ISNUMBER(SEARCH("12,SHEET1!A1)),"730A-8P",IF(ISNUMBER(SEARCH("8",SHEET1!A1)),"")) And if 12 nor 8 is typed in sheet 1 cell A1, I want sheet2 cell a1 to reflect sheet1 cell a1

 

[Snakehips]

uum6212,

Forgive me but does the previous post not do exactly that?

Do you have/need data validation in Sheet1 A1 so that input values are limited to your list as above?

 

[uum6212]

=IF(ISNUMBER(SEARCH("12",Sheet1!C4)),"730A-8P",IF(ISNUMBER(SEARCH("8",Sheet1!C4)),"","Sheet1!C4")) This formula is in sheet2 C4... If I type anything else besides 12 or 8 in sheet1!C4 it comes up blank on sheet2 c4.

 

[Snakehips]

UUM6212,

Here are screenshots from sheets 1 & 2 with data / formula in rows 1 to 4 showing various results....

Excel 2007

	B	C
1	Data	ABC12
2		W12-8
3		A-8-BC
4		OTHER-1

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Note that your original IF formula will ignore 8 if 12 is also in the data string.



Excel 2007

	B	C
1	Result	730A-8P
2		730A-8P
3
4		OTHER-1

<COLGROUP><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula C1 =IF(ISNUMBER(SEARCH("12",Sheet1!C1)),"730A-8P",IF(ISNUMBER(SEARCH("8",Sheet1!C1)),"",Sheet1!C1)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Please explain further if this is not what you require as a result.

 

[T. Valko]

Thanks for the ideas but already tried these things and they didn't work. I appreciate the help though. Let me see if I can explain it better.

Sheet 1 Cell A1: the following numbers/text can be in this cell. 12, 8, 7a-330p, 730a-330p, 730a-8p, 12p-830p, 1130a-8p, 1130a-12a, 330p-12a, 745p-745a, 1145a-745a, D, AL.

I want Sheet 2 Cell A1 to reflect the following.
=IF(ISNUMBER(SEARCH("12,SHEET1!A1)),"730A-8P",IF(ISNUMBER(SEARCH("8",SHEET1!A1)),"")) And if 12 nor 8 is typed in sheet 1 cell A1, I want sheet2 cell a1 to reflect sheet1 cell a1

The problem that I see is if you're searching for 12 then there are multiple possibilities that match:

12
12p-830p
1130a-12a
330p-12a

Same thing if you're searching for 8:

8
730a-8p
12p-830p
1130a-8p

That's how SEARCH works. If the string to search for is ANYWHERE in the cell then it matches.

Is that what you want?

 

[uum6212]

Snake Hips, or T. Valko: This is an example of what I would like to happen.

Excel 2007 - sheet 1

	B	C
1	Entered	12
2		730A-8P
3		8
4		D

<TBODY>
</TBODY>

When sheet 1 is entered like above, Sheet 2 should look like this below.


Excel 2007 Sheet 2

	B	C
1	Result	730A-8P
2		730A-8P
3		730A-4P
4		D

<TBODY>
</TBODY>

 

[Snakehips]

uum6212,

So, just to be sure......

If A1 = 12 then 730A-8P
If A1 = 8 then 730A-4P
If A1 = anything else then = A1