I had a master worksheet with a long list of clients to monitor their payment
received (Jan to Dec).

clients name Jan Feb Mar
Peter
John
Albert

At the end of the month, I will received a list of clients who had made
payment for the particular month. How can I transfer this info into the
master worksheet. I cannot just copy as not all clinets made payment for that
month.

I would fill in the blank cell with "0" from the clieints list and then paste
and copy.

"vcff" wrote:

I had a master worksheet with a long list of clients to monitor their payment
received (Jan to Dec).

clients name Jan Feb Mar
Peter
John
Albert

At the end of the month, I will received a list of clients who had made
payment for the particular month. How can I transfer this info into the
master worksheet. I cannot just copy as not all clinets made payment for that
month.

Presuming the 12 "month" sheets are named: Jan, Feb, etc
with client names in col A, payment amounts in col B
(only client names who paid up will appear, others will not)

In your Master,
in B1 across are the month headers: Jan, Feb, etc
in A2 down is the full listing of client names
(all names are presumed unique, order is immaterial)

If you just want to check and indicate client names which appear in the 12
"month" sheets (ie those who paid up), place this in B2:
=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A2),"Paid", "")
Copy across & fill down to populate

If you want to extract the amounts paid up (in col B in the source sheets),
place this instead in B2:
=IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))," ",INDEX(INDIRECT("'"&B$1&"'!B:B"),MATCH($A2,INDIRE CT("'"&B$1&"'!A:A"),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"vcff" wrote:
I had a master worksheet with a long list of clients to monitor their payment
received (Jan to Dec).

clients name Jan Feb Mar
Peter
John
Albert

At the end of the month, I will received a list of clients who had made
payment for the particular month. How can I transfer this info into the
master worksheet. I cannot just copy as not all clients made payment for that
month.

Must the master N month worksheet in the same file?
I tried but the result is zero "0"

Did I miss out something?

vcff

"Max" wrote:

Presuming the 12 "month" sheets are named: Jan, Feb, etc
with client names in col A, payment amounts in col B
(only client names who paid up will appear, others will not)

In your Master,
in B1 across are the month headers: Jan, Feb, etc
in A2 down is the full listing of client names
(all names are presumed unique, order is immaterial)

If you just want to check and indicate client names which appear in the 12
"month" sheets (ie those who paid up), place this in B2:
=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A2),"Paid", "")
Copy across & fill down to populate

If you want to extract the amounts paid up (in col B in the source sheets),
place this instead in B2:
=IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))," ",INDEX(INDIRECT("'"&B$1&"'!B:B"),MATCH($A2,INDIRE CT("'"&B$1&"'!A:A"),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"vcff" wrote:
I had a master worksheet with a long list of clients to monitor their payment
received (Jan to Dec).

clients name Jan Feb Mar
Peter
John
Albert

At the end of the month, I will received a list of clients who had made
payment for the particular month. How can I transfer this info into the
master worksheet. I cannot just copy as not all clients made payment for that
month.

OK I had found my mistake nad now it works.

Thanks for the help, Max.

Have a nice day.

vcff

"Max" wrote:

Presuming the 12 "month" sheets are named: Jan, Feb, etc
with client names in col A, payment amounts in col B
(only client names who paid up will appear, others will not)

In your Master,
in B1 across are the month headers: Jan, Feb, etc
in A2 down is the full listing of client names
(all names are presumed unique, order is immaterial)

If you just want to check and indicate client names which appear in the 12
"month" sheets (ie those who paid up), place this in B2:
=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A2),"Paid", "")
Copy across & fill down to populate

If you want to extract the amounts paid up (in col B in the source sheets),
place this instead in B2:
=IF(ISNA(MATCH($A2,INDIRECT("'"&B$1&"'!A:A"),0))," ",INDEX(INDIRECT("'"&B$1&"'!B:B"),MATCH($A2,INDIRE CT("'"&B$1&"'!A:A"),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"vcff" wrote:
I had a master worksheet with a long list of clients to monitor their payment
received (Jan to Dec).

clients name Jan Feb Mar
Peter
John
Albert

At the end of the month, I will received a list of clients who had made
payment for the particular month. How can I transfer this info into the
master worksheet. I cannot just copy as not all clients made payment for that
month.

Welcome. Glad you got it working.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"vcff" wrote in message
...
OK I had found my mistake and now it works.
Thanks for the help, Max.
Have a nice day.
vcff