Show a sheet1 row in sheet2 based on values in col sheet1.A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!
Please help!
I have a large sheet with numerous rows in sheet1. Column A in sheet-1 is either blank or has a value. I want to show in sheet-2 (a new sheet) all sheet1 rows that have a value in column A.

Thanks in advance,
Michelle
 
Hi Michelle
one way:
- select your range in sheet 1
- goto 'Data Filter - Autofilter'
- Filter all relevant row
- copy the row and paste them on sheet 2
 
Hi Michelle
never say never :-)
See below a formula appraoch. Some assumptions:
- use column A on sheet 1 to check if the row is empty or not
- start the formula in row one on the second sheet.

Enter the following in cell A1 on your second sheet (enter this as
array formula with CTRL+SHIFT+ENTER)
=IF(ISERROR(INDEX(A$1:A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100),1
000),ROW()))),"",INDEX(A$1:A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$1
00),1000),ROW())))

copy this to the right and down



--
Regards
Frank Kabel
Frankfurt, Germany
Michelle said:
Frank,
Thanks. That's what I am currently doing, however, I have to do this
Click to expand...
each time the data changes. I was hoping there's some type of pivot
table option I could use. Seems like there isn't any!
 
Hi
you may add the sheet reference. So use
=IF(ISERROR(INDEX('sheet1'!A$1:A$100,SMALL(IF('sheet1'!$A$1:$A$100<>"",
ROW('sheet1'!$A$1:$A$100),1000),ROW()))),"",INDEX('sheet1'!A$1:A$100,SM
ALL(IF('sheet1'!$A$1:$A$100<>"",ROW('sheet1'!$A$1:$A$100),1000),ROW()))
)
 
Frank,
Thanks a ton! You just saved me about an hour each day for the next few days!

Michelle!
 
Back
Top