| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to get the final "result" to show in one cell? Excell 2002
Hello, I am soooo confused with this scenario....
I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Not sure what you're trying to do but this *might* work (I'm just
guessing!): H8: =IF(E8,120,IF(D8,60,"")) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hi Lisa Ann
Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
MVP... That is exactly what I wanted....., it worked GREAT!! Thanks! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: Not sure what you're trying to do but this *might* work (I'm just guessing!): H8: =IF(E8,120,IF(D8,60,"")) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hey guys... one more problem that I came across... are you still there?
I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Lisa Ann,
The dollar signs and decimal places are controlled by the formatting in the F22 and G22 cells themselves, not any other cells. Use Format - Cells - Number with those cells selected, and set it to the accounting or currency format you want. Or just use the $ formatting button on the Formatting Toolbar. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
That would happen when the linked cells E22 and D22 are FALSE. When those
are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hello, What you mentioned below is correct: ......the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hello,
I just wanted to mention that I was wrong in my other "reply" below.... In looking at the sheet further, E22 is the ONLY cell that is displaying correctly using this formula in cell H22: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) E22 displays as $100.00 D22 as 50 F22 as 60 G22 as 90 But I would like them all to have the "accounting" displaying features like the $100.00. I have all the cells set with "accounting". Thanks..., I just thought I would clarify further. Any help is VERY much appreciated! Thanks and have a great day, Lisa Ann "Lisa Ann Kashner" wrote: Hello, What you mentioned below is correct: .....the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
E22 displays as $100.00
D22 displays as $ 50.00 F22 displays as 60 G22 displays as 90 With D22:G22 being the linked cells try this array formula**: =MAX(D22:G22*{50;100;60;90}) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, What you mentioned below is correct: .....the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hello, I put in the array formula in (H22) as you show below, but it gave me the #VALUE! error now. I don't understand what you mention about the "key combinations" either. Can you please explain? Thanks, Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: E22 displays as $100.00 D22 displays as $ 50.00 F22 displays as 60 G22 displays as 90 With D22:G22 being the linked cells try this array formula**: =MAX(D22:G22*{50;100;60;90}) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, What you mentioned below is correct: .....the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
Hi Lisa Ann
I tried T. Valko solution and it works fine, but the formula needs to be entered using Ctrl+Shift+Enter...meaning you need to hold down the Ctrl key and the Shift Key and then hit the ENTER key, if done correctly you'll see curly brackets { } on each end of the formula in the formula bar. you can also try this non array formula: =IF(D22,50,IF(E22,100,IF(F22,60,IF(G22,90,"")))) Regards! Jean-Guy Regards! Jean-Guy "Lisa Ann" wrote: Hello, I put in the array formula in (H22) as you show below, but it gave me the #VALUE! error now. I don't understand what you mention about the "key combinations" either. Can you please explain? Thanks, Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: E22 displays as $100.00 D22 displays as $ 50.00 F22 displays as 60 G22 displays as 90 With D22:G22 being the linked cells try this array formula**: =MAX(D22:G22*{50;100;60;90}) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, What you mentioned below is correct: .....the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to get the final "result" to show in one cell? Excell 2002
THANKS A MILLION! It worked just fine! :) Lisa Ann "pinmaster" wrote: Hi Lisa Ann I tried T. Valko solution and it works fine, but the formula needs to be entered using Ctrl+Shift+Enter...meaning you need to hold down the Ctrl key and the Shift Key and then hit the ENTER key, if done correctly you'll see curly brackets { } on each end of the formula in the formula bar. you can also try this non array formula: =IF(D22,50,IF(E22,100,IF(F22,60,IF(G22,90,"")))) Regards! Jean-Guy Regards! Jean-Guy "Lisa Ann" wrote: Hello, I put in the array formula in (H22) as you show below, but it gave me the #VALUE! error now. I don't understand what you mention about the "key combinations" either. Can you please explain? Thanks, Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: E22 displays as $100.00 D22 displays as $ 50.00 F22 displays as 60 G22 displays as 90 With D22:G22 being the linked cells try this array formula**: =MAX(D22:G22*{50;100;60;90}) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hello, What you mentioned below is correct: .....the check boxes are for prices and whichever check box is checked that's the amount you want in the cell..... Two of my cells are displaying correctly when checked off - but two are not (as I mention below). Thanks soooo much for you thoughts on this!! Lisa Ann -- Thanks in advance! Lisa Ann Kashner "T. Valko" wrote: That would happen when the linked cells E22 and D22 are FALSE. When those are FALSE the first part of the formula returns an empty TEXT ("") string which is concatenated to the result of the second part of the formula. So, what you get is a result like this: ""60 or ""90. You can't see the "", all you see is 60 or 90 but it's a TEXT string so the accounting format doesn't apply. There's probably a better way to do this if you'd tell use what it is you're doing. I can sort of guess: the check boxes are for prices or fees and whichever check box is checked that's the amount you want in the cell? Or , you want the highest amount if more than one check box is checked? -- Biff Microsoft Excel MVP "Lisa Ann Kashner" wrote in message ... Hey guys... one more problem that I came across... are you still there? I came across a situation that I have 4 different check boxes. Each one with a different value. This is my formula: H22 CELL CONTAINS: =IF(E22,100,IF(D22,50,"")&IF(F22,60,IF(G22,90,"")) ) The H22 column is set up to display the numbers as: accounting/2 decimal places/dollar sign. 2 columns are displaying correctly AND 2 columns are not: E22 displays as $100.00 D22 displays as $ 50.00 BUT... F22 displays as 60 G22 displays as 90 What am I doing wrong? F22 and G22 are missing the $ and decimal place? Thank you! Lisa Ann "Lisa Ann Kashner" wrote: WONDERFUL! Thank you.... I would just love to have you guys "smarts"!!!! -- Thanks in advance! Lisa Ann Kashner "pinmaster" wrote: Hi Lisa Ann Maybe something like this: =IF(D8=TRUE,60,"")&IF(E8=TRUE,IF(D8=TRUE," - 120",120),"") HTH Jean-Guy "Lisa Ann Kashner" wrote: Hello, I am soooo confused with this scenario.... I have checkboxes set up. cell H8 has: 1st checkbox formula is: =IF(D8=TRUE,60,"") cell I8 has: 2nd checkbox formula is: =IF(E8=TRUE,120,"") I would like the final result to show up in H8. For example: If D8 was checked off - I would like the result to show up in H8. If E8 was checked off - I would like the result to show up in H8 too. How is this accomplished? Right now I have 2 total columns BUT I would only like one Please help! Thanks in advance! Lisa Ann |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| when a "check box" is checked, a "result" to be shown in another c | Excel Discussion (Misc queries) | |||
| Show a blank result in a cell when there is no value in the "Lookup" cell | New Users to Excel | |||
| Show a "$" at the beging of this result =ROUND(D99*0.75,4)&".00 Ro | Excel Discussion (Misc queries) | |||
| Why does excell switch my stock symbol "ACN" to "CAN"? | Setting up and Configuration of Excel | |||
| if "a" selected from dropdown menu then show "K" in other cell | Excel Worksheet Functions | |||
Linear Mode
