under certain criteria copy data.

[dave]

Hi,

This has got me and I need to ask the greaty and wise for help again.

Is it possible and if so what is the formula to:

Copy data from one work sheet to another if box has certain criteria.

If a column in one work sheet has any entry in it, example Page 2 column D,
I would like to Copy (link) a specific row of data in Page 2 column A to C
into another work sheet ie. Page 1 column A to C.

Hope this makes sense.

Dave

 

[Guest]

formulas return a value, they can not perform an action
like copy.

 

[Max]

Perhaps you're looking for this kind of set-up ..

Assume you have

In sheet: Page 2
----------------------
Cols A to D, data from row2 down

SN Field1 Field2 Field3
1 Data11 Data22 Criteria1
2 Data12 Data23 Criteria2
3 Data13 Data24 Criteria3
4 Data14 Data25 Criteria3
5 Data15 Data26 Criteria1
6 Data16 Data27 Criteria3
7 Data17 Data28 Criteria2
8 Data18 Data29 Criteria1
9 Data19 Data30 Criteria3
etc

where the "criteria" is specified in col D

Use an empty col to the right of the table, say col F

Put in F1: ='Page 1'!A1

Put in F2: =IF(D2="","",IF(D2=$F$1,ROW(),""))
Copy F2 down by as many rows as data is expected
in the table, say down to D1000?

In sheet: Page 1
----------------------
Input a "criteria" into A1: Criteria1 (say)

Copy and paste the col labels over from Page 2
into A22 : SN Field1 Field2 Field3

Put in A3:

=IF(ISERROR(MATCH(SMALL('Page 2'!$F:$F,ROW(A1)),'Page
2'!$F:$F,0)),"",OFFSET('Page 2'!$A$1,MATCH(SMALL('Page
2'!$F:$F,ROW(A1)),'Page 2'!$F:$F,0)-1,COLUMN(A1)-1))

Copy A3 across to D3, then fill down
by as many rows as was done for col F in: Page 2
viz. down to D1000

For the sample data in Page 2
you'll get the display in Page 1:

Criteria1
SN Field1 Field2 Field3
1 Data11 Data22 Criteria1
5 Data15 Data26 Criteria1
8 Data18 Data29 Criteria1

Try change the input criteria in A1 to: Criteria2
you'll get:

Criteria2
SN Field1 Field2 Field3
2 Data12 Data23 Criteria2
7 Data17 Data28 Criteria2

And so on ..