Hi YLWT,
I have been working on a script to address just this issue.
Here is the before
After the script run:
The script itself:
--start script
-- This script converts column A in one table (PopupSourceTable in the script) into an alphabetized list of popups. It copies the last cell in that column, then reverts the column to text. You will want to keep that last cell empty. It then refreshes popups in column A of a data table (DataTable in the script) starting with a user defined row.
-- the columns, tables and sheets can all be customized by defining the properties below.
property DataEntrySheet : "popup tables" --name of sheet with popups to be refreshed
property DataEntryTable : "Data Table" --name of table with popups to be refreshed
property DataEntryColumn : "a" --column with popups to be refreshed
set PopStartRow to {}
set pasteRange to {}
property PopValueSheet : "popup tables" --name of sheet with popup source table
property PopValueTable : "customer list" --name of table with popup's values
property PopValueColumn : "b" --name of column with popup's values
set copyRange to {}
tell application "Numbers"
set d to front document
set ps to d'ssheetPopValueSheet
set pt to ps'stablePopValueTable
set s to d'ssheetDataEntrySheet
set t to s'stableDataEntryTable
set tf to t'sfiltered--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 (PopValueColumn & "2:" & name of cellptRows of columnPopValueColumn)
set selection range to rangecopyRange
set selection range'sformat to text
sortbycolumnPopValueColumndirectionascending--alphabetize popups
set selection range'sformat to pop up menu
-- popupsmade
set selection range to cellptRows of columnPopValueColumn of pt
set v to value of cell ptRows of pt
end tell
activateapplication "Numbers"
tell application "System Events" to keystroke "c" using command down
tell pt
set selection range to rangecopyRange
set selection range'sformat to text
end tell
tell t
set filtered to false
set tRows to count rows
set pasteRange to ((name of cellPopStartRow of columnDataEntryColumn) & ":" & (name of celltRows of columnDataEntryColumn))
set selection range to rangepasteRange
tell application "System Events" to keystroke "v" using command down
set filtered to tf
end tell
end tell
--end script
Unfortunately, this script is running inconsistency on my machine. It does everything it is supposed to including selecting the correct range to paste into and then it sometimes pastes into a new table. Help SG! can you see where my script is unclear?
quinn
