Asking Excel to insert a drop down list based on previously entered text
Example, Say in Cell A1 "Breakfast" is typed in. In A2 I'd like a drop down menu to appear, i.e. with cereal, toast, yoghurt, eggs or bacon to select from.
However, if "Lunch" is entered in A1, a different drop down menu should appear, i.e. sammy, salad, pasta.
And finally, if "Dinner" is entered in A1, yet a different drop down menu should appear, i.e. steak, chicken, tuna, stir fry.
Is this possible and if so, what is the formula to be used?
you are asking excel to do the job of access...try using access for this...excel is merely a place to store data...access is a tool to utilize that datayou are asking excel to do the job of access...try using access for this...excel is merely a place to store data...access is a tool to utilize that data
You can't post conmments that contain an email address.
An expert who has achieved level 2 by getting 100 points
MVP:
An expert that gotÂ 5 achievements.
Novelist:
An expert who has written 50 answers of more than 400 characters.
Scholar:
An expert who has written 20 answers of more than 400 characters.
Expert
108 Answers
Re: Asking Excel to insert a drop down list based on...
Yes...no....yes....and yes. And if that sounds like a funny response let me explain. First of all you can write a formula for a specific cell (or cells or interrelated cells where you menus would display). It takes a while and if you are new to the syntax it could take a couple hours even (let's assume you are not a math major). Using Access, for something this apparently simple, I would not suggest (another learning curve but admittedly a reasonably easy one). But, this is the second yes, the easiest way to do this would be to simply insert a "comment". When you hold the cursor over the cell...your data...i.e. menu...will pop up. And yes...(last yes) you could have Sunday thru Monday across the top and Breakfast, lunch and dinner in the right hand column. Now this assumes what you have said is what your are trying to do...effectively meal planning...and you are not trying to plug in the inventory in the fridge to develop a shopping list.
Lastly...yes I could write the formula....but not knowing what you do (and figuring if you are going to use Excel at all you really do need to learn how to do it) I would be wasting a bit of time. Hope this helps...Tango.
Hi,
a 6ya expert can help you resolve that issue over the phone in a minute or two.
best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
the service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need. goodluck!
- If you need clarification, ask it in the comment box above.
- Better answers use proper spelling and grammar.
- Provide details, support with references or personal experience.
Tell us some more! Your answer needs to include more details to help people.You can't post answers that contain an email address.Please enter a valid email address.The email address entered is already associated to an account.Login to postPlease use English characters only.
Tip: The max point reward for answering a question is 15.
Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.
Formulas used in Excel must follow a certain syntax.
All formulas begin with an equals sign (=).
Some formulas use operands such as +,-, *,/ for addition, subtraction, multiplication or division. For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3.
Other formulas refer to different functions such as SUM, AVERAGE and others. For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3.
Formulas can be combined with operands. For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10.
Functions can be nested within each other. For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.
Ever wish that you didn't have to type out the same thing over
and over again? Well you can save time by using Excel's drop down list!
Drop Down Preparation
Imagine that we are creating an Excel spreadsheet to document
our purchases from the grocery store. For each purchase we want to mark
down the name of the food and the type of the food.
The way a drop down list works in Excel is that it takes
values from some place on your spreadsheet and uses them to populate the
options in the list. Lucky for us, the type of food is a perfect
opportunity to use a drop down list!
In cells A1 through A7 enter the type of food that will
be used for the drop down list.
Creating a Drop Down List
Now that we have a source for our drop down list, we can
create a begin to create our drop down.
1.Select
cells D2 though D20 (We will be creating a drown down list for each cell in
this range)
2.Click
the Data menu and select Validation
3.Choose
List from the Allow drop down menu
4.Click
the Source button that appears to the right of the textfield
5.Select
our source cells A1 though A7
6.Press
Enter and Click OK
Testing Your Drop Down
All of your cells in the Type column should now have a
drop down list. To test to see if your drop down works, click on cell D2
and then click on the downward facing arrow and see if all the types of
food are available.
Use the test function.
Formula in cell A1 would be =text(a2,"dddd").
This would pick up the date from cell A2 and return the day of the week in cell A1.
You can number rows in a column by entering a number in cell A1 (usually the number 1 but youcan start with any number) and the formula (=A1+1) in the next row. The result there will be 2. Copy that formula down the rows you want to number and they will be numbered 3, 4, 5, etc. Each row adds 1 to the previous row so if you do anything that disrupts the sequence (like inserting a row between two others) you will have to copy the formulas down again to restore the sequence.
You can also use the Edit-Fill-... menu command to put a series of numbers into rows. Put the starting number in th efirst row. Highlight it and the rows that you want to number and select Edit-Fill-Series... Those numbers will not change if you insert columns or move the formulas.
Or you can use the formula =ROW(A1) in any cell to return the number of that row. (The result of =ROW(A1) is the number 1 in cell A1, the result of =ROW(A2) is the number 2 in cell B2, etc. In this case inserting rows will not affect the numbering (i.e. row A5 will always be numbered 5 even if the data in it is moved down.)
You need to use the ROUND function. E.g. type: =ROUND(A1,2)*A2
Assuming the 2.131576 is in cell A1 and 1000 is in A2. The round function round the value to 2 decimals (in this case because I specified 2 as the second argument).
Hi, Try selecting a cell and right click, Format cells, Font - Bold the text the way you want it to appear in that cell and then let it be the value you want to appear when you select that cell as a solution to a "What If" statement. I build training spread sheets that let people generate pre-programmed answers to various data entries. Answers appear like magic. Glad to be of assistance - please rate the posting - I can learn from you.
Hello ycool11,
If your asking what i think you are the formula would be
=(a2-a1)*24
That assumes a2 is that last date entered and a1 is the initial starting date. if you would like a more intense formula let me know and i'm sure i can help.
I'm not entirely sure how you want to use this, but it is fairly easy to automatically assign a value to a cell based on the value in another.
Let's assume that you enter your score (A, B, C or D) in cell A1. If you put the following equation in cell A2 it will then display the corresponding score:
you are asking excel to do the job of access...try using access for this...excel is merely a place to store data...access is a tool to utilize that data
×