Forum Discussion
Excel Drop Down Delections
I have 70 drop down options. I use a macro to copy/paste below the drop down list from 10 specific separate columns to the column I am in, the copied column depends which (70 options) is selected...
mathetes , sure.
It is an "Equipment Selection" application. 25 criteria that is macro-copied beneath the drop down selection of which equipment you choose, and then it calculates/counts all the items that qualify with those criteria.
So I have 70 pieces of equipment, you pick one, it loads the criteria (copy/paste) and shows how many items fit in the specific chosen equipment.
I have 10 drop downs in 1o different columns that all select from the same data to load the criteria. Because I am pasting below the drop down in each drop-down column, the paste instruction has the specific address of that column. So there are 70 instructions for each column in the macro to paste to the specific column, and since there are 10 columns, that makes 700 macro instructions.
I would think there is a way to copy from the equipment data just as I do now, but paste in a relative column of the drop down I am in and NOT have to give the specific column's address.
I am having difficulty picturing what you're describing. If this doesn't contain confidential or proprietary information, it would help a LOT if you were to post a copy of the spreadsheet on OneDrive or GoogleDrive and then paste a link here that grants us edit access to that file.
- mathetes it is proprietary.
- i am trying to paste a copied range in the column i am in, without the maco needing the start of the paste range. Maybe say “ ‘x’5 “. Not “G5” eg.” Simplest i can say it. Thanks for trying!
Can you create a mockup, maybe slightly less ambitious or complex, but complete enough to demonstrate the challenge and what is desired? And then post the mockup as suggested above...
In trying to picture what you are facing, it does occur to me that possibly the FILTER function could serve your needs, in that it is what's called a "dynamic array" function; i.e., it delivers multiple rows (and/or columns) that meet specified criteria. Here's a link to a very helpful YouTube video created by Microsoft to introduce that function, around two years ago. It does require a current version of Excel.
- mathetes it is proprietary.
