Populate a table of items based on 1 cell selection

[mattg2448]

Good evening,

I am trying to build a program to assist in bidding that sort of works as a "database" by referencing preloaded tables and auto populating date based on a selection. I am really above my head here, so I may have trouble describing what i am trying to do.

This is my "Template" so far, here is the general idea of what I am shooting to build.

Row 5 would be the row where we enter in the task or bid item, this would have our take off information and total costs of our bid for that specific line item.
The highlighted area is to be in a collapsible group. The intention is to use XLOOKUP and have data tables in the back end with preset values to help speed up the estimation process.

Cell B7 is a drop down menu that will reference a "Crew Name" and i want the column C/D between rows 9 and 20 to populate my "predetermined" equipment and crew cost codes. Some crews have 2 items, some crews have 15 items. I also need room to be able to add/subtract items as needed, or change them based on the needs of the project. Once i get the cost codes into the column i can use XLOOKUP and reference the 2-3 other tables i will need to pull from to complete the information for each other row.

Here's how i set up my table to retrieve cost codes but it seems complex and messy.

The last part about this is I need to be able to copy and paste this entire section so i can build out 10-50 bid items that all have their own specific items need. What is the best way to set this up for work flow?

Thank you!

 

[Edgar_]

You should be entering data to a table, not to a report. Let other tools generate the report based on the items of your table and you will save yourself from a bunch of unnecessary headaches. There are too many things to point out, but construction stuff like this requires database-like design. Take a look at this file:

tasks with breakdown.xlsx

docs.google.com

In this setup, tasks, breakdowns, and breakdown items are organized into dedicated tables. This structure allows for easy data lookup and helps eliminate duplicates. Additionally, it simplifies the creation of tools for your solution, such as data entry forms and reports.
1. Add a task
2. Add items
3. Add breakdowns

For example, in order to add your crews, you would go to items table and add one crew for each item. Then, from the breakdown table, you can enter the key for the crew and that table will look up whatever you assigned in the items table. Things are correctly related this way. Also, you can reuse the items for each new project or use them over and over for the same project in different tasks. Just link the breakdown to a task key and you will be able to query your costs and create many types of reports in a scalable way.