PDA

View Full Version : [SOLVED] Dynamic range from sheet 1 as input to combo box on sheet 2



El_Diabolo
11-11-2013, 05:52 PM
Hi all,

This is a resurrection of a question I had three years ago with which I gave up, because despite help and assurances from some very good people, I simply could not get it to work.
I don't propose posting any data or code at this stage as I believe the issue is purely a technical one, which I am clearly too dumb to solve.

Back then I was using Excel 2003,but now use Excel 2010. I believe 2003 may not have supported what I was/am trying to do, as follows:

I have a combo box on Sheet 2 which is validated against data on Sheet 1, BUT what I had to do was copy and paste the Sheet 1 data into Sheet 2 using VBA on Worksheet_Activate.
It all works just fine except that the fly in the ointment is that whenever I create a new data row on Sheet 2, after having used the combo box and populated the other fields on that row, the newly added row causes the pasted Sheet 1 data to move down a row, which beheads the pasted data. This means that the next time I use the combo box the top entry has disappeared. Ad infinitum. So, my question is how do I get my combo box pointed to the data on Sheet 1 so that I can stop the copying/pasting. I have been trying all day to solve it, but just keep going round in circles. This just MUST be so simple, but I cannot see how to do it. I have searched the net and forums and still cannot manage it. Any help would be greatly appreciated, no matter how stupid it may make me look and feel.

Best regards.

mikerickson
11-11-2013, 07:17 PM
In the attached there is a list on Sheet1, column A.
There is a dynamic Named Range, Name: ListRange RefersTo: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A),1)
On Sheet2, there is a ComboBox from the Forms menu with the InputRange of ListRange.
Sheet2!A1 has list validation with the Source =ListRange.

As items are added to the list on Sheet1 (no blank rows please), they will be added to the options for either of those dropdowns.

El_Diabolo
11-13-2013, 09:57 AM
Thank you, Mike for your prompt and helpful reply. I apologise for not getting back sooner - a combination of life and testing. On trying your solution I found that the drop-down list would not accept keyed input (perhaps I should have mentioned that requirement), so I would have to use an ActiveX control(I believe). However, by incorporating the Index function, as provided by you, into my then existing efforts we have solved my problem and it now works fine. Many, many thanks, Mike. Three years on and eventually it works. Whoopee. There is, however, one niggling doubt in my mind, which I perhaps should not raise in this thread, but since you are a Mac Master I will mention it here.(If you think I should post it separately that's fine). My basic requirement here is to give a little help to my wife for work purposes, but she uses a Mac at work, whereas I use a non-Mac laptop, and I'm not sure if ActiveX controls are supported on MAC. Any clarification you could give me would be great(if it's not against the forum rules and regs).
Again, many thanks for your help. Best regards.