I can't make what I want to happen, happen.

**Thanos** · 01-16-2007, 12:38 AM

I have attached the spreadsheet I am working on called Tree Sample Form. In this Excel spreadsheet, I have three worksheets. One is called "Tree Data"; the second, "Bark_Cone_Leaves"; and finally "Summary".
In the "Tree Data" worksheet I want to mark off which species I have in a sample. Then on the "Summary" I want the descriptions of cone, bark, and leaves to appear beside the species.
I can make all of this happen, but on the "Summary" worksheet, any species I have not marked off in the "Tree Data" shows up as "#N/A". I don't want this. In fact I want no space between the different species in a sample.
Please look at my attached example and HELP!!!

**jasoncw** · 01-16-2007, 12:51 AM

The simplest solution would be to just AutoFilter the Summary sheet. Place your cell in the table, then select Data > Filter > AutoFilter from the menu. Then for column A, select NonBlanks.

The only down side is that each time you change your tree selections, you would have to reselect NonBlanks to have the proper results on your Summary sheet.

**Thanos** · 01-16-2007, 10:47 AM

The AutoFilter does what I want, but I am still wondering if there is a way to make it happen automatically and instantly when I fill in the data in the "Tree Data"?

Also, can a person make a formula respond to a "Check Box"?. For example, if the check box is checked can a formula return the value of 1?

**jasoncw** · 01-16-2007, 11:45 AM

This should accomplish what you need hopefully.

**Thanos** · 01-16-2007, 11:53 AM

I think you made a macro to autofilter the Summary page. How did you make that?

**jasoncw** · 01-16-2007, 12:18 PM

You are correct. I made a couple of changes to the spreadsheet.

First, I incorporated the use of check boxes, rather than using an "X", per your above request. The way I did this was create a check box, then right-click and select Properties. From the properties menu, you can change the title of the check box (which I did for each tree). You can also link it to a cell. What this does is for example, in check box 1, it is linked to cell C2. If the box is checked, C2 = TRUE. Otherwise C2 = FALSE. Then I slightly modified your formulas in column B to incorporate TRUE and FALSE, rather than "X" or "".

Then I used the following macro for the Summary sheet:

Please Login or Register  to view this content.

What this code does is that every time you activate (select) this sheet, it will perform the above steps. It will select cell A1 and then autofilter the selection based on nonblanks. You can view the code by right-clicking on the 'Summary' tab and select view code.

By the way, there are a lot of macros that you can "record" yourself if you do not know the proper language. That's how I taught myself (not including all the wonderful help on this site!). From the menu, select Tools > Macro > Record, then perform your tasks, and click the stop button. You can then view the code you just "recorded" with ALT + F11.