fetching certain fields from 1 worksheet to another(if criteira me

  • Thread starter Thread starter prem
  • Start date Start date
P

prem

Hi guys this is the problem I'm having right now.

On sheet 1, I have the headings as follows:
student name NRIC number block number street name payment mode,
where student names are placed under column A, NRIC number under column B
and so on,

On sheet 2, I have the following headings:
account holder name A/C no. student name block number street name,
but this this time, the students name are placed in column C, block number
under column D and so on.

This is what I need:
There are only two payment modes (column O) in sheet 1; full and GIRO. When
the payment mode is GIRO, the fields student name, block number and street
name from sheet one need to be replicated in sheet 2 under the same headings.

How do I go about accomplishing this? Thank you guys for your help.

Regards,
Prem
 
Here's a formulas play to deliver the required results dynamically from
Sheet1 into Sheet2

Source data is in Sheet1 as posted, data from row2 down
with key col = col O (payment mode)

In your Sheet2
Set aside an empty col to the right for the criteria, say col K?
Put in K2: =IF(Sheet1!O2="GIRO",ROW(),"")
Leave K1 empty. Copy K2 down to cover the max expected extent of data in
Sheet1's col O, say, down to K50?

Then to extract "student name" from Sheet1's col A (into col C in Sheet2),
Place this in C2, fill down to C50:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(Sheet1!A:A,SMALL($K:$K,ROWS($1:1))))

Similarly to extract corresponding "block number" & "street name" from
Sheet1's cols C & D (into cols D & E in Sheet2)
Place this in D2:
=IF(ROWS($1:1)>COUNT($K:$K),"",INDEX(Sheet1!C:C,SMALL($K:$K,ROWS($1:1))))
Copy D2 to E2, fill down to E50. All result lines will appear neatly packed
at the top.

P/s: The col to be returned from Sheet1 is defined in this part:
... INDEX(Sheet1!C:C,
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 
Hey Max

Thank you for your prompt reply. However, I seem to have problems with this.

When I put in the =IF(Sheet1!O2="GIRO",ROW(),"") formula into sheet 2, it is
displaying the number 2 for some reason. And when I paste your formulae for
the name fields, block fields and so on, nothing seems to be returned.

Right now, I have only 2 sets of data in Sheet 1

Not sure if this will affect things, but I have formatted my data section as
a table. And when pasted the =IF(Sheet1!O2="GIRO",ROW(),"") formula into
sheet2, I did so in a column outside the table.

What am I doing wrong?

Regards,
Prem
 
When I put in the =IF(Sheet1!O2="GIRO",ROW(),"") formula into sheet 2, it is
displaying the number 2 for some reason.
Click to expand...

Those are arbitrary row nums in the criteria col for source rows which
satisfy the specified criteria. These nums in the criteria col (col K in my
construct) will be read by the other extract formulas.
And when I paste your formulae for the name fields,
block fields and so on, nothing seems to be returned
Click to expand...

You need to amend all the formula points to col K (ie the criteria col given
in my construct) to the correct col in your actuals. If you had set up the
above criteria in say, col X instead, change all the references to col K
within the extract formulas to point instead to col X, ie replace $K:$K with
$X:$X.

Try it again, it should work fine.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:65
xdemechanik
---
 
Thank you Max.

It works perfectly this time.
Really appreciate your help.

Regards,
Prem
 
Hey Max,

Thank you very much. Its working perfectly. Really appreciate the help

Regards,
Prem Ananthan
 
Back
Top