| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statemrent
Hello,
I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Hi Mac,
Check out the group for today's question...Jim May asked a similar question HTH -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "mac" wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Please note: not all of us use the same newsgroup reader. Most of us cannot
rate your answer. -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Pranav Vaidya" wrote in message ... Hi Mac, Check out the group for today's question...Jim May asked a similar question HTH -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "mac" wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
sorry..forgot to mention the name
it was named as Editing and moving from 6 if to 7 if -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "mac" wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Hi,
Thank you for your help. What does Bob Phillips mean by multiple cells. As you can tell I am still learning. I just need one more nesting if statement. Any ideas? Thank you. -- thank you mac "Pranav Vaidya" wrote: sorry..forgot to mention the name it was named as Editing and moving from 6 if to 7 if -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "mac" wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Bob meant that you can split the formula up into smaller chunks into
several cells, and then combine them. See my post for an alternative approach. Pete On Oct 3, 4:57 pm, mac wrote: Hi, Thank you for your help. What does Bob Phillips mean by multiple cells. As you can tell I am still learning. I just need one more nesting if statement. Any ideas? Thank you. -- thank you mac "Pranav Vaidya" wrote: sorry..forgot to mention the name it was named as Editing and moving from 6 if to 7 if -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer is useful, please rate this post as an ANSWER!! "mac" wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
You have duplicated some of your checks. You have:
IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, so this can be reduced to: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, thus saving you two levels of nesting. Here's another way - set up this table in the same sheet as your formula, say in cells X1 to Y6: '15764 J '14484 I '99922 L '39300 I '49340 P '21604 R The apostrophe is to turn the numbers into text - you won't see them on screen. Then use this formula: =IF(ISNA(VLOOKUP(C6,X$1:Y$6,1,0)),0,INDIRECT("'MA RUGS'!"&VLOOKUP(C6,X $1:Y$6,2,0)&"10")) The table can be made much larger, and you only need to change the table references in the VLOOKUP parts of the formula. Note that if you wanted to copy this down several rows, you would need to change the final "10" (to something like ROW(A10)) Hope this helps. Pete On Oct 3, 4:32 pm, mac wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Hi Peter_UK
That works great!!!! But, I have another column with the same (15764,14484, etc.) that will give me the result for a different year, How can I use this formula for different years. The formula works for 2007, but I need the 2006 column also, that is why I was using column references. Thank you for you help!!!!!! -- thank you mac "Pete_UK" wrote: You have duplicated some of your checks. You have: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, so this can be reduced to: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, thus saving you two levels of nesting. Here's another way - set up this table in the same sheet as your formula, say in cells X1 to Y6: '15764 J '14484 I '99922 L '39300 I '49340 P '21604 R The apostrophe is to turn the numbers into text - you won't see them on screen. Then use this formula: =IF(ISNA(VLOOKUP(C6,X$1:Y$6,1,0)),0,INDIRECT("'MA RUGS'!"&VLOOKUP(C6,X $1:Y$6,2,0)&"10")) The table can be made much larger, and you only need to change the table references in the VLOOKUP parts of the formula. Note that if you wanted to copy this down several rows, you would need to change the final "10" (to something like ROW(A10)) Hope this helps. Pete On Oct 3, 4:32 pm, mac wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
I'm a bit confused - are you saying that column C is for 2007 values
but you have another column (B, by any chance?) for 2006? Or is it that columns I, J, L, P and R are the 2007 values, but you have some other column(s) that you also want to get data from? In what way would you like to use these values? Please describe the layout of your data and what it is you want to do, then I'll be able to advise you more directly. Pete On Oct 3, 6:56 pm, mac wrote: Hi Peter_UK That works great!!!! But, I have another column with the same (15764,14484, etc.) that will give me the result for a different year, How can I use this formula for different years. The formula works for 2007, but I need the 2006 column also, that is why I was using column references. Thank you for you help!!!!!! -- thank you mac "Pete_UK" wrote: You have duplicated some of your checks. You have: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, so this can be reduced to: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, thus saving you two levels of nesting. Here's another way - set up this table in the same sheet as your formula, say in cells X1 to Y6: '15764 J '14484 I '99922 L '39300 I '49340 P '21604 R The apostrophe is to turn the numbers into text - you won't see them on screen. Then use this formula: =IF(ISNA(VLOOKUP(C6,X$1:Y$6,1,0)),0,INDIRECT("'MA RUGS'!"&VLOOKUP(C6,X $1:Y$6,2,0)&"10")) The table can be made much larger, and you only need to change the table references in the VLOOKUP parts of the formula. Note that if you wanted to copy this down several rows, you would need to change the final "10" (to something like ROW(A10)) Hope this helps. Pete On Oct 3, 4:32 pm, mac wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Hi Pete
I have 18 columns that have amounts in them (44 rows) ex below 15764 14484 2006 2007 2006 2007 54.32 55.60 600.47 634.75 36.20 98.00 522.30 535.00 etc if the # is 15764 I want the data to populate in another worksheet under 2006 and the 2007 # to populate under 2007 for the 44 rows. I hope I am not confusing you more. The nestnng worked but I was one shoirt. Thank you for any help you can give me. -- thank you mac "Pete_UK" wrote: I'm a bit confused - are you saying that column C is for 2007 values but you have another column (B, by any chance?) for 2006? Or is it that columns I, J, L, P and R are the 2007 values, but you have some other column(s) that you also want to get data from? In what way would you like to use these values? Please describe the layout of your data and what it is you want to do, then I'll be able to advise you more directly. Pete On Oct 3, 6:56 pm, mac wrote: Hi Peter_UK That works great!!!! But, I have another column with the same (15764,14484, etc.) that will give me the result for a different year, How can I use this formula for different years. The formula works for 2007, but I need the 2006 column also, that is why I was using column references. Thank you for you help!!!!!! -- thank you mac "Pete_UK" wrote: You have duplicated some of your checks. You have: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, so this can be reduced to: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, thus saving you two levels of nesting. Here's another way - set up this table in the same sheet as your formula, say in cells X1 to Y6: '15764 J '14484 I '99922 L '39300 I '49340 P '21604 R The apostrophe is to turn the numbers into text - you won't see them on screen. Then use this formula: =IF(ISNA(VLOOKUP(C6,X$1:Y$6,1,0)),0,INDIRECT("'MA RUGS'!"&VLOOKUP(C6,X $1:Y$6,2,0)&"10")) The table can be made much larger, and you only need to change the table references in the VLOOKUP parts of the formula. Note that if you wanted to copy this down several rows, you would need to change the final "10" (to something like ROW(A10)) Hope this helps. Pete On Oct 3, 4:32 pm, mac wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statemrent
Mac,
the table I gave you just converts the number into the column you want to get the data from, so for 15764 the data would be obtained from column J in the MA RUGS sheet. If you are now saying that you have paired columns, one for 2006 and another for 2007, and that you need to extract 2006 data into one sheet and 2007 data into another sheet, then you can use the same approach of putting a simple table in both sheets, listing your criteria numbers (are these account numbers?) and then next to each you need to put the column letter where that data will come from. The formula remains the same in both sheets, though if you have 18 columns, or 9 pairs of columns, then the lookup tables should have 9 entries and you will need to change the Y$6 references to Y$9 in the formula. If you are still unsure you can send me a copy of your file - desensitize it first if necessary. Hope this helps. Pete On Oct 4, 1:56 am, mac wrote: Hi Pete I have 18 columns that have amounts in them (44 rows) ex below 15764 14484 2006 2007 2006 2007 54.32 55.60 600.47 634.75 36.20 98.00 522.30 535.00 etc if the # is 15764 I want the data to populate in another worksheet under 2006 and the 2007 # to populate under 2007 for the 44 rows. I hope I am not confusing you more. The nestnng worked but I was one shoirt. Thank you for any help you can give me. -- thank you mac "Pete_UK" wrote: I'm a bit confused - are you saying that column C is for 2007 values but you have another column (B, by any chance?) for 2006? Or is it that columns I, J, L, P and R are the 2007 values, but you have some other column(s) that you also want to get data from? In what way would you like to use these values? Please describe the layout of your data and what it is you want to do, then I'll be able to advise you more directly. Pete On Oct 3, 6:56 pm, mac wrote: Hi Peter_UK That works great!!!! But, I have another column with the same (15764,14484, etc.) that will give me the result for a different year, How can I use this formula for different years. The formula works for 2007, but I need the 2006 column also, that is why I was using column references. Thank you for you help!!!!!! -- thank you mac "Pete_UK" wrote: You have duplicated some of your checks. You have: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, so this can be reduced to: IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10, thus saving you two levels of nesting. Here's another way - set up this table in the same sheet as your formula, say in cells X1 to Y6: '15764 J '14484 I '99922 L '39300 I '49340 P '21604 R The apostrophe is to turn the numbers into text - you won't see them on screen. Then use this formula: =IF(ISNA(VLOOKUP(C6,X$1:Y$6,1,0)),0,INDIRECT("'MA RUGS'!"&VLOOKUP(C6,X $1:Y$6,2,0)&"10")) The table can be made much larger, and you only need to change the table references in the VLOOKUP parts of the formula. Note that if you wanted to copy this down several rows, you would need to change the final "10" (to something like ROW(A10)) Hope this helps. Pete On Oct 3, 4:32 pm, mac wrote: Hello, I have this if statement and when I go to add 2 more if statements I get an error. Can you help? Any help will be greatly appreciated. Thank you =IF(C6="15764",'MA RUGS'!J10,IF(C6="14484",'MA RUGS'!I10,IF(C6="99922",'MA RUGS'!L10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="39300",'MA RUGS'!I10,IF(C6="49340",'MA RUGS'!P10,IF(C6="21604",'MA RUGS'!R10,0)))))))) -- thank you mac- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
