if(formula)

[MicheleP]

Hi,
I've tried creating a formula that will return the value "14" if true or "4"
if false but i can't get in to work.

*=IF([B23177Takeoff.xls]H23177!$A$1:$A$3=A1,"14","4"). I'm compare two
separate spread sheets and the formula is in Book1 C1, C2, etc. When in
enter C1 the it returns the correct infromation, but once I enter in C2 it's
incorrect.

Book1 B23177Takeoff
A B C A B C
1 200 14 1 200
2 201 4 2 202
3 203 4 3 205

Any help would be much appreciated. MicheleP

 

[Pete_UK]

Try it like this:

=IF(ISNA(MATCH(A1,[B23177Takeoff.xls]H23177!$A$1:$A$3,0)),4,14)

This implies that the takeoff file is open at the same time. I've also
removed the " from around the numbers as that will change them to text
values.

Hope this helps.

Pete

 

[ShaneDevenshire]

Hi,

A couple of things -
1 .There is nothing in your formula that uses any cells in column C, so it's
not clear why entering anything in C2 or C1 would have any effect on the
formula?
2. In this part of the formula you are comparing 1 cell with 3 cells and IF
does not allow this unless you enter it as an array.
B23177Takeoff.xls]H23177!$A$1:$A$3=A1
3. What exactly are you trying to accomplish with this formula? What if A1
and A2 of one sheet = A1 of the other sheet, should the result be different
from what happens if A1 only equals A3?
4. This may be intentional but by putting quotes around the "14" and "4" you
are making them text, not numbers, is that what you want to do?

 

[MicheleP]

I want to detirmine the location(Mexico/14 or Phoenix/4) of the work for
individual piece #'s(ex. 1-100)... Each workbook has a list of these Piece#
1-100 listed by rows.

Example Book1 Cell A1 and Book 2 Cell A2 both = 200 so the formalu needs to
return the number 14 or text 14 into Book1 B1, B2, etc... In my formula I'm
trying to say that is Book2 cells $A$1:$A$100 equal Book1 Cell A1, Cell A2,
Cell A3 etc.
I hope this make sense.

Book1 Book2
A B A
Piece# Location Piece#
1 200 14 1 199
2 201 4 2 200
3 203 4 3 205

Hi,

A couple of things -
1 .There is nothing in your formula that uses any cells in column C, so it's
not clear why entering anything in C2 or C1 would have any effect on the
formula?
2. In this part of the formula you are comparing 1 cell with 3 cells and IF
does not allow this unless you enter it as an array.
B23177Takeoff.xls]H23177!$A$1:$A$3=A1
3. What exactly are you trying to accomplish with this formula? What if A1
and A2 of one sheet = A1 of the other sheet, should the result be different
from what happens if A1 only equals A3?
4. This may be intentional but by putting quotes around the "14" and "4" you
are making them text, not numbers, is that what you want to do?

--
Thanks,
Shane Devenshire

Hi,
I've tried creating a formula that will return the value "14" if true or "4"
if false but i can't get in to work.

*=IF([B23177Takeoff.xls]H23177!$A$1:$A$3=A1,"14","4"). I'm compare two
separate spread sheets and the formula is in Book1 C1, C2, etc. When in
enter C1 the it returns the correct infromation, but once I enter in C2 it's
incorrect.

Book1 B23177Takeoff
A B C A Piece# Location Piece#
1 200 14 1 200
2 201 4 2 202
3 203 4 3 205

Any help would be much appreciated. MicheleP

 

[MicheleP]

Thanks it worked.

Try it like this:

=IF(ISNA(MATCH(A1,[B23177Takeoff.xls]H23177!$A$1:$A$3,0)),4,14)

This implies that the takeoff file is open at the same time. I've also
removed the " from around the numbers as that will change them to text
values.

Hope this helps.

Pete

Hi,
I've tried creating a formula that will return the value "14" if true or "4"
if false but i can't get in to work.

*=IF([B23177Takeoff.xls]H23177!$A$1:$A$3=A1,"14","4"). I'm compare two
separate spread sheets and the formula is in Book1 C1, C2, etc. When in
enter C1 the it returns the correct infromation, but once I enter in C2 it's
incorrect.

Book1 B23177Takeoff
A B C A B C
1 200 14 1 200
2 201 4 2 202
3 203 4 3 205

Any help would be much appreciated. MicheleP

 

[MicheleP]

Shane,

Pete_UK's solution worked. Thank you for your time...

Hi,

A couple of things -
1 .There is nothing in your formula that uses any cells in column C, so it's
not clear why entering anything in C2 or C1 would have any effect on the
formula?
2. In this part of the formula you are comparing 1 cell with 3 cells and IF
does not allow this unless you enter it as an array.
B23177Takeoff.xls]H23177!$A$1:$A$3=A1
3. What exactly are you trying to accomplish with this formula? What if A1
and A2 of one sheet = A1 of the other sheet, should the result be different
from what happens if A1 only equals A3?
4. This may be intentional but by putting quotes around the "14" and "4" you
are making them text, not numbers, is that what you want to do?

--
Thanks,
Shane Devenshire

Hi,
I've tried creating a formula that will return the value "14" if true or "4"
if false but i can't get in to work.

*=IF([B23177Takeoff.xls]H23177!$A$1:$A$3=A1,"14","4"). I'm compare two
separate spread sheets and the formula is in Book1 C1, C2, etc. When in
enter C1 the it returns the correct infromation, but once I enter in C2 it's
incorrect.

Book1 B23177Takeoff
A B C A B C
1 200 14 1 200
2 201 4 2 202
3 203 4 3 205

Any help would be much appreciated. MicheleP

 

[Pete_UK]

Glad to hear it, Michele - thanks for feeding back.

Pete