Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
So I have this monster spread sheet that has a large input page, then several
other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
On the summary page
=IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
what if I wanted to show a bunch of cells?
I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
You have a couple of options.
You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
"Dave Peterson" wrote: You have a couple of options. You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
So wat ended up working is =IF(Input!$D$42="Yes", ROI!A88, ""). I then
dragged the auto fill in both directions. The array wouldn't work since I could not delete the zeros from all the fields that were not in use. (I need zero vaules shown elsewhere in the sheet...) thanks for all your help, Dave and JLathem. joe "Dave Peterson" wrote: You have a couple of options. You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
If you didn't want to see those 0's if D42 is Yes and A88 is empty, I would have
guessed that you would have used: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: So wat ended up working is =IF(Input!$D$42="Yes", ROI!A88, ""). I then dragged the auto fill in both directions. The array wouldn't work since I could not delete the zeros from all the fields that were not in use. (I need zero vaules shown elsewhere in the sheet...) thanks for all your help, Dave and JLathem. joe "Dave Peterson" wrote: You have a couple of options. You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
Dave,
By assuming that, you would also be implying that I have a functioanl brain in my head... ;-) Thanks again for the help. joe "Dave Peterson" wrote: If you didn't want to see those 0's if D42 is Yes and A88 is empty, I would have guessed that you would have used: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: So wat ended up working is =IF(Input!$D$42="Yes", ROI!A88, ""). I then dragged the auto fill in both directions. The array wouldn't work since I could not delete the zeros from all the fields that were not in use. (I need zero vaules shown elsewhere in the sheet...) thanks for all your help, Dave and JLathem. joe "Dave Peterson" wrote: You have a couple of options. You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show / hide data by seeing if a field says "Yes"
Just say posted the wrong message in the newsgroup--the workbook was fine. That
always works for me! <vbg Joseph Hand wrote: Dave, By assuming that, you would also be implying that I have a functioanl brain in my head... ;-) Thanks again for the help. joe "Dave Peterson" wrote: If you didn't want to see those 0's if D42 is Yes and A88 is empty, I would have guessed that you would have used: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: So wat ended up working is =IF(Input!$D$42="Yes", ROI!A88, ""). I then dragged the auto fill in both directions. The array wouldn't work since I could not delete the zeros from all the fields that were not in use. (I need zero vaules shown elsewhere in the sheet...) thanks for all your help, Dave and JLathem. joe "Dave Peterson" wrote: You have a couple of options. You can: Select a range that is the exact size as ROI!A88:G108 (21 rows by 7 columns) Then type your formula, but instead of hitting enter, hit ctrl-shift-enter. This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) or... You could use individual formulas: =IF(Input!D42="Yes", ROI!A88, "") =IF(Input!D42="Yes", ROI!b88, "") .... in all 147 cells. Depending on what's in the cells and what you want returned, you may want to use a formula like: =if(input!d42="yes",if(roi!a88="","",roi!a88),"") Joseph Hand wrote: what if I wanted to show a bunch of cells? I tried the following: =IF(Input!D42="Yes", ROI!A88:G108, "") the whole report I want to include is in those cells. Entering the above, I get a "$VALUE!". If I back out the rest fo the range and just include ROI!A88, that shows up fine. Thank you for the original reply. It is definately on the right track. joe "JLatham" wrote: On the summary page =IF('Input Page'!X9="YES",'Some Other Page'!B19,"") and even for related labels in cells if you want to hide them for neat appearance? =IF('Input Page'!X9="YES","Label: ROI Item 9","") "Joseph Hand" wrote: So I have this monster spread sheet that has a large input page, then several other pages for tweaking specific parameters. At the end, there is a report page that has a summary of the results from each other page. (it basically calculates storage needs, server sizing needs, and ROI for an Email Archiving product) What I want to do is either show or hide the ROI data from the summary page based on if I have a field on the Input page that contains the word "yes". Thanks in advance for any help. joe -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to hide "Send To Mail Recipient" data? | Excel Discussion (Misc queries) | |||
How to set the default "summarized by" of a data field to "Sum"? | Excel Discussion (Misc queries) | |||
Pivot Tables - How can I "reset" the selections in "Row Field"? | Excel Discussion (Misc queries) | |||
if "a" selected from dropdown menu then show "K" in other cell | Excel Worksheet Functions | |||
"Show Field List" in Pivot Table Toolbar doesn't work | Excel Discussion (Misc queries) |