Q: Another popup menu question
-
Mar 27, 2015 6:42 AMby Yellowbox,Hi Jeff,
is it possible to get a long list from a colum into a popup menu without manually typing each entry or item in that popup menu?
Pop-Up Menus are powerful and versatile in Numbers 3. Here we go. A long-winded explanation, but it works.
This table has Conditional Highlighting as a safeguard. Select all the Body Cells (A2 to the bottom) and Format Panel > Cell > Conditional Highlighting > Add a Rule > Blank > Cell is Blank > Red Fill.
Select all the white cells and Format Panel > Cell > Data Format > Pop-Up Menu (Start with Blank is important).
The red cells are not included in the Pop-Up Menu and remain as Automatic format.
Click on Cell A2 and Copy.
In another table (Form Entry) double click on the column label A to select all Body Cells and Paste
In each row, choose a customer name from the Pop-Up Menu to reflect your orders.
Set unused rows to None.
When you need to add another customer, type the name in the first red cell of Create Pop-Up Menu table.
That cell turns white (it is no longer Blank).
Again, select all the white cells and set to Pop-Up Menu.
Click in A2 of Create Pop-Up Menu table and Copy.
Click in A2 of the Form Entry table (do not select the whole column) and Paste.
Select all Body Cells of Column A and Format Panel > Cell > Data Format > Merge Menu Items
Each cell in A of the Form Entry now has a Pop-Up Menu containing Customers 1 to 5.
But here is the power of Pop-Up Menus in Numbers 3: The original Customer names have not been overwritten (Customers 1, 3, 4, 1)
Now you can choose Customer 5 in the first red cell.
Long-winded? Yes, and it took more time to write this reply than to it took to add a customer to the Pop-Up Menu.
Regards,
Ian.
-
Mar 27, 2015 8:04 AMby t quinn,Hi jfnall,
I am in a similar situation so I wrote a script that addresses it. Here is my set up:
I have my popups in column A of sales. I have just added Byron to my contacts and want to update the popups in sales starting with row 3. Note the footer row in contacts. Since the script copies the last cell in the row I want it to stay empty.
The script:
-- This script converts column A in one table into an alphabetized list of popups. It copies the last cell in that column, then reverts the column to text. It then refreshes popups in column A of a data table starting with a user defined row.
property DataEntrySheet : "Sheet 1" --name of sheet with popups to be refreshed
property DataEntryTable : "Sales" --name of table with popups to be refreshed
set copyRange to {}
property PopValueSheet : "Sheet 1" --name of sheet with popup values table
property PopValueTable : "Contacts" --name of table with popup values
set PopStartRow to {}
tell application "Numbers"
set d to front document
set ps to d's sheet PopValueSheet
set pt to ps's table PopValueTable
set s to d's sheet DataEntrySheet
set t to s's table DataEntryTable
set tf to t's filtered --this records filter setting on data Entry Table
display dialog "Start from row #..." default answer "" with icon 1 -- with icon file "Path:to:my.icon.icns" --a Week # row
set PopStartRow to {text returned of result}
tell pt --convert list to alphabetized popups
set ptRows to count rows
set copyRange to ("A2:" & name of cell ptRows of column "A")
set selection range to range copyRange
set selection range's format to text
sort by column 1 direction ascending
set selection range's format to pop up menu
-- popupsmade
set selection range to cell ptRows of column 1 of pt
set v to value of cell ptRows of pt
end tell
activate application "Numbers"
tell application "System Events" to keystroke "c" using command down
tell pt
set selection range to range copyRange
set selection range's format to text
end tell
tell t
set filtered to false
set tRows to count rows
set pasteRange to ((name of cell PopStartRow of column "A") & ":" & (name of cell tRows of column "A"))
set selection range to range pasteRange
tell application "System Events" to keystroke "v" using command down
set filtered to tf
end tell
end tell
Open your Applescript Editor (I think it is Script Editor in Yosemite) and paste this script in. I set my Script Editor to show up in the menu bar and run scripts right from there.
You need to make sure that the 4 properties at the beginning of the script agree with your setup. Your customer table may be my contacts table and it may not be on the same sheet. When you run the script it will ask you where you want to start with the refreashed popups. It will overwrite any cells starting with the row you designate.
This is a generic version of the script I made for myself. Let me know how it works for you.
quinn
-
Mar 27, 2015 8:56 AMby SGIII,I've noticed that if you put Copy Style and Paste Style the toolbar:
Then you can:
- Add the new value(s) to your source column
- Select the values in the column and choose Data Format > Pop-Up Menu
- Select one of the cells in the source column (which now includes the new values in its Pop-up Menu) and click Copy Style
- Select cells in the target column, and click Paste Style. The target column will then include the (expanded) Pop-up Menu.
SG
-
Mar 28, 2015 9:25 AMby SGIII,deleting cells and copying and pasting in new popup menu cells in its place, with the added customer name.
Curious why you think "deleting cells and copying and pasting" is anything like what is suggested in any of the messages upthread!
Have you tried any of the suggested methods?
SG
-
Mar 29, 2015 5:47 PMby Jfnall,there is copying and pasting on the 1st and 3rd descriptions.
what i was wondering was if the newest version of Numbers allowed for a source column that could be added to, and those rows typed into the source column would automatically become available in a linked Popup target cell. would be nice to add a customer, or several, then sort the column, and the popup menu be adjusted with the new names, in alphabetical order, without having to manually move stuff around inside the popup menu editing window.
