copy information

[vcff]

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.

 

[pgarcia]

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

 

[Max]

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,INDIRECT("'"&B$1&"'!A:A"),0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik

 

[vcff]

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

Did I miss out something?

vcff

 

[vcff]

OK I had found my mistake nad now it works.

Thanks for the help, Max.

Have a nice day.

vcff

 

[Max]

Welcome. Glad you got it working.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik