Hi, I'm new to Numbers (But have worked in Excel many years ago).
I have a database with various stuff in it. I want to have an input column with dropdowns that is populated with values (or text) from the database (In a certain order). How can I do that the easiest way?
Many thanks//O
null-OTHER, macOS Sierra (10.12.6)
If I understand you correctly, you could format the cells as Pop-Up Menu. An easy way to set up Pop-Up menus is a certain order is to list the values in cells. Then select those cells, and choose Pop-Up Menu as the data format.
For example, if you want the list to be A, Z, B, T you would do this:
That gives you this in the Pop-Up Menu in each cell:
You can add-subtract and edit the items in the list here in the panel at the right:
Once you have the Pop-Up Menu the way you like it you can copy-paste the cell containing it to wherever you want it in your table. Select it, command-c to copy, select multiple cells in the column where you want it, and command-v to paste.
SG
If I understand you correctly, you could format the cells as Pop-Up Menu. An easy way to set up Pop-Up menus is a certain order is to list the values in cells. Then select those cells, and choose Pop-Up Menu as the data format.
For example, if you want the list to be A, Z, B, T you would do this:
That gives you this in the Pop-Up Menu in each cell:
You can add-subtract and edit the items in the list here in the panel at the right:
Once you have the Pop-Up Menu the way you like it you can copy-paste the cell containing it to wherever you want it in your table. Select it, command-c to copy, select multiple cells in the column where you want it, and command-v to paste.
SG
Yeah, sure. Sorry for being a bit unclear.
So this is the database I am working on:
And this is where you make your choice:
One way or the other, I want the choice in the dropdown to print the names of the columns from the top table (somewhere), or save them in a string, or hide the other names.
Hi Oscar,
You could use a nested IF formula, but that will become convoluted and difficult to diagnose when it gets long.
I suggest the VLOOKUP function to search another table.
Column B in Table 1 has Pop-Up Menus
Formula in Table 1 C2 (and Fill Down)
=IF(B2="Choose a value","",VLOOKUP(B2,Lookup Table::A:B,2,0))
If the Pop-Up Menu shows the first item (Choose a value) then insert "" (NULL).
Else
find a match in the Lookup Table with the formula VLOOKUP(B2,Lookup Table::A:B,2,0)
Regards,
Ian.
Edit: Oscar and SG replied while I was composing my reply. Please ignore if I missed the point. Ian.
If you transpose your data table so it looks like this...
... then you can use the power of filters built into the Numbers interface. To display the companies with 200 outlets all you have to do is this:
Giving you this:
You can easily do more sophisticated filters (>200, <200, etc) in the panel at the right.
Transposing your data to take advantage of filters is easy. Click in your table and choose Table > Transpose Tables and Columns from your menu.
SG
Thanks! Yeah, I actually was able to sort it out anyway. But I do have another question:
Is it possible to only have the "different" options in the dropdown, i.e. in the data table I have let's say 20 columns with different values. Some of the columns have the same value. some thing like this: 200, 200, 150, 15, 100, 200, 150...
In this scenario, is it possible to have the dropdown look like this: "200, 150, 100, 15"?
I am thinking I need to sort them first in some other cell?
Thanks!!
//O
Ok, so another question:) (I have been googling and checking tutorials...)
So, let's assume someone chooses "200" in the drop down. How can I "print" the "names" of the columns that has that value in their datasets?
IF B2=200; A2, A3, A7; "Empty"
IF B2= 150; A4, A8 etc.....
is there an easier way?
(EDIT, that didn't work)
What I am trying to do is a comparison chart. You choose what value you want from the dropdown, then in the "output" you get the names of all the columns that has that value
Filters can be simple or sophisticated. My example specifically showed the scenario where choosing 200 would hide the other entries. But you can do a lot more with them. I suggest experimenting with them to see what they can do.
SG
This is the result I get from selecting cells with those values and setting Data Format to Pop-Up Menu:
You can then drag the 15 down to get the order you want:
SG
I'm having trouble following your last two posts. Perhaps you could post a screenshot of what you are trying to do?
SG
Ah, sorry. Should have tried it first before asking!
Thanks again!
I can make it work with 1 option each for true and false, but I can't put several cells in the true or false spot
Great, thanks, this will be super useful!
Awesome, thanks! This, together with that post about recording equipment, will def help!
BUT, can't I have that filter be interactive? i.e. Choosing 200 from a dropdown will hide all the other companies?
Also, can I have the conditions be determined by a cell? (i.e. "Equal to B1")
Ok, thanks!
Populating dropdown with database
