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

The functions LOOKUP or VLOOKUP will do this for you.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45411

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,SM ALL($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,SM ALL($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
---
"prem" wrote:
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.

"JBeaucaire"
The functions LOOKUP or VLOOKUP will do this for you.
Not in the OP's instance here. Believe s/he's looking for multiple line
returns (where "GIRO" appears in the payment column)
--
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


"Max" wrote:

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,SM ALL($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,SM ALL($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
---
"prem" wrote:
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.

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

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

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
---
"prem" wrote:
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

Thank you Max.

It works perfectly this time.
Really appreciate your help.

Regards,
Prem

"Max" wrote:

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

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

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
---
"prem" wrote:
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

Hey Max,

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

Regards,
Prem Ananthan

"Max" wrote:

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

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

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
---
"prem" wrote:
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

Marvellous to hear you got it going.
You're welcome, and thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:65
xdemechanik
---
"prem" wrote in message
...
Hey Max,

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

Regards,
Prem Ananthan