Hot answers tagged

6

All steps in this answer need to be carried out on your Search sheet. There is no need to touch your Data sheet. First make sure you have at least 4,506 rows available in your Search Sheet. The reason for this number is that we're allowing enough room below your search field for all of the corresponding rows of data you have. In Cell G7 enter the following ...


4

You can use the module function for this. In A2 enter =MOD(A1;1000)


3

Days and dates are always messy, so whenever possible, one should try to use someone else's hard work. In this case, Apple have done it for us. Say your date is in B2, example forumla to add five business days: WORKDAY(B2,5,) You can read more about its usage and find the other date-related functions available in Apple's Numbers Functions Reference.


3

ADDRESS has an optional parameter specifying the table name for the reference which can span across sheets by specifying the sheet name before the table name. ADDRESS(row, column, addr-type, addr-style, table) table: An optional value specifying the name of the table. table is a string value. If the table is on another sheet, you must also include the ...


3

You need to direct the save command to Numbers app... This should work for you. tell application "Numbers" save document 1 in POSIX file "/Users/name/Desktop/Workout.numbers" delay 0.1 close document 1 delay 0.1 quit end tell Or if you want to make the code a little prettier... This should work also. set saveToFolder to path to ...


2

Here is an AppleScript method, which demonstrates three different types of action taken upon the rows with duplicate cell entries in columns "A" and "B" (equivalent to cells 1 and 2): use N : application "Numbers" -------------------------------------------------------------------------------- # PROPERTY VALUES & GLOBAL VARIABLES property document : a ...


2

You should be able to Select All the cells in the sheet, then in the Format Sidebar, click the Cell tab. Then in the Fill section of the sidebar, click the color well, then select a fill option. It will say Multiple Fills. You should be able to select No Fill and delete all background (fill) colors:


2

I think that the best solution would be to just assign your variable formula to a different cell and refer to that one in your main formula. Example: Cell A1: =5−SUM(C35:I35) Cell B1: IF(A1>0,A1,"") You could then hide the column where the variable is, so you don't have to see it.


2

I found some solution now. SOLUTION 1 Use "COUNTIF" function to find the number of cells that match the given text values and then multiply with the reference numbers. SOLUTION 2 Use Cell "Data Format" Select some Cell At the Format Panel > Cell > Data Format Create a Custom Format. Select type as "Number" Add some Rule that you want to format number ...


2

Sadly, there simply is no feature in Numbers resembling open-ended cell-range references. The only manageable workaround I observed actually working as intended involved either using entire rows, or marking as headers all of the columns/rows to be ignored. "Whole rows" tack, example procedure: Normalize and rearrange your data to inhabit the entire set of ...


2

Sorry to bring bad news, but there simply is no suitable formula, in-place ORing, or list comparison of that type in Numbers. Of the common workarounds for similar spreadsheet problems, I find the "helper column" approach to be the most straightforward and maintainable. Create a new column to calculate whether or not that row should count in the later ...


2

Compounding SUMIF statements with SUM will work as well without chewing up an intermediate results column. Like so: SUM(SUMIF(memo-column,"this-string*",amount-column), SUMIF(memo-column,"that-string*",amount-column))


2

Create a Numbers spreadsheet with the appropriate function logic, then lock the cells that viewers shouldn't touch. Then share a link from Numbers, with no protection so that anyone can use the web link. I think you'll be pretty pleased with it.


2

I finally got what I wanted. I used an app called Script Debugger 7, though not necessary, it really helped me with all the classes & options available with each application and detailed help. Further, it was really easy to write scripts in this app. For anybody needing a similar solution, I hope this helps. This script is not the best, in terms of ...


2

Is there a way to access a menu or list of all the sheets in a Numbers document, so I can quickly jump to one specific sheet without having to scroll or page one-by-one with the previous/next keyboard shortcuts? The only way I know of is to AppleScript it and use some mechanism to trigger the script, e.g. an Automator Service1. As an example, pressing ⌃⌘S ...


2

There is rich documentation and deep automation support for Numbers being automated with Automator and AppleScript. Here is a mid-tutorial link to where to save your scripts and shows the menu bar to trigger these scripts. I could see you entering the formula or substituting / selecting the range for which to apply a function quite easily. https://...


2

When putting a sum-over-the-entire-column formula into a regular cell, you are basically self-referencing the formula cell as a value cell. Try putting the the formula into a footer row. This way you can reference only the cells in a column without including the header or footer row. Add a footer row in the "Header & Footer" section in the Format > ...


2

You’re trying to define the filePath variable by combining a Posix path string with an HFS string. Try defining the filePath variable like this… set filePath to (path to library folder from user domain as text) & ¬ "Mobile Documents:com~apple~Numbers:Documents:Budget.numbers" tell application "Numbers" open alias filePath end tell OR.. My ...


1

Well, thank you Solar Mike, looking at Excel documentation gave me the answer to my question about how to define an inline collection with rows and columns. My mistake was trying to use C++ array initializer syntax. Instead you use a semicolon like so: =ROWS({FALSE,4;TRUE,5;TRUE,6}) =COLUMNS({FALSE,4;TRUE,5;TRUE,6}) =VLOOKUP(TRUE,{FALSE,4;TRUE,5;TRUE,6},2,...


1

Please consider revising your question to contain a very clear explanation of what you've tried, what didn't work in your best attempt(s), your tables' names, your label cells, your data cells' addresses and contents, which values are entered manually, and which you'd like to be automatic. Using my best guesses: You said Current Date is manually entered. ...


1

You can get a sort of macro facility working. Try this: Insert a separate sheet, make the table name unique to the spreadsheet (I use "Macros"), and ensure the table has one header row and one header column. Then label the column A header "Name" and column B header "macro". This sets you up with a shorthand, self-documenting naming system. To add a macro, ...


1

Just "spitballing" here, but do you have a Time Machine backup? If so, search it for 'index.xml'. If you find a file that resides in a Numbers 2.2-related folder, copy it to the present and put it in the same spot where you found it in the "past".


1

This sort of weirdness sometimes comes about with versions and saved application state where the application doesn't want to let go of some data you added. Do you have a Time Machine backup of the file in question? If not - be sure to copy out any data or log into the cloud version of numbers to be sure you have a cloud version you can live with. Then ...


1

You can do it by adding an extra column and using a formula in this column. Press = key on the keyboard to open the formula window and enter the formula: Then press the green button and copy paste the formula in other cells: You can copy and paste the data into the original column afterwards (but use "Edit" - "Paste Formula Results" command this time).


1

In the process of doing this exercise I understood lists are much better for storing read data. So here is the working code: tell application "Numbers" to tell the front document to tell the active sheet to tell table 1 repeat with i from 2 to the count of cells of column "A" -- row 1 is a header set theProduct to formatted value of cell i of ...


1

In Numbers go to: File menu > Advanced > Language and Region Select Persian from the dropdown list for the languages and then hit OK. That will revert your numbers as well as the rest of the locale related settings to Persian. Voila! (You can do the same in Excel or the rest of the Office family). Update: Note that this method will change all ...


1

AFAIK you can't. The closest you can get is highlighting a row/column/cell, and long-pressing (or force touching) then dragging, to insert/replace somewhere else. Also, highlight one of the above and pressing Option-ArrowKey (substitute ArrowKey for an arrow key) to insert a row/column/cell in the direction of the arrow key. Here is a link to the Numbers ...


1

Try conditional highlighting - this doesn't provide quite that high level of automation, as you have to add rules one-by-one - but you can automate values with all sorts of controls. Find the main menu in Format -> Cell -> Conditional Highlighting... Here is the main menu: You have options such as: Numbers | Text | Date | Duration | Blank Hope this helps :...


1

You just need to subtract the later from the former. ie: B2-B1


Only top voted, non community-wiki answers of a minimum length are eligible