Need help with a formula to pull info from other excel files PLEASE!

Copper Contributor

I apologize but, I am far from any novice user of Excel. Basically what I'm trying to accomplish. Is we have multiple projects that we do. That we need to verify sewer lines. Our first attempt to do so is by accessing the sewer from the street. If we are not able to complete it that way we need to make contact with home owner to schedule and appt. If we are not able to arrange an appt. We need to attempt four points of contact. A day and night phone call. As well as a day and night knock. Which is what I'm trying to accomplish with better tracking.

 

I can very easily get things to work for the total lines and remaining lines. But the issue I'm running into is trying to figure out how to determine the number of contact we need for each given project. Basically how we do things is. When we get a project there is no cell color fills. Other then olive which mean that address was already on another project. If we clear from the street we don't use any cell fill. If it is failed and we need to make an appt/points of contact. We change the line yellow. Once an appt is made we change the comment box green.

 

If we have an appt we are not concerned about making further points of contact. It's the ones we don't have verified and have no appts that we need to track. Any help with this would be greatly appreciated. I can't imagine it would be too difficult to set up. I have attached files so you can see what I'm trying to go for. 

0 Replies