Forum Discussion
Excel- Help transfering dropdown lists to new sheet
I have an excel estimate sheet I made for doing comstruction estimates. My idea is to make drop down lists in the estimate sheet for products needed for the job. Each line item will have a drop down list of the supplies/materials to choose from. I want to take supplies/materials that were chosen from the drop downs and add them to another sheet, supplies/materials sheet. The part I'm not sure how to do, is getting the selected items from each drop down list and putting it into another sheet, and then being able to use that sheet to show all the materials and supplies needed for the job. Any help would be greatly appreciated. Thank you.
- Did you try to copy paste cells having dropdowns?
- That would defeat the whole purpose, as I want it to auto populate to a new sheet every time I fill out a new estimate.
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?
