Use formula to hide/delete cell?

[Lost!]

Hi all, I'm stuck on the last part of this project, and I need some
assistance!

One worksheet of my spreadsheet has a list of forms with a check box
next to each form name. When a box is selected, the corresponding form
name is pulled to a list of forms on two other worksheets of this
file.

The worksheets to which these things are pulled use a simple IF
function. It looks something like this:

=IF('Worksheet2'!I35=TRUE,'Worksheet2'!B35,"")

According to this formula, if the condition is TRUE (i.e. the box is
checked), the form name is listed on the lists. If the condition is
FALSE (not checked), the cell on the list is left blank. Here is where
I have the problem.

Let's say I check the first, fifth and last boxes on the list. This
will pull the first, fifth and last form names, but it will leave
spaces for all the forms in between. I don't want these spaces. Is
there a way to tell the formula to hide the cell (or delete it and
shift up) if the value is FALSE? I tried conditional formatting, but it
only lets you change how the cell looks (colors, etc.); it won't let you
hide or delete it.

Should I be trying some sort of macro?

Thanks for your help!!

 

[Backdoor Cover]

I would post this in the programming forum.

But what you can do is just create the most basic macro that sorts
sheet by a specific column. So that after you fill out the forms yo
can just run the macro quickly and push the empty rows to the bottom.
Then delete them.

Maybe not the best answer, but it will work

 

[Lost!]

Thanks for your help

 

[Gord Dibben]

Formulas cannot hid or delete cells or rows.

VBA would be required but try this first.

Select the data range.

F5>Special>Blanks>OK.

Now Edit>Delete.....choose entire row if that's the case or Shift Up to just
delete cells.


Gord Dibben Excel MVP