Controlling visibility of cells in Excel

[sanddrag]

Here's what I want to do in Excel. I want to have a bunch of data (like forces on an object sitting on a ramp) based on the input of something like an angle in degrees from 0-90 in 1 degree increments. I would like to have all 90 rows, one for each angle. But say I want to display the data for only 0-30 degrees. Is there some sort of function I can use where I'll have a cell for "up until what angle do you wish to display?" and then I won't see anything beyond whatever value is input in that cell?

Or maybe I shouldn't say controlling the visibility, but maybe controlling how many times the formula from the first cell is copied down. Or maybe when you input "up until what angle" it will create those cells on its own.

Sorry I didn't describe that well but hopefully you understand what I'm trying to do. I'm not sure if it is possible, but I thought I'd ask.

If you have a question about my description of what I want to do, please ask and I'll try to clarify.

[Karthik]

Sanddrag,

The spreadsheet I just made up does it in two different ways. The first worksheet uses conditional formatting to check if the cell value is greater than the inputted cutoff. If it is, the text is set to white. This method depends on you having all the data pre-generated.

The second version calculates the data each time. Given an inputted limit and increment (adds some versatility), it generates your list of angles. The formulas are all their for you to play with. I find Excel much easier to learn via re-engineering as opposed to explainations. So I've attached it.

It's late, so it might not be perfect...

Also, there's probably a better way to do it, there always seems to be with Excel.

[Dave Scheck]

Have you tried the Auto Filter feature? Here's a quick example.

Set up two columns with headers, Angle and Force seem like what you have. Once you have all your data entered, select the columns. Now click on Data->Filter->AutoFilter.

You should now have drop down menus in your header cells. Based on what you were asking, you'll want to select the "Custom" option from the Angle drop down. You'll get a dialog where you can enter your filtering criteria. When you enter your query, the rows that don't match will be hidden and the row numbers for the matching rows will be blue.

To get all of your data back, select the "All" option from the drop down.

To remove the drop downs, click Data->Filter->AutoFilter again.

What I like best is that it allows you to filter based on multiple columns. I use this a lot at work when I need to dig through data logs. I can very quickly show all messages of a certain type that occurred in a certain time interval.

Play around with it a little bit, it should be fairly intuitive.

Hope this helps.

[sanddrag]

Originally Posted by Karthik

The second version calculates the data each time. Given an inputted limit and increment (adds some versatility), it generates your list of angles. The formulas are all their for you to play with. I find Excel much easier to learn via re-engineering as opposed to explainations. So I've attached it.

That's almost exactly what I wanted. However, I didn't like the sort of back door route of using the hidden "A" column. So, I devised a better IF statement to take care of that. Take a look.

[Karthik]

Originally Posted by sanddrag

That's almost exactly what I wanted. However, I didn't like the sort of back door route of using the hidden "A" column. So, I devised a better IF statement to take care of that. Take a look.

Very nice. I like this new solution better as well. Although Dave's method sounds even better. I've never played with Auto-Filter before, but I am definitely going to in the future.

[sanddrag]

I tried Autofilter and didn't get much luck out of it. The IF function method is working perfectly. Go check out what I was up to in my new whitepaper "Object on an Incline Spreadsheet" It also makes one quite tricky but very creative (imho) use of Conditional Formatting and a use of cell data validation.

Excel is a much more powerful program than most people know. I'm glad I'm finally getting a chance to explore it in more depth than just basic arithmetic and graphs.