I am moving data from excel to numbers. In excel I use some extensive pull down menus. Is there a quicker way to create pull downs than to type them in in the inspector? Other solution is ther a way to check data dring input against a a list during input instead of pull down?
MacBook Pro 15”, macOS 10.15
"Other solution is ther a way to check data dring input against a a list during input instead of pull down?"
You could use MATCH to locate an identical entry in a separate table. Set to require an exact match (find value), the function would return a number indicating the position of that item in the list; not finding the item would return a 'not found' error and display a red error triangle.
Wrapping the MATCH statement in an IF could return a check mark (square root sign) if MATCH returned a number greater than zero, and the same error message if the value was not found by MATCH.
Wrapping those two statements in IFERROR would permit replacing the error triangle (and message) with a text messsage (eg. letter X) when MATCH returned an error (or any other error occurred).
Example formula.
Entry is in A2, formulas in B2, C2 and D2, list of correct values is in column B of a table named Valid.
MATCH(A2,Valid::B,find value)
IF(MATCH(A2,Valid::B,find value)>0,"√","")
IFERROR(IF(MATCH(A2,Valid::B,find value)>0,"√",""),"X")
Formula shown is in B2 (and filled down)
Replace find value with 0 if copying and pasting the formula into a cell.
If your region uses comma as the decimal separator, replace ann commas in the formulas with semi-colons.
Regards,
Barry
"Other solution is ther a way to check data dring input against a a list during input instead of pull down?"
You could use MATCH to locate an identical entry in a separate table. Set to require an exact match (find value), the function would return a number indicating the position of that item in the list; not finding the item would return a 'not found' error and display a red error triangle.
Wrapping the MATCH statement in an IF could return a check mark (square root sign) if MATCH returned a number greater than zero, and the same error message if the value was not found by MATCH.
Wrapping those two statements in IFERROR would permit replacing the error triangle (and message) with a text messsage (eg. letter X) when MATCH returned an error (or any other error occurred).
Example formula.
Entry is in A2, formulas in B2, C2 and D2, list of correct values is in column B of a table named Valid.
MATCH(A2,Valid::B,find value)
IF(MATCH(A2,Valid::B,find value)>0,"√","")
IFERROR(IF(MATCH(A2,Valid::B,find value)>0,"√",""),"X")
Formula shown is in B2 (and filled down)
Replace find value with 0 if copying and pasting the formula into a cell.
If your region uses comma as the decimal separator, replace ann commas in the formulas with semi-colons.
Regards,
Barry
The easiest way to create a (set of) pop-up menu cell(s) is to enter the values into a single column of contiguous cells, select all of those cells, then click the Format brush, choose Cell, and set the data format to Pop Up menu.
Each of the selected cells will now contain a popup menu, set to the value displayed in the cell. Each of those cells is independent of the others, and any of those cells may now be copied and pasted into any cell in the document.
You can add items to one of those menus by entering the items into contiguous cells above or below the cell containing a popup menu, selecting that cell and the ones containing items to be added, then choosing Pop Up menu again in the format panel used above.
Example:
Copied and pasted B3, four more items in B4 to B7, items have been added to menu (and four new popup menu cells created) Note "Start with" setting in the inspector panel:
Each of the cells in B3-B7 now contains a copy of the menu shown. Extras may be deleted.
Regards,
Barry
Numbers Pull Down List or data validation
