show non-blank via formula?
 

Hi all -
Ok, here's my problem: I have two worksheets. The 1st sheet has data
arranged like this:

Item1 SubItems Date1 Date2
subitem1
subitem2
subitem3

Item2 SubItems Date1 Date2
subitem1
subitem2
subitem3

etc

I would like to display only the item title (Item1, Item2, etc) on my 2nd
sheet without any blank spaces between them. IOW, like this:

Item1
Item2
Item3

etc

The data on sheet 1 changes often so I'd like sheet 2 to be dynamicallly
populated - IOW, by formula.

Any ideas?

thanks in advance!

show non-blank via formula?
 

Try this:

Assume the items are listed every 5 cells on sheet1 starting in cell A1:

A1 = item1
A6 = item2
A11 = item3
A16 = item4
etc

Enter this formula on sheet2 and copy down as needed:

=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*5,)

Biff

"MeatLightning" wrote in message
...
Hi all -
Ok, here's my problem: I have two worksheets. The 1st sheet has data
arranged like this:

Item1 SubItems Date1 Date2
subitem1
subitem2
subitem3

Item2 SubItems Date1 Date2
subitem1
subitem2
subitem3

etc

I would like to display only the item title (Item1, Item2, etc) on my 2nd
sheet without any blank spaces between them. IOW, like this:

Item1
Item2
Item3

etc

The data on sheet 1 changes often so I'd like sheet 2 to be dynamicallly
populated - IOW, by formula.

Any ideas?

thanks in advance!

show non-blank via formula?
 

Hmmmm... there's no fixed number of rows between each item... so one item
record might have two subitems.... another could have ten.

"T. Valko" wrote:

Try this:

Assume the items are listed every 5 cells on sheet1 starting in cell A1:

A1 = item1
A6 = item2
A11 = item3
A16 = item4
etc

Enter this formula on sheet2 and copy down as needed:

=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*5,)

Biff

"MeatLightning" wrote in message
...
Hi all -
Ok, here's my problem: I have two worksheets. The 1st sheet has data
arranged like this:

Item1 SubItems Date1 Date2
subitem1
subitem2
subitem3

Item2 SubItems Date1 Date2
subitem1
subitem2
subitem3

etc

I would like to display only the item title (Item1, Item2, etc) on my 2nd
sheet without any blank spaces between them. IOW, like this:

Item1
Item2
Item3

etc

The data on sheet 1 changes often so I'd like sheet 2 to be dynamicallly
populated - IOW, by formula.

Any ideas?

thanks in advance!

show non-blank via formula?
 

1) Enters this formual in cell A1 on sheet 2

=sheet1!A1

2) Drag down column

3) Filter, custom filter, does not equal 0

Good luck!

"MeatLightning" wrote:

Hi all -
Ok, here's my problem: I have two worksheets. The 1st sheet has data
arranged like this:

Item1 SubItems Date1 Date2
subitem1
subitem2
subitem3

Item2 SubItems Date1 Date2
subitem1
subitem2
subitem3

etc

I would like to display only the item title (Item1, Item2, etc) on my 2nd
sheet without any blank spaces between them. IOW, like this:

Item1
Item2
Item3

etc

The data on sheet 1 changes often so I'd like sheet 2 to be dynamicallly
populated - IOW, by formula.

Any ideas?

thanks in advance!

show non-blank via formula?
 

Well, your posted sample looked like it followed a pattern.

Try this array formula** :

=IF(ROWS($1:1)<=COUNTA(Sheet1!A:A),INDEX(Sheet1!A: A,SMALL(IF(Sheet1!A$1:A$100<"",ROW(Sheet1!A$1:A$1 00)-MIN(ROW(Sheet1!A$1:A$100))+1),ROWS($1:1))),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"MeatLightning" wrote in message
...
Hmmmm... there's no fixed number of rows between each item... so one item
record might have two subitems.... another could have ten.

"T. Valko" wrote:

Try this:

Assume the items are listed every 5 cells on sheet1 starting in cell A1:

A1 = item1
A6 = item2
A11 = item3
A16 = item4
etc

Enter this formula on sheet2 and copy down as needed:

=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*5,)

Biff

"MeatLightning" wrote in
message
...
Hi all -
Ok, here's my problem: I have two worksheets. The 1st sheet has data
arranged like this:

Item1 SubItems Date1 Date2
subitem1
subitem2
subitem3

Item2 SubItems Date1 Date2
subitem1
subitem2
subitem3

etc

I would like to display only the item title (Item1, Item2, etc) on my
2nd
sheet without any blank spaces between them. IOW, like this:

Item1
Item2
Item3

etc

The data on sheet 1 changes often so I'd like sheet 2 to be
dynamicallly
populated - IOW, by formula.

Any ideas?

thanks in advance!