Saving data after using formulas to automatically transfer rows based on data validation

I have an issue with the attached spreadsheet. This might be a universal issue with no work around.

I have some pretty cool INDEX MATCH automatic data transfer formulas that allow me to enter info on one tab while automatically populating another. My problem is that when I enter information specific to that entry in the second tab, if I were to change the original data designation, I lose all the new data I entered.

In the attached file; If I change the "Prospect" in Row 10 of the Contacts Tab to "Counseling", his information will automatically populate in the Counseling tab but I will lose the information in Column Q that was specific to the Prospect Tab.

I think the formula I have in here for automatically transferring data is amazing - I just can't seem to figure out how to save new data (like Column Q) so that it transfers independent of my ROW/MATCH/INDEX formulas. Sounds complicated but you can easily see it illustrated in the attachment.

Hi,

This could be just my over-modest Englishness, but perhaps describing your own formulas as 'amazing' will not exactly endear you to the majority of readers of this forum, some of whom have no doubt produced formulas far more worthy of that accolade without the merest hint of self-congratulation.

Still, as to your problem, I have to say that I'm a little confused on first inspection. Each of your three tabs has a Family Size column. In the Prospects tab the entries in this column are static values. The entries in the corresponding column in the Contacts tab look up to these static values in the Prospects tab, which is fine.

What I don't understand is then why the entries in the Family Size column in the Counseling tab aren't also derived from these static values, but instead come via the circuitous route of looking-up the Family Size in the Contacts tab, which in turn looks up the Family Size in the Prospects tab(?) How is this different from pulling the data directly from the Prospects tab?

Regards