Extract Information To Another Worksheet

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

Guest

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.
 
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
 
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 B1:D1

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 A2:D2, 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.
 
Thanks a lot Max.
That was really impressive and helpful.
keep up the good work.
Regards,
Imran.
 
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.
 
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?
 
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,
 
Back
Top