Post info from one spreadsheet in another

[Bideshi Jim]

I work in CPS and my supervisor has tasked me to take data from a spreadsheet he created and have it populate in a spreadsheet I created. His spreadsheet (Intake) tracks the worker who received the referral, the name on the referral, the number of the referral, the date the referral was received, the priority of the referral, and the county the referral was in. My spreadsheet (Tracking) tracks the referral number, the name on the referral, the worker who received the referral, the date the referral was received, and due dates. My thoughts are to enter the referral number in Tracking and have it auto populate the name on the referral, the worker's name, and the date the referral was received. I cannot figure out how to have this happen. I have attached a sample workbook. Any help will be greatly appreciated!


District Referral Log .xlsm

[p24leclerc]

try this macro in your Tracking sheet:

Please Login or Register  to view this content.

[Bideshi Jim]

That worked wonderfully Pierre!!! Thanks so much. My only issue now is if someone enters the wrong number in the Referral Number cell and then delete the number it seems to "break" the whole thing. In other words it appears the script no longer works if they delete the referral number in Intake. Is there something I can do about that?

[p24leclerc]

Sorry about that. I should have doone it right the first time. Here you go:

Please Login or Register  to view this content.

The difference is that now we look if we found something. If not, we do nothing. This means that if no data is copied, you got a wrong referral number.

[Bideshi Jim]

01 Tracking before deletion.jpgI'm still having some problems with an error after deleting a referral number in tracking. I don't mean to be a bother, but the people who will be working the spreadsheet are less savvy with spreadsheets than I am (and you can tell how savvy I am!!!). I have attached three pictures showing the spreadsheet before I delete the referral number, the error I get when I delete the referral number, and the debugging screen. Thank you so much for all of your help!!!!

03 Debugging screen.JPG01 Tracking before deletion.jpg02 Tracking error after deleting referral number.JPG

[p24leclerc]

I think I understand your point and it is a very good one.
I thought it would be a good thing to erase all copied data if you delete a referral. What do you think?
here the modified macro:

Please Login or Register  to view this content.

[Bideshi Jim]

That worked perfectly!!! I especially love how it deletes the line when you delete the referral number. Thank you, thank you, thank you!!!