Home |
Search |
Today's Posts |
#1
|
|||
|
|||
under certain criteria copy data.
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 |
#2
|
|||
|
|||
formulas return a value, they can not perform an action
like copy. -----Original Message----- 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 . |
#3
|
|||
|
|||
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 A2:D2 : 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 .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "dave" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
How do I copy data (word) into respective cells when the data bei. | New Users to Excel | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel | |||
Copy data into a NEW worksheet | Excel Discussion (Misc queries) | |||
Combo box copy data | Excel Discussion (Misc queries) |