Blank rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brian.baker13@googlemail.com - 19 Nov 2007 15:50 GMT

Hi

I have a summary table looking at rows in another table

i.e.
=a13,=a14,=a15 etc etc

When the corresponding cells are empty I am left with blank rows on my
summary table.

Is there any way of eliminating the row automaticall when a blank cell
is looked up. Thus will straighten my summary table up and reduce the
blank rows so I only have those with data in showing?

Thanks

Brian

Reply to this Message

Pete_UK - 19 Nov 2007 15:57 GMT

Apply a filter to the column returning data - select non-blanks from
the filter pull-down (or if these are numbers select Custom | Not
equal to | 0), and this will display only those rows with data in
them.

Hope this helps.

Pete

On Nov 19, 3:50 pm, "brian.bake...@googlemail.com"
<brian.bake...@googlemail.com> wrote:

> Hi
>
[quoted text clipped - 13 lines]
>
> Brian

Reply to this Message

brian.baker13@googlemail.com - 19 Nov 2007 18:03 GMT

> Apply a filter to the column returning data - select non-blanks from
> the filter pull-down (or if these are numbers select Custom | Not
[quoted text clipped - 27 lines]
>
> - Show quoted text -

Cheers Pete,

Will try out tomorrow does look straightforward

Reply to this Message

Pete_UK - 19 Nov 2007 19:04 GMT

Thanks for feeding back, Brian.

I use the method all the time, where I can have a reporting sheet with
all categories listed, and then each month if there is nothing for a
particular category the filter hides it and the table bunches up
accordingly for the print-out. Next month I re-apply the filter.

Pete

On Nov 19, 6:03 pm, "brian.bake...@googlemail.com"
<brian.bake...@googlemail.com> wrote:

> > Apply a filter to the column returning data - select non-blanks from
> > the filter pull-down (or if these are numbers select Custom | Not
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message

Bernard Liengme - 19 Nov 2007 16:13 GMT

Let's say your summary table starts in G1 (adjust as needed)
In H1 enter the text A13; in H2 the text A14, and so on
In G1 =INDIRECT(H1) and copy down as far as needed
In I1 enter =--ISBLANK(INDIRECT(H1) and copy down the column as needed
Let's say the last row is 20
Now sort G1:I20 by column I
Now all you numbers are at the top and zeros (from blank cells) are at the
bottom
Delete row that are not required.
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Hi
>
[quoted text clipped - 13 lines]
>
> Brian

Reply to this Message

brian.baker13@googlemail.com - 19 Nov 2007 18:01 GMT

On Nov 19, 4:13 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Let's say your summary table starts in G1 (adjust as needed)
> In H1 enter the text A13; in H2 the text A14, and so on
[quoted text clipped - 34 lines]
>
> - Show quoted text -

Thankyou for your kind assistance, I will try tomorrow

Reply to this Message