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

[Joseph Hand]

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

 

[JLatham]

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]

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

 

[Dave Peterson]

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]

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]

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]

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]

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]

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

Dave,

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

Thanks again for the help.

joe