The noise is coming from a faulty fluid pump on the transmission. The pump gear is damaged and rubbing the case of the pump housing and causing this noise. You will need to remove the trans and replace the pump and replace all the seals in the transmission.
And why does it go away when it goes into overdrive?
In the door jam give me the code letter or number under TR in the bottom right of the VCIS label and I will tell you the cost and the cheaper route.
If you keep driving you will fill the transmission with pieces of metal and soon fragments and the transmission will at that time be ruined.
If you decide to purchase a Ford remanufactured transmission thats going to run 2.514$
Remove and replace/install a transmission is 8.5 hours ( my shop rate is 88$ an hour)
If you overhaul the removed trans its 8.2 hours
If you replace the pump and seals and fluid then you will spend 841$
This will help you decide what is the best choice.