Omit blank cells from function results

[Guest]

I have 6 sheets in my workbook. Sheet 1 is the main database of customers,
customer account #, phone number and tonnes of grain, etc. The other 5 sheets
have been categorized into different types of grain (i.e. spring wheat,
durum, barley, etc.). I have created a formula to copy the customer
information and grain tonnes from Sheet 1 into each of the corresponding 5
sheets. An example of one of my formulas looks like this:
IF(Main!F6>0,Main!B6," "). My formula works fine, except that my problem is
that if the first 20 customers didn't have spring wheat, then it will display
20 blank rows on my spring wheat sheet. How can I get it to omit blank cells
from my results?

 

[Roger Govier]

Hi Tanya

The quick answer to your question would be to perform a Sort on each sheet
using Customer as the sort key.
All the Blank rows will move to the bottom of the list.

Perhaps you could consider other methods to achieve your desired result.
Rather than having separate sheets, you could use Autofilter.
Highlight your headings and choose Data>Filter>Autofilter you could then
filter by type to show the list of Customers growing Durum for example.
If you need totals of tonnages etc. for type, then I would insert a few rows
at the top of the sheet and in the cell immediately above tonnage enter
=SUBTOTAL(9,F4:F1000) or whatever the correct range would be to cover the
maxiumum range of data, for whatever column holds the tonnages.

You might also care to look at the use of Pivot Tables.
An excellent starting point would be
http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[Guest]

Thanks for your quick response, but I was kind of hoping for a less "manual"
way of getting the same result. Is that possible?

 

[Roger Govier]

Hi Tanya

There was a posting a little while ago by Max which addressed a similar
problem.
I think you would need to have your field F6 (or a copy of it) as the first
column in your master list for it to work as per the example, but take a
look at

Here's the link to a sample file with the implemented construct:
http://www.savefile.com/files/9975448
File: Automatically sorting data_LeeHutch_misc.xls