Ever seen people use spreadsheets where they click on a cell and there's a drop down menu shown for them to select a value? It's a useful technique to make sure data is entered accurately into a spreadsheet.
Want to know how to do it? Read on! Here's how it looks when it's done:
As an example to show how it's done, I'm going to create a spreadsheet to log my fruit consumption. I create a new worksbook called "fruit_logger.xlsx" and create two worksheets: "Fruit Worksheet" and "Lookup". See this article for guidance on worksbooks, worksheets and renaming tabs.
On my Lookup sheet I add all the fruits I'm ever likely to eat in a single column:
I then go to my Fruit Worksheet and select cell A1. I select the "Data" tab at the top of the screen, then in the Data Tools group I select the "Data Validation" icon, the one with a green tick and a no entry sign on it. It's circled on the diagram below:
After clicking Data Validation the form shown below appears. Under "Allow:" select "List". Then click the up arrow button to the right of the box under where it says "Source". It's circled on the diagram below:
A small box with "Data Validation" at the top appears. Click on the "Lookup" worksheet, click on cell A1, keep the mouse button clicked and drag down until you've got all your fruit selected. The data validation form now looks like the image below. The "Source:" value of =Lookup!$A$1:$A$7 simply means, "look at cells A1 through to A7 on the worksheet called 'Lookup".
Click OK and you're back to your Fruit Worksheet. Click into cell A1 and you can see the drop down list of fruits!
If you type anything that's not on the list into the cell you get this snazzy error message:
To have this drop down in more cells, simply copy cell A1 and paste it into the other cells where you want the list!
Comment below if this worked OK for you!
No comments:
Post a Comment