Excel- Help transfering dropdown lists to new sheet

khilla579 

You refer to an estimate sheet in the singular, so we have to assume there is only one, and that it is "cleared" to start the next project's estimate.

And I'm going to assume there is nothing special in the structure of the estimate or project-specific sheets; e.g., all of the selected materials are in one column and all the material quantities are calculated into/entered into a different single column on the same rows.

FYI, while dropdown lists on the estimate sheet will be useful in selecting material (avoiding spelling errors, extraneous spaces, and inconsistent capitalization), their use is entirely irrelevant to any "auto populate" feature.

The "auto populate" can occur in these ways:

• You have a (replenishable) supply of project-specific sheets, each of which contains formulas that pull data from the estimate sheet. (Excel formulas are good at pulling data.) But since you need the values to become fixed once the estimate is complete, some other action (like a manual copy + Paste Values on one of the project-specific sheets) is needed at completion. (Changes thereafter are not automatically reflected.)
• You expect to have some procedure (macro) that copies the values to a new/existing project-specific sheet, and you execute that procedure when the estimate is complete. (Excel formulas are incapable of pushing data to locations other than adjacent cells.) That's going to require VBA*, written to handle your specific data structures. We might write such code, if we know the data structure (which specific columns, and what the range of rows can be), or we might not. But introducing VBA also means introducing security issues (e.g., can I trust the provided code, and can I trust everyone who will ever be able to edit the workbook that contains the procedure?).
• Magic. Or at least less work than simple copy + Paste Values operations. But after many years, this is still under development.

* Office Script might be able to do this work, but I have no specific knowledge of it.

So are you not revealing some complexity to us? Say, your estimate sheet can calculate zero quantities for some categories of material, and you don't want the zero-quantity materials (or embedded blank rows) to be copied? Or you need values to be placed into specific cells, depending on…something else you have not revealed?