Show / hide data by seeing if a field says "Yes"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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","")
 
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
 
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),"")
 
Dave Peterson said:
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),"")
Click to expand...
 
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
 
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),"")
 
Dave,

By assuming that, you would also be implying that I have a functioanl brain
in my head... ;-)

Thanks again for the help.

joe
 
Just say posted the wrong message in the newsgroup--the workbook was fine. That
always works for me! <vbg>

Joseph said:
Dave,

By assuming that, you would also be implying that I have a functioanl brain
in my head... ;-)

Thanks again for the help.

joe
Click to expand...
 
Back
Top