Create popup field to enter value and have that value part of a formula that populate

[fujnchaqita]

I don't know if this is possible but if it is, that would be awesome! Here's what I want to do. I have a drop-down menu created in excel (let's say in cell A1) with different text selections. One of the selections is "Travel Mileage." If choose "travel mileage" from the drop-down menu, I want to either have a pop-up field that has you enter the number of miles traveled in that pop-up field. If I enter, for example 10.75 miles, I want cell B1 to return the value of 10.75 miles X $0.55 = $5.91. If "travel mileage" is not selected, it doesn't do anything. Is this possible in excel? If so, I want to know if it can go one step further and display the number of miles (the number 10.75) next to the word "travel mileage" so that I know what the person entered into the pop-up field.

Please help!
Thank you!!!!

[rscsmith]

This is VBA territory, and requires a Worksheet_Change macro. This macro fires every time a cell on the worksheet is changed.

Open the VBA Editor. Double click on the sheet you are interested in and paste in the following code:

Please Login or Register  to view this content.

You need to ensure the text exactly matches (capitalisation, etc), but if you're using drop downs this is easy.

Best regards, Rob.

[royUK]

Why would you want VBA to do this? A simple IF function would work

[fujnchaqita]

Rob, that was AWESOME! It worked flawlessly and exactly what I wanted. Thank you times a million!!!

[rscsmith]

Hi Roy

I thought it would be necessary as the request was for a pop up box, a drop down (assumed to be a validation list) is being used, and then the validation needs to be violated to insert the mileage next to "Travel Mileage". There's no way to enter the mileage without introducing another column or the possibility of user error (i.e. relax the validation and people start entering "Travel Mileage 10m", "Travel Mileage10" ...) which becomes hard to read in the IF statement.

Mind you, I'm 10 years out of date using Excel 2003 (damn my archaic company!) There may well be other and better options in 2010 that I'm not aware of.

I agree - it's normally better to avoid VBA and I would be very pleased to see my code become obsolete.

Cheers, Rob.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.