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!F60,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?

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 DataFilterAutofilter 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

--
Regards

Roger Govier


"TanyaE" wrote in message
...
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!F60,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?

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" wrote:

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 DataFilterAutofilter 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

--
Regards

Roger Govier


"TanyaE" wrote in message
...
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!F60,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?

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895


--
Regards

Roger Govier


"TanyaE" wrote in message
...
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" wrote:

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 DataFilterAutofilter 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

--
Regards

Roger Govier


"TanyaE" wrote in message
...
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!F60,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?