MS Excel 2000 - Can I put a Combo box into a cell?

  Ben Avery 17:29 09 Aug 2003
Locked

G'day

I have a friend who works in Pharmacautials and he uses Excel to calculate profit margins on medicines etc.

What he would relly like to do is have a combo box inserted as a cell, the drop-down values of which correspond to the fixed prices of the medicines. See the example below to see what he'd like to accomplish:

Let's say I have 3 cells.

*In cell "A1" I input the cost of a medicine - lets say "£3.40"

*In cell "B1" I have the profit margin percentage at lets say "35" (No % sign)

*In cell "C1" I use a basic formula like mabe "=SUM(A1/100*(B1+100))"

When I enter the figures, the correct figure of £3.40 + 35% comes out at £4.59 and evertime I change the price or the margin the total price will change accordingly.

What I would ideally like to know is, could I change the "Margin" cell and/or the "Price" cell to a combo box with say multiples of 5% or something and input a formula into cell "C3" which will then work from these figures?

If so, how???? I know it involves using the combo box on the ActiveX Controls Toolbox and may involve writing in Visual basic - both of which I am unfamiliar with. I have used Combo boxes extensively in MS Access but that's different!

Any help would be appreciated!

Cheers

BA

  VoG II 17:41 09 Aug 2003

You can do this without needing VBA.

Enter your %margin figures in a range of cells. I normally use white text for these and protect them against being over-written.

On an Excel Sheet, View/Toolbars/Forms, Select Combo box and place it where you want on the sheet.

Right click the box, Format Control.

In "Input range" enter the range where your %s are.

In cell link enter the cell address where you want "the answer" - I normally use the cell under the box, so it is hidden.

Note that "the answer" is actually the index number in the list, not the % (in this case). If say your list of %s is 5, 10, 15 then the % would be given by "the answer" * 5%. Alternatively you could use an offset - say your % list starts in A1, A2 etc and your "answer" is in C3 then

=OFFSET(A1, answer-1,0) will give thye % value.

  Ben Avery 23:52 09 Aug 2003

I knew it would be you who would reply - I think you must be the "whizz" taht so many threads speak about! OK, I've checked exactly what he wants to do but have only had partial success myself. Here goes:

************************************************

The combo box will contain the drug names. I used sheet 2, column A to input the names into a list.

In column B of the same sheet (Sheet 2) I have inputted the price of each drug - Sheet 2 e.g.:

Cell A1 = "Drug A", Cell B1 = £1.00

Cell A2 = "Drug B", Cell B2 = £2.00 etc. etc.

************************************************

Now, on sheet 1 the list of "Sheet 2 Column A" is ready and whichever drug I select, the cell number reference (list reference) appears in cell A1 (covered by the Combo box). How can I now set it so that, if I select Drug J (list number 10) the next cell (Sheet 1 Cell B1) contains the relevant price which in this case would be Sheet2!B10??

Thank you!

BA

  VoG II 09:32 10 Aug 2003

In B1 on Sheet1 enter the formula

=OFFSET(Sheet2!A1,A1-1,1)

  Ben Avery 18:01 10 Aug 2003

Thanks for the info. You really are the Excel whizz!

Could you explain the formula above to me as I tend to find that if I understand HOW it works, then I'll retain the info for the future?

Thank you so much! ;o)

BA

  VoG II 18:16 10 Aug 2003

The function =OFFSET("cell", "rows", "columns") returns the value "rows" down and "columns" across from "cell".

Setting "cell" as Sheet2!A1 points to the top leftmost cell in the list of items and prices.

Setting "rows" as A1-1 says e.g. for the third item (when Sheet1!A1=3) look down 2 rows from the top of the list on Sheet2.

Setting "columns" as 1 says to look one column to the right; i.e. Column B on Sheet2.

  Ben Avery 18:32 10 Aug 2003

You're a gem, thank you! So assuming the price list was to the LEFT of the Drug List I guess the formula would become:

=OFFSET(Sheet2!A1,A1-1,-1)

If so I think I actually understand that!

:o)

BA

  VoG II 18:40 10 Aug 2003

Yes, you've got it.

  Ben Avery 22:31 10 Aug 2003

You obviously spend a lot of time using excel in whatever your line of work is, and I'd like to thank you for imparting some of this knowledge to us on the forum. You are a real help to many here, myself included. Thank you so much!

Do you know of any good sites for learning a little more about Excel functions? I looked at the list available in Excel's help menu and was shocked at just how many there are! I have only ever used "SUM" and "IF" with some of the Finance Dept in our Head Office using "ROUND".

If you could point me in the right direction for learing it'd be great - or any good books worth buying, like "Excel for Dummies" etc?

BA Thank you again!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Android Oreo News: Release Date & New Features

11 must-have iMessage stickers packs by Jean Jullian, Christoph Niemann, Jon Burgerman & more

iMac 21.5-inch (2017) review

Que faire en cas de vol ou perte de votre smartphone ?