show non-blank via formula?

[Guest]

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!

 

[T. Valko]

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

 

[Guest]

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

 

[Guest]

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!

 

[T. Valko]

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$100)-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