Extract Information To Another Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M Imran Buhary - 01 Apr 2007 09:20 GMT

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.

Signature

M Imran Buhary

Reply to this Message

Gary''s Student - 01 Apr 2007 13:12 GMT

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

Signature

Gary''s Student - gsnu200713

Reply to this Message

M Imran Buhary - 02 Apr 2007 08:52 GMT

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.

Signature

M Imran Buhary

> In a separate, helper, column in row 2 enter:
>
[quoted text clipped - 5 lines]
> Then copy the visible rows and paste them to your separate worksheet
> Then remove Autofilter in the original sheet

Reply to this Message

Max - 01 Apr 2007 13:51 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi All,
> I have a worksheet with Colonm Headings and Data below each heading,
[quoted text clipped - 8 lines]
> from
> the original list, its like a building list.

Reply to this Message

M Imran Buhary - 02 Apr 2007 08:50 GMT

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

Signature

M Imran Buhary

> One formulas play which makes it dynamic as required
>
[quoted text clipped - 29 lines]
> > from
> > the original list, its like a building list.

Reply to this Message

Max - 02 Apr 2007 11:42 GMT

Welcome. Glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Thanks a lot Max.
> That was really impressive and helpful.
[quoted text clipped - 3 lines]
> --
> M Imran Buhary

Reply to this Message

M Imran Buhary - 22 Jun 2008 08:05 GMT

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?

Signature

M Imran Buhary

> One formulas play which makes it dynamic as required
>
[quoted text clipped - 29 lines]
> > from
> > the original list, its like a building list.

Reply to this Message

Rate this thread: