If you are creating or modifying an Invoice Form in Excel, you will want to add a Combo Box Control to allow users to select products from a listing. The key in formatting your Combo Box Control is to choose the correct cell to contain the “Cell Link.”
Cell Link in Form Control
I think that you will benefit from “seeing how this is done” in this video lesson. I know that I always struggled with “reading about” Excel Form Controls. Once you see how important the “Cell Link” placement is, you will better understand how Combo Box Controls work.
Use INDEX Function
Once we have formatted the Combo Box, we need to be able to look up other values to place on our Invoice. In this example I demonstrate how to use the INDEX() Function to lookup the “Unit Price” for each product selected from the Combo Box list on the Invoice. As a best practice, I recommend that you use “Named Ranges” for the “array” that you Index. The INDEX() Function has three arguments:
- The ARRAY to Index – In this case our “named range” with three fields (Product Name, Unit Price, Cell Link)
- The ROW reference – In this case the cell in the ARRAY that contains our CELL LINK for the Combo Box
- Optionally, the COLUMN reference – in this case “2” for the 2nd Column in the ARRAY (Unit Price)
Let me know if my videos in this series have helped you to understand how to use Form Controls in Excel. It took me some time to figure out how they worked; I hope that I can save you some time and ease your frustration in apply them to your forms. Add your comments below or send me an email – danny@thecompanyrocks.com
Find Additional Videos for Form Controls in Excel
Form Controls include Option Buttons, Spinners, List Boxes and more. Here is a link to the other videos in my series on Form Controls in Excel.
Watch Tutorial on YouTube
If you prefer, follow this link so that you can watch this video on my YouTube Channel – DannyRocksExcels
Resources Available at My Online Shopping Site
I invite you to visit my new, secure online shopping site where you can Learn how you to “Master Excel in Minutes – Not Months!
How to Add a Combo Box Control to an Invoice Form in Excel [ 8:01 ] Play Now | Play in Popup | Download (1645)
Thanks! this was a great help – I’d forgotten this workflow.
Thank you for adding your comment Catherine. I am pleased that I could help you with this tutorial.
Danny Rocks
The Company Rocks
the video is very useful.Thanks danny
But I have aquery. Is there anyway thtat we can change the cell ink auomatically when we copy the input range?
Please advise.
thanks again
Hello Rajesh –
I am pleased that you enjoyed this tutorial.
As you have learned, the “key” to making Form Controls work correctly is the “Cell Link.” This becomes an “Absolute Cell Reference” which means that it is Fixed in place.
The other “key” – in my opinion – is to use “Named Range”References for your Array and, in this lesson the Unit Price. Since these “Key Referencess” are Fixed In-place, you should have no problem in copying the input range – which I interpret as the Invoice to a new location.
Danny Rocks
The Company Rocks
Serialno Employees
1 Abdan
2 Aguilar
3 Ahamed
4 Ajay
5 Al Salimi
I have “Employees”in combobox as Input Range and Serial No as cell link. . But while copying combo box to another cell and selecting another name , the first combo box is also getting changed
pls. provide me a soloution to this
Hello Rajesh –
Thank you for sending me your example. When you copy the Combo Box from – e.g. Cell B2 to Be, you must go in and Edit the Combo Box to change the CELL REFERENCE. I recommend that you use a “Named Range” – e.g. “Employees” for your Input Range. I also recommend that you create an additional Column, titled Cell Link – and that you make reference to the cells in this new column for your Cell Link. So, now, let’s say that in your first combo Box, your input range is the Named Range “Employees” and your Cell link is $C$2 (The 1st Cell in the New Column for the Field “Cell Link.”) Now, when you copy this first Combo Box down one row, you MUST edit the Cell Link to be – in this case $C$3.
Does this make sense to you?
I have decided to film a tutorial that will demonstrate this. Watch for it to be posted on my website and on YouTube a little later this week.
Danny Rocks
The Company Rocks
Thank you
Thank you very much for your valuable comments
Ny pleasure. I am happy that I could help you with this.
Danny Rocks
The Company Rocks
one more doubt on this . May be silly.. Do I have to mannualy edit cell link for all entries in input rage if I have more than 700 to 800 entries? pls help
Rajesh –
No question is EVER “silly” – so please ask them,
The short answer to your question is that, “YES, each reference must be changed to reflect its position in the Combo Box.”
The longer answer is that – You can “automate this process buy using a VBA – Visual Basic for Applications Macro to accomplish this task.”
However, I would most likely propose steppping back and analyzing what it is that you want to accomplish. Perhaps, in this case, a different program – e.g. MS Access – might be better suited to collect this input information. Collect the information in Access and THEN, export the data to Excel for analysis.
Danny Rocks
The Company Rocks
thank you very much
It is always my pleasure to help my readers whenever I can.
Danny Rocks
The Company Rocks