Forum Discussion
Excel Drop Down Delections
I am not a macro person but am asking a question that may help them as well: could you back up and explain what the context is here? What kind of data are you dealing with in the first place? What's the basic input and output, the "business purpose" for all of the copying and pasting?
I ask because this sounds--even though you are using a macro--as if it's a heavily "manual" process, causing me to wonder if it might be possible to re-think the entire design. But in order to even address that possibility, we'd need to know what that big picture is.
[And, of course, you're entirely free to ignore these questions. Just understand, if we were sitting down face-to-face, and if I did work with macros, I still would be asking those questions first.]
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.
