Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
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 On Sep 27, 12:24*am, MicheleP wrote: 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 * |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
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 "MicheleP" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
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 "ShaneDevenshire" wrote: 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 "MicheleP" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
Thanks it worked.
"Pete_UK" wrote: 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 On Sep 27, 12:24 am, MicheleP wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
Shane,
Pete_UK's solution worked. Thank you for your time... "ShaneDevenshire" wrote: 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 "MicheleP" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if(formula)
Glad to hear it, Michele - thanks for feeding back.
Pete On Sep 30, 11:10*pm, MicheleP wrote: Thanks it worked. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|