Here's an easy dynamic formulas play which would deliver the equivalent
results that you seek on both counts. It might appeal to you.
In Sheet1,
you have the source data in cols A to D as posted,
with the key col = col D ("processed?")
Create 2 adjacent criteria cols to flag the 2 statuses in "processed?",
ie "yes" and <"yes"
In E2: =IF(D2="yes",ROW(),"")
In F2: =IF(D2<"yes",ROW(),"")
Copy E2:F2 down to cover the max expected extent of source data,
say down to F200?
Then in Sheet2,
you could extract all the "yes" cases by pointing it to Sheet1's col E
In A2:
=IF(ROWS($1:1)COUNT(Sheet1!$E:$E),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!$E:$E,ROWS($1:1))))
Copy A2 to C2, fill down to C200 (same extent)
And in Sheet3,
you could extract the "remainder" cases (except "yes")
by pointing it to Sheet1's col F
In A2:
=IF(ROWS($1:1)COUNT(Sheet1!$F:$F),"",INDEX(Sheet1 !A:A,SMALL(Sheet1!$F:$F,ROWS($1:1))))
Copy A2 to C2, fill down to C200 (same extent)
Sheet2 & Sheet3 will give you the 2 positions that you seek which is dynamic
to Sheet1's source data as it changes in the key col D.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"prem" wrote:
I have 2 sheets now, Sheet1 and Sheet2. In both sheets, my headers for the
data are exactly the same. Only thing is, in sheet1, I have an extra column
of data labelled "proessed?".
Lets assume that I have 4 columns in sheet1, "name", "sex", "location" and
the extra "processed?" column.
In sheet2, I have the same first 3 column as sheet1, only without the
"processed?" coulmn.
What I need is a way to fetch the data from sheet1 to the corresponding
column in sheet2 when the "processed?" column contains the word "yes". I
believe I already have created a similar thread in which Max gave a good
reply.
However, now I need the data from sheet1 to be deleted when it is sent over
to sheet2. Is there a way to do this?