Problem with stacks and runtime failures

[JimDandy]

Attached is a workbook that I’ve been trying to get working. I’ve used a couple of the great ideas presented in this forum, and individually they’ve worked but when I combine them together portions stop working altogether and at other times I receive stack errors and “Method 'formula' of object 'range' failed” runtime errors.

Essentially, there are three worksheets (Sheet1, 4 and 8) that share the data in the top 6 rows. If you change any of those fields while in any of those 3 sheets, the data in the others changes to match, so the appearance is that the top 6 rows are the same from sheet to sheet. Sheet1 is the source (for lack of a better term) as the other 2 sheets will update it when they are modified, and their cell’s data is then set back to read from Sheet1. This does not always work, and sometimes one sheet updates but the other does not. It’s also been when changing data in these top 6 rows that the errors occur. The problems are most notable whenever you modify data in the top of Sheet4.

I’d also like to modify the tab names but this also seems to cause issues with the VBA code (Sheet1=Property Description, Sheet8=Financing, Sheet4=APOD). Sheet2 is a hidden sheet that is used to help with the address field State.