Is it possible to conditionally hide a column in Numbers?

Question

Level 1

8 points

Is it possible to conditionally hide a column in Numbers?

Can anyone help? I have been struggling with this for ages and have looked for solutions to this on this forum but haven't been able to make them work.

I would like to be able to hide a column in a table if the value in a cell elsewhere on the same sheet is "Yes" (and keep the column if the value is "No").

I've tried the following methods without success:
1. Creating an extra "superposed" table consisting of one cell with the value set to equal the "Yes/No" cell, then conditionally formatting it so the text and the fill are white if the text contains "Yes". I've tried putting this underneath the column I wish to hide (by "sending to the back"), and making the column's fill transparent (by moving the opacity slider down to 0% on the colour fill box), but can't make it work.

2. Creating a second column next to the "Yes/No" one, conditionally formatting it and trying to copy the formatting to the cells in the column I would like to hide - I think I am on the wrong track with this one.

If it isn't possible to hide the column, then it would be OK if I could at least make it so the cells in that column could read "not applicable" or something similar.

I am getting quite frustrated - I am sure there must be some way of doing this - any advice would be greatly appreciated!

iMac 24", Mac OS X (10.5.8), iwork 09

Posted on Aug 24, 2009 8:23 AM

Reply

Answer 1

Aug 24, 2009 9:12 AM in response to Susy L.

Hi Susy L.,

The solution to your question depends on how large your table is and whether you need to be able to edit the values in the column to be hidden.

Could you tell us more about your table?

Jerry

Reply

Answer 2

KOENIG Yvan

Level 8

41,819 points

Aug 24, 2009 9:30 AM in response to Susy L.

No need for complicated trickery.

store the values in an other column which is always hidden.
Here I assume that it's column Z.

In the existing column, insert the formula

=if($D$12=true,"",Z)

if the cell $D$12 (the one used as a switch) is true, the cells will display nothing.
They will display the value if $D$12 contains false.

Yvan KOENIG (VALLAURIS, France.) lundi 24 août 2009 18:30:40

Reply

Answer 3

Badunit

Level 7

30,714 points

Aug 24, 2009 3:44 PM in response to Susy L.

You can make the last column on a table completely disappear but it takes some trickery that I doubt is worth it. And it makes it difficult to edit the "hidden" column when it is unhidden. The trick is to place another table overtop of that last column. The top table covers all the rows, the header, the column name, etc. I don't recommend you going this route, but I wanted to demonstrate that it is possible.

In the image below, I somehow messed up the center table when I was putting the image together; the entire table is actually there. A link to the Numbers document is below so you can download it and see how it was done. It takes several steps to get the top table formatted correctly with no borders, no fill, formulas, and a conditional format.

http://files.me.com/pwb3/k33m5w.numbers.zip

Reply

Answer 4

KOENIG Yvan

Level 8

41,819 points

Aug 25, 2009 2:22 AM in response to Badunit

Why use this kind of trickery which apply only to the last column when the goal may be achieved with no trickery at all in a single table ?

Yvan KOENIG (VALLAURIS, France.) mardi 25 août 2009 11:22:39

Reply

Answer 5

Susy L. Author

Level 1

8 points

Aug 25, 2009 3:27 AM in response to KOENIG Yvan

Thank you all for your help.

Badunit - that is very cool and thanks for going to the trouble of doing that, but if I am not mistaken it was to show it could be done - as you say I would rather not go down that route.

I would prefer to use the method Yvan suggested, but can't seem to make it work - it works when the "switch cell"s value is True, but when it is false I just get a red triangle. I think this is because I don't understand what you mean by "store the values in an other column which is always hidden", Yvan? Which values and how?

Maybe this will help to clarify what I mean. This is a simplified (and completely made up!) analogy of what I am doing:

http://files.me.com/susylong/nlplfy

The purpose of the numbers document is to collect information about an individual case. First, in Table 1 I collect basic information, then in Table 2 further information about specific characteristics of that individual are collected. Obviously if the individual is bald, column D in Table 2 is irrelevant, so if B3 in Table 1 is True, I want all of the cells in column D, Table 2 to become blank, and if it is False, I want column D to stay as it is so that I can edit its content.

Hope this makes sense and thanks again for all your help.

Reply

Answer 6

KOENIG Yvan

Level 8

41,819 points

Aug 25, 2009 5:09 AM in response to Susy L.

The true datas are stored in column E which you will hide.
In the cells of column D the formula is:
=IF(Basic information :: $B$3=TRUE,"",E)
If the box is checked, the cells are blank, if the box is unchecked, the cells display the contents of the E cells.

Yvan KOENIG (VALLAURIS, France.) mardi 25 août 2009 14:08:53

Reply

Answer 7

Susy L. Author

Level 1

8 points

Aug 25, 2009 5:36 AM in response to KOENIG Yvan

That's fantastic - thank you!

Reply

Answer 8

Susy L. Author

Level 1

8 points

Aug 25, 2009 5:47 AM in response to KOENIG Yvan

Although, I have just realised that this means that I can't then use the pop-up menus in column D. Any idea how I get round that?

Susy

Reply

Answer 9

KOENIG Yvan

Level 8

41,819 points

Aug 25, 2009 5:50 AM in response to Susy L.

Unhide column E so you will be able to use the popups.
Then Hide it again.

Yvan KOENIG (VALLAURIS, France.) mardi 25 août 2009 14:50:34

Reply

Answer 10

Badunit

Level 7

30,714 points

Aug 25, 2009 5:51 AM in response to Susy L.

I would do it slightly differently than Yvan.

1. Add a column E to your second table.

2. In that column put the formula =IF(Basic information :: $B$3,D,"")

3. In D2 put the conditional format of "if equal to E2 then white text on white fill". Do the same for D3, D4, etc.

4. Hide column E

That third step is the tedious one.

When you're done, if the checkbox is checked, the hair choices will "hide". If someone clicks on one of those cells they will see the popup but even if they select something it doesn't show up.

Reply

Answer 11

Susy L. Author

Level 1

8 points

Aug 25, 2009 6:08 AM in response to Badunit

That's great - now it works!

Thanks again!

Reply

Answer 12

KOENIG Yvan

Level 8

41,819 points

Aug 25, 2009 9:28 AM in response to Badunit

I felt that the need to unhide one column was less tedious than defining a conditional format for each cell but after all, it's a matter of taste 😉

Yvan KOENIG (VALLAURIS, France.) mardi 25 août 2009 18:14:24

Reply

Answer 13

Badunit

Level 7

30,714 points

Aug 25, 2009 8:08 PM in response to KOENIG Yvan

I believe the functionality of the spreadsheet was the important factor; the end user should not be required to hide/unhide a column. The tediousness of entering multiple conditional formats was the price to be paid. If Numbers ever gets filters for hiding columns, the solution to the problem will be easier.

In fact, now that I think about it, we may have done this poster a disservice. If the spreadsheet was transposed, the solution is easy. In the image below there is a hidden column E. In cell E4 is the formula =Basic information :: $B$3 . In the reorganization panel I set up a filter for "show rows that match column E is not true. A similar thing could be done for other rows. For example, say there was also a checkbox/popup for Male/Female and the selection was Male, you could hide rows that are female-specific and unhide rows that are male specific (and vice versa if the selection was Female).

Reply

Answer 14

Susy L. Author

Level 1

8 points

Sep 3, 2009 9:46 AM in response to Badunit

Thanks very much both of you - I have only just seen your latest responses. I managed to make it work with your first method, Badunit, but it's good to know there is also another way. I hadn't thought of using the reorganisation function. I am very impressed with this forum!

Reply