Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
struggling with if logical test function
I have an Excel 2007 worksheet that is being used as a travel expense form
where I work. I have inherited this form, and have been charged with updating it. There is a summary section at the bottom of the form that is supposed to pull various account information from all over the page. The formula that is currently in the cell is this: =IF(H38=0," ",$O$48&","&$H$38&","&M49&","&$F$38&","&$L$38&","& "0"&","&$J$41&","&$J$38) which results in an account string, such as: 6450, 1005, 648001, 501205,,0,CR,3005 in other words, the information from cells O48, H38, M49, F38, L38, some fields I don't get, J41, and J38. I now need the result in this cell to look like this: 6450, 1005, 3005, 501205, CR, 648001 in other words, the information from cells O48, H38, J38, F38, J41, and M49 - eliminating a few fields from the old string and changing the order. Honestly, I have no idea what I'm looking at when I look at the formula. I understand the principle that my formula is set up to always test false, but I don't know how to read the value_if_false statement in order to know how to change the order of the result and eliminate some of the fields. I know this is kind of a silly question, and I'm not sure anyone can help based on what I've given you here, but at the very least, can someone point me toward a good resource about how to read the syntax? I just don't know what the ", &, and commas mean, and of course, I'm supposed to have this changed asap. Thank you! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
struggling with if logical test function
The &","& merely inserts a comma between two values.
So, $O$48&","&$H$38 inserts the value of O48, a comma, the value of H38. If O48 contains 6450, and H38 contains 1005, you will see 6450,1005. to get 6450, 1005 you need to change your formula to $O$48&", "&$H$38, which will insert not only a comma, but also a space, between the two amounts. To change the order, you therefore have to change the order in which you refer to the cells. So, =IF(H38=0,"",$O$48&", "&$H$38&", "&", "&$J$38&", "&$F$38&", "&$J$41&", "&$M$49) will do the trick -- HTH Kassie Replace xxx with hotmail "neuromoe" wrote: I have an Excel 2007 worksheet that is being used as a travel expense form where I work. I have inherited this form, and have been charged with updating it. There is a summary section at the bottom of the form that is supposed to pull various account information from all over the page. The formula that is currently in the cell is this: =IF(H38=0," ",$O$48&","&$H$38&","&M49&","&$F$38&","&$L$38&","& "0"&","&$J$41&","&$J$38) which results in an account string, such as: 6450, 1005, 648001, 501205,,0,CR,3005 in other words, the information from cells O48, H38, M49, F38, L38, some fields I don't get, J41, and J38. I now need the result in this cell to look like this: 6450, 1005, 3005, 501205, CR, 648001 in other words, the information from cells O48, H38, J38, F38, J41, and M49 - eliminating a few fields from the old string and changing the order. Honestly, I have no idea what I'm looking at when I look at the formula. I understand the principle that my formula is set up to always test false, but I don't know how to read the value_if_false statement in order to know how to change the order of the result and eliminate some of the fields. I know this is kind of a silly question, and I'm not sure anyone can help based on what I've given you here, but at the very least, can someone point me toward a good resource about how to read the syntax? I just don't know what the ", &, and commas mean, and of course, I'm supposed to have this changed asap. Thank you! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
struggling with if logical test function
Hi,
Here is one solution =IF(H38,$O$48&","&$H$38&","&$J$38&","&$F$38&","&$L $38&","&M49,"") If you are not copying this formula you can simplify it to =IF(H38,O48&","&H38&","&J38&","&F38&","&L38&","&M4 9,"") -- If this helps, please click the Yes button. Cheers, Shane Devenshire "neuromoe" wrote: I have an Excel 2007 worksheet that is being used as a travel expense form where I work. I have inherited this form, and have been charged with updating it. There is a summary section at the bottom of the form that is supposed to pull various account information from all over the page. The formula that is currently in the cell is this: =IF(H38=0," ",$O$48&","&$H$38&","&M49&","&$F$38&","&$L$38&","& "0"&","&$J$41&","&$J$38) which results in an account string, such as: 6450, 1005, 648001, 501205,,0,CR,3005 in other words, the information from cells O48, H38, M49, F38, L38, some fields I don't get, J41, and J38. I now need the result in this cell to look like this: 6450, 1005, 3005, 501205, CR, 648001 in other words, the information from cells O48, H38, J38, F38, J41, and M49 - eliminating a few fields from the old string and changing the order. Honestly, I have no idea what I'm looking at when I look at the formula. I understand the principle that my formula is set up to always test false, but I don't know how to read the value_if_false statement in order to know how to change the order of the result and eliminate some of the fields. I know this is kind of a silly question, and I'm not sure anyone can help based on what I've given you here, but at the very least, can someone point me toward a good resource about how to read the syntax? I just don't know what the ", &, and commas mean, and of course, I'm supposed to have this changed asap. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A logical test in the If function for blank, i.e., If blank? | Excel Worksheet Functions | |||
How do I write an If(And( function using dates as my logical test? | Excel Worksheet Functions | |||
EXCEL 2000 - IF function returns wrong result from logical test | Excel Worksheet Functions | |||
Can I create an "IF" function using cell color as a logical test? | Excel Worksheet Functions | |||
Logical test | New Users to Excel |