Forum Discussion
Mac Excel 16.9 data validation drop down list
For what it's worth, your image is NOT showing a data validation drop down list; it's the list that appears when you try to Sort based on the column heading. Given the inconsistencies in that list that shows, the spaces, I would think it would be very beneficial to actually have a good Data Validation list.
That is aside from wanting to add Thailand to the list of valid entries, but it's another issue altogether.
An image, sadly, is not anywhere near as effective as the actual worksheet when soliciting help, so may I ask you to post a copy of the actual workbook. Or a reasonable facsimile--after taking out the names of any real people or other confidential info. Then I or somebody else can help with more specific recommendations.
Thank you for your prompt reply, attached excel file, in summary not sure where to add/amend dropdown list for example - country of origin. Have checked data validation (list) field and it appears empty.
You actually did not have a data validation table for the column on Country of Origin. Nor was it set up as a table.
So I've done both...Sheet 2 now contains a list that is referenced in the Data Validation for column F. And I've turned the array of information on the "Nitrile" sheet into a table, so that as new rows get added, the data validation for column F continues.
If you have the most recent version of Excel, then we could make the Data Validation table one that grows dynamically as new countries get added. You'd need to be able to make the functions UNIQUE and FILTER work...so a way to test whether you have that is simply to start typing in an empty cell, =UNIQUE(..... and see if it lets you complete it.
If it does work, come back and I'll create that data validation list so it expands as you add new legitimate entries.
Thank you ever so much for your assistance and time.
You actually did not have a data validation table for the column on Country of Origin. Nor was it set up as a table.
/* ok, that is what was throwing me off, i thought there was a dropdown in the original file with some values, i understand there was a dropdown column but with no values.
When you set up as a table, I assume you mean as you have now set up a table on sheet 2 with values
So I've done both...Sheet 2 now contains a list that is referenced in the Data Validation for column F. And I've turned the array of information on the "Nitrile" sheet into a table, so that as new rows get added, the data validation for column F continues.
/* i have now added a dropdown list for column c also, however when I add a new row it does not provide the same data validation as you have managed for column F, not sure what I need to do to allow that.
If you have the most recent version of Excel, then we could make the Data Validation table one that grows dynamically as new countries get added. You'd need to be able to make the functions UNIQUE and FILTER work...so a way to test whether you have that is simply to start typing in an empty cell, =UNIQUE(..... and see if it lets you complete it.
/* UNIQUE function is available cell G8- I have provided the latest file. Thank you so much.
If it does work, come back and I'll create that data validation list so it expands as you add new legitimate entries.
You said at the start of your very first post here that you are a new user who has inherited this spreadsheet. It is becoming clear that this is the case...
I also think (very candidly) that whoever you inherited this spreadsheet from was also not really adept at Excel--it bears the marks of a person trying to use Excel as a fancy way to keep notes, but basically designed as if it's a giant sheet of ledger paper (rows and columns) with space to write in a somewhat organized fashion. "Somewhat organized" because too many of the cells contain multi-faceted entries; for example:
This sort of entry would make it difficult to actually use Excel for any of the manifold ways that it can summarize complex data. I'm not knowledgeable enough about the field you're working in to give advice on what exactly should be done with cells like that, but suffice it to say that something like "FOB, CIF Sea, CIF Air" isn't a clear single answer to anything; it's notes on possibilities or notes on alternatives, things to be examined. At least that's what it looks like. And the whole spreadsheet looks like that. You can print it off as a neatly organized set of notes, but don't expect to use Excel to do anything other than enabling you to fix a typo and re-print. If the list gets long enough, you could filter to get only entries pertaining to Malaysia.... but you'd be missing out on a lot of other benefits if this were organized as a really well-designed Table.
So I have added a feature that will extend the data validation lists automatically (making use of the SORT function). You'll see how to do that on the "tables" sheet. When you add a country, or a type, add them to the blue shaded tables and you'll see that the new names get added in real-time, and the list re-sorted into alphabetical order; the new updated list also immediately appears in the data validation on your "Nitrile" sheet.
But all that having been said, I'd highly recommend that you set this task aside for a time and get a basic Excel education, making sure that it includes a working understanding of Tables. Here's an example of what you can find on-line on that subject: https://exceljet.net/excel-tables
And another: https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
There are also lots of videos available on YouTube.