Compile List

[Ka2Dueck]

Hi everyone,

I created a workbook that records all the deficiencies that are in one building. There are currently 100 units and each unit has its list of deficiencies that need to be completed by the appropriate trade. I would like to create a separate worksheet for each trade that compiles all the deficiencies they have in a list with the corresponding unit #. I also want that it would be sensitive to any changes made in the individual unit worksheets. I have attached an Excel workbook to show what I would like it to look like. I hope I am making sense here and if anyone could help that would be greatly appreciated.

 

[Micron]

Probably not what you want to read but this is a job for a database. If you're not experienced with Access then you might not be up to the challenge of its learning curve. I think this is the major point that makes Excel unsuitable for this

I also want that it would be sensitive to any changes made in the individual unit worksheets.

 

[Ka2Dueck]

Probably not what you want to read but this is a job for a database. If you're not experienced with Access then you might not be up to the challenge of its learning curve. I think this is the major point that makes Excel unsuitable for this

Thank you for responding. I was hoping it would be an easy solution but makes sense that it wouldn't. Thank you

 

[Micron]

Based on your post, theoretically you could do it all in one db table but then you'd have to manually re-type things over and over again (such as "electrician" for trades). To get around that and still have only one table would require you to use lookup fields which most experienced developers won't use. If you decide to go the db route you could start a thread in the Access forum where you'd outline your requirements. Be prepare for some questions from responders because there are usually many variables that are not known but need to be.

One thing you might consider either way is to base the project on jobs, not deficiencies. Jobs might be something you'd want to relate cost/budgeting data to as opposed to doing it at the deficiency/task level. F'rinstance, you might give a plumber 3 tasks for one unit (replace kitchen faucet, fix leaking bathroom sink, fix leaking toilet) and relate cost info to the job, not every task performed. However, that would require another table for jobs as well as for deficiencies/tasks. It might seem better to compartmentalize things at first, but splitting the elements can make your db more flexible. The trick is to not overdo that.