Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
I have a spreadsheet that has 8 seperate tables that do a
calculation. If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
On Nov 4, 10:24*pm, Mel wrote:
I have a spreadsheet that has 8 seperate tables that do a calculation. *If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. * What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. *Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel Hello Mel, I'm not really clear what you want to do. What value is in M8, can you give an example. Are you looking for a match with one of those 8 cells. If M8 = BA8 for instance then what value would you like to return? regards, barry |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
On Nov 4, 4:31*pm, barry houdini wrote:
On Nov 4, 10:24*pm, Mel wrote: I have a spreadsheet that has 8 seperate tables that do a calculation. *If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. * What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. *Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel Hello Mel, I'm not really clear what you want to do. What value is in M8, can you give an example. Are you looking for a match with one of those 8 cells. If M8 = BA8 for instance then what value would you like to return? regards, barry- Hide quoted text - - Show quoted text - The info in M8 will be from one of the 8 fields. One field will have a value such as NUONT01 and the rest will have 'false'. Only one will have a value, the rest will have false listed. Mel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
If the values are "False" (as in the logical False condition) then this will
work: =IF(U8,U8,IF(AN8,AN8,IF(BA8,BA8,IF(BN8,BN8,IF(CA8, CA8,IF(CN8,CN8,IF(DA8,DA8,IF(DN8,DN8,"No Matches")))))))) "Mel" wrote: I have a spreadsheet that has 8 seperate tables that do a calculation. If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
On Nov 4, 8:43*pm, dhstein wrote:
If the values are "False" (as in the logical False condition) then this will work: =IF(U8,U8,IF(AN8,AN8,IF(BA8,BA8,IF(BN8,BN8,IF(CA8, CA8,IF(CN8,CN8,IF(DA8,DA8*,IF(DN8,DN8,"No Matches")))))))) "Mel" wrote: I have a spreadsheet that has 8 seperate tables that do a calculation. *If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. * What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. *Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel .- Hide quoted text - - Show quoted text - no, this will not work. in the 8 fields that I have listed, there will only be one that had data ie (NUONT2) and all the other fields will be displaying 'FALSE'. I need to be able to only display the field with the data and not 'false'. thx Mel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
nested function to select value from 8 options
On Nov 5, 2:49*pm, Mel wrote:
On Nov 4, 8:43*pm, dhstein wrote: If the values are "False" (as in the logical False condition) then this will work: =IF(U8,U8,IF(AN8,AN8,IF(BA8,BA8,IF(BN8,BN8,IF(CA8, CA8,IF(CN8,CN8,IF(DA8,DA8**,IF(DN8,DN8,"No Matches")))))))) "Mel" wrote: I have a spreadsheet that has 8 seperate tables that do a calculation. *If the input option matches one of the 8, a value will be displayed, if not, 'False' will be displayed. * What I am trying to do is look at all 8 fields and the one that is not equal to 'false', show that value. field to display in is M8. *Fields with the data a U8, AN8, BA8, BN8, CA8, CN8, DA8, DN8. I will be copying this formula down for the forty rows below. (M8 to M47). I have tried "If(and" and "if(or" but cannot get past the first 3. I know there has to be an easier way. thx Mel .- Hide quoted text - - Show quoted text - no, this will not work. * *in the 8 fields that I have listed, there will only be one that had data ie (NUONT2) and all the other fields will be displaying 'FALSE'. *I need to be able to only display the field with the data and not 'false'. thx Mel- Hide quoted text - - Show quoted text - Hello Mel, Try =SUBSTITUTE(U8&AN8&BA8&BN8&CA8&CN8&DA8&DN8,"FALSE" ,"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cannot select any options or type any data | Excel Discussion (Misc queries) | |||
How can I select multiple options in an Excel 2003 drop down list | Excel Discussion (Misc queries) | |||
cannot select options in AutoShapes in Excell | Excel Discussion (Misc queries) | |||
data validation - select multiple options? | Excel Discussion (Misc queries) | |||
Select Case Options | Excel Discussion (Misc queries) |