Extract Information To Another Worksheet

[M Imran Buhary]

Hi All,
I have a worksheet with Colonm Headings and Data below each heading, except
the "Column A" (in some cases it has and in some cases it doesnt). I want to
extract all rows where there is data in "Column A" to a separate worksheet.
The Entire Row should be copied to the new Worksheet once i enter a value in
Column A for each record and the new sheet should be frequently updated from
the original list, its like a building list.

 

[Gary''s Student]

In a separate, helper, column in row 2 enter:

=--ISBLANK(A2)

and copy down

Then switch on Autofilter and set this new column to display only 0
Then copy the visible rows and paste them to your separate worksheet
Then remove Autofilter in the original sheet

 

[Max]

One formulas play which makes it dynamic as required

Assume source data in Sheet1, cols A to C, from row2 down. Col A is the key
col, as per post

In Sheet2,

Paste the same col headers into B11

Put in A2:
=IF(Sheet1!A2="","",ROW())
Leave A1 blank

Put in B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))

Copy B2 to D2. Select A22, copy down to cover the max expected extent of
data in Sheet1, say down to D200. Hide away col A. Cols B to D will return
the required results, ie only the lines where col A in Sheet1 is not blank,
all neatly bunched at the top. When you update in Sheet1's col A, Sheet2
will reflect it automatically.

 

[M Imran Buhary]

Thanks a lot Max.
That was really impressive and helpful.
keep up the good work.
Regards,
Imran.

 

[M Imran Buhary]

Hi Buddy,
I think Max gave me the answer I was looking for.
I wanted to do this without the filtering machanism. And auto updation too.
So, thanks a lot for your time and effort anyway.
keep up the good work Experts....
Regards,
Imran.

 

[Max]

Welcome. Glad it helped.

 

[M Imran Buhary]

Hi Max,

You gave me this tip long time ago. Sorry to disturb you again in this one.
Suppose in one column i have maturity dates, Using the same formulas you
gave me earlier is it possible reflect the information automatically sorted
by the column which has the dates?
How do I do this?

 

[M Imran Buhary]

Hi Max, I added you in my yahoo mail chat. I need some help on this function
you suggested me below. Can we have a chat. please add me on your yahoo
(e-mail address removed)

in your answer below as you have designed it the data is sorted based on
numbers in Column A of sheet 1. Can I sort the data based on a different
column, e.g I have dates in another column and I need to stack the data in
sheet 2 based on this. Hope you understood my question.

Thanks & regards,