Another popup menu question

This is a great script. I'm wondering if you can help me modify it. In my application, I need to have the popup menu appear only in a single cell, but can't seem to figure out how to modify the script so that the updated pop-up menu pastes exclusively into Cell A1 of my DataEntryTable. How would I edit

setpasteRangeto ((nameofcellPopStartRowofcolumn "A") & ":" & (nameofcelltRowsofcolumn "A"))

So that it pastes into a specific cell, rather than the entire column?

Thanks in advance!

Hi mld.smith,

Glad you are using the script. Here is a version that pastes into one cell only (A1).

-- 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 popup in cell A1 of a data table.

property DataEntrySheet : "Sheet 3" --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 3" --name of sheet with popup values table

property PopValueTable : "Contacts" --name of table with popup values

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

tell pt--convert list to alphabetized popups

set ptRows to count rows

set copyRange to ("A1:" & name of cell ptRows of column "A")

set selection range to rangecopyRange

set selection range'sformat to text

sortbycolumn 1 directionascending

set selection range'sformat to pop up menu

-- popupsmade

set selection range to cell ptRows of column 1 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 selection range to range (name of cell 1 of column "A") -- this is the cell that the popups are pasted into

tell application "System Events" to keystroke "v" using command down

set filtered to tf

end tell

end tell

You can see the pasteRange is unneeded. I took most of the extraneous stuff out- no need for the dialogue and other stuff. I keep thinking I should be able to set a value for a cell without copy/paste but I am still coming up empty on that one.

quinn

Walkermomma66 wrote:

Can't use conditional formatting on iPad Air 2.

Of course you can "use" Conditional Highlighting on the iPad! You set it up in Numbers on the Mac, sync the document to the iPad, and it works as expected there.

SG

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.

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'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 ("A2:" & name of cell ptRows of column "A")

set selection range to rangecopyRange

set selection range'sformat to text

sortbycolumn 1 directionascending

set selection range'sformat 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

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 cell PopStartRow of column "A") & ":" & (name of cell tRows of column "A"))

set selection range to rangepasteRange

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

I've noticed that if you put Copy Style and Paste Style the toolbar:

Then you can:

1. Add the new value(s) to your source column
2. Select the values in the column and choose Data Format > Pop-Up Menu
3. Select one of the cells in the source column (which now includes the new values in its Pop-up Menu) and click Copy Style
4. Select cells in the target column, and click Paste Style. The target column will then include the (expanded) Pop-up Menu.

SG

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

Hi Jeff,

Currently, scripts are only running on the desktop. Don't know if that will change antime soon.

quinn

Hi Jeff,

The answer is "No, you can't do that in Numbers."

You can always give Apple feedback. This is a popular request on this forum.

quinn

THANK YOU! This helped me with a problem I've had with a checkbook spreadsheet I've been slaving over! Now I can copy popup items from month to month/tab to tab.

Easiest solution, by far!

Can't use conditional formatting on iPad Air 2. But I can use your formula but can' t make any changes to my list should I need to.