Inserting CELLS!
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Dang, I hope I just missed how to do this, but when I mark a section of a table and want to insert some cells (and push everything else down), I could only find stuff about inserting rows and columns!
The only way I could move the SECTION down to make room above was to mark the whole column of data from the point I wanted to insert and drag it down. This was a pain because that particular section is pretty long.
Does Numbers have anything like Excel where you can insert cells and have the program automatically make the movement of cells to leave room?
12 replies
Sign in or join Beachball Help to reply.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I feel that my messages where clear: I'm writing about scripts.
The fact that we may move formulas with the mouse is a basis of spreadsheet but is perfectly meaningless here because this feature can't be reproduced in a Script.When I try to help someone I feel that I may hope that he read carefully my responses.
Given that, I feel useless to give more details đ
Here is the script, play with it.--[SCRIPT insert_cells]
(*
Enregistrer le script en tant qu'Application ou Progiciel : insert_cells.app
déplacer l'application créée dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-ĂȘtre crĂ©er le dossier Numbers et peut-ĂȘtre mĂȘme le dossier Applications.
Copiez un groupe de cellules dans le presse-papiers.
Placez le curseur au point d'insertion de votre choix.
menu Scripts > Numbers > insert_cells
Le script vous demandera de choisir d'insérer vers le bas ou vers la droite.
Il insÚrera les valeurs copiées.
Il s'agira de valeurs fixes.
Des modifications dans le bloc source ne seront pas répercutées.
Merci Ă Scott Lindsey & Ed.Stockly du forum [email protected]
+++++++
Save the script as an Application or an Application Bundle: insert_cells.app
Move the newly created application into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Copy a group of cells in the clipboard.
Put the cursor where you want.
menu Scripts > Numbers > insert_cells
It will ask you to select insert to the bottom or insert to the right.
It will insert the copied set of values.
Of course, it will be a 'frozen' block.
Changes in the source one will not be reflected.
Thanks to Scott Lindsey & Ed.Stockly from [email protected]
Yvan KOENIG (Vallauris, FRANCE)
3 février 2009
*)
property theApp : "Numbers"
--=====
on run
try
set avant to the clipboard as text
on error
error "No copied values in the clipboard !"
end try
set avant to paragraphs of avant
repeat with i from 1 to count of avant
set item i of avant to my decoupe(item i of avant, tab)
end repeat
set {rName, tName, sName, dName} to my getSelection()
if my parleFrancais() then
if rName is missing value then error "Aucune feuille ne contient une table sélectionnée."
else
if rName is missing value then error "No sheet has a selected table."
end if
if my parleFrancais() then
set default to "vers le bas"
set toBottom to display dialog "Comment insérer ?" buttons {"Annuler", "vers la droite", default} default button default
else
set default to "to the Bottom"
set toBottom to display dialog "Insert how ?" buttons {"Cancel", "to the Right", default} default button default
end if
set toBottom to (button returned of toBottom) = default
set twoNames to my decoupe(rName, ":")
set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
(* Here we know the starting point of the destination area. *)
set nbLignesSource to count of avant
set nbColonnesSource to count of item 1 of avant
tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
set oldLastColumn to column count
set oldLastRow to row count
if toBottom then (*
Here we insert to the bottom *)
my ajouteLignes(dName, sName, tName, nbLignesSource)
repeat with y from (oldLastRow + nbLignesSource) to (rowNum1 + nbLignesSource) by -1
repeat with x from colNum1 to (colNum1 - 1 + nbColonnesSource)
set value of cell x of row y to value of cell x of row (y - nbLignesSource)
end repeat
end repeat
else (*
Here we insert to the right *)
my ajouteColonnes(dName, sName, tName, nbColonnesSource)
repeat with y from (oldLastColumn + nbColonnesSource) to (colNum1 + nbColonnesSource) by -1
repeat with x from rowNum1 to (rowNum1 - 1 + nbLignesSource)
set value of cell x of column y to value of cell x of column (y - nbColonnesSource)
end repeat
end repeat
end if -- toBottom
repeat with y from 1 to nbLignesSource
tell row (rowNum1 - 1 + y)
repeat with x from 1 to nbColonnesSource
set value of cell (colNum1 - 1 + x) to item x of item y of avant
end repeat
end tell -- row
end repeat
end tell -- application
end run
--=====
on ajouteColonnes(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t
repeat n times
add column after last column
end repeat
end tell -- table of sheet of document of application
end ajouteColonnes
--=====
on ajouteLignes(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t
repeat n times
add row below last row
end repeat
end tell -- table of sheet of document of application
end ajouteLignes
--=====
on getSelection()
local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
tell application "Numbers" to tell document 1
set mySelectedRanges to selection range of every table of every sheet
repeat with sheetRanges in mySelectedRanges
repeat with thisRange in sheetRanges
if contents of thisRange is not missing value then
try
thisRange as text
on error errMsg number errNum
set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)
return {myRange, myTable, mySheet, myDoc}
end try
end if -- contentsâŠ
end repeat -- thisRange
end repeat -- sheetRanges
end tell -- document 1 of application
return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on decipher(n)
local letters, colNum, rowNum
set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
if (character 2 of n) as text > "9" then
set colNum to (offset of (character 1 of n) in letters) * 64 + (offset of (character 2 of n) in letters)
set rowNum to (text 3 thru -1 of n) as integer
else
set colNum to offset of (character 1 of n) in letters
set rowNum to (text 2 thru -1 of n) as integer
end if
return {colNum, rowNum}
end decipher
--=====
on decoupe(t, d)
local l
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to ""
return l
end decoupe
--=====
on parleFrancais()
local z
try
tell application theApp to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z = "Annuler")
end parleFrancais
--=====
--[/SCRIPT]Of course, it is also available on my iDisk:
<http://idisk.me.com/koenigyvan-Public?view=web>Download:
For_iWork:iWork '09:for
Numbers09:Insertcells.zipYvan KOENIG (from FRANCE mardi 3 février 2009 15:54:08)
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Numbers is definitely not a clone of Excel.
The described feature is unavailable.
If the cells below the insertion point contain values, not formula, I may write a Script which will mimic the feature.
Yvan KOENIG (from FRANCE lundi 2 février 2009 12:59:11)
- 0
dbk9999 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I know they are not clones. I mention the Excel ability in case my description isn't clear and readers have experience with Excel - thus maybe making it clearer as to what I am looking for.
Some situations I have formulas and others do not. Also, sometimes it would involve moving one column's rows down and other times multiple columns.
Fortunately, it isn't something I normally do every day. There may even be times I can structure things differently and be okay - but I don't think that will always be the case.
Curious as to why having formulas or not would make a difference with a script though. I mean, when I manually moved the data, it didn't look like the formulas got messed up (maybe I better check in more detail?).
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
As is, we are unable to grab formulas when we get the contents of cells.
We get only the values.I hope that it will change but we have to live with that.
The script may set temporarily the format of cells which move to text.
Doing that, we will get the formulas as text but they will be copied as is.
Resetting the format to automatic will revive formulas but I'm quite sure that they will not point to correct cells.
It will remain your task to be sure that what you paste is correctly formatted. If you must paste formulas, the source must be formatted as text before copying.I'm writing the script.
You will be able to test it when it will be ready.Yvan KOENIG (from FRANCE lundi 2 février 2009 20:38:11)
- 0
dbk9999 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Well, I'm a little confused, but I appreciate the help. I look for these issues as I work.
But when you say you can't grab formulas, are you just referring to scripts? I ask because when I grabbed the whole section I needed to move down and dragged it lower to make room for new monthly summary data (I do this each month), I still see formulas.
They aren't complicated and all refer to info within a given month. Basically column F4 just takes whatever amount of money is in H3 and adds whatever is in H4. There is only one that is different, but still stays within the same section.
So, I'm not sure if this is what you were referring to when you said you would end up with the data but not the formulas.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I feel that my messages where clear: I'm writing about scripts.
The fact that we may move formulas with the mouse is a basis of spreadsheet but is perfectly meaningless here because this feature can't be reproduced in a Script.When I try to help someone I feel that I may hope that he read carefully my responses.
Given that, I feel useless to give more details đ
Here is the script, play with it.--[SCRIPT insert_cells]
(*
Enregistrer le script en tant qu'Application ou Progiciel : insert_cells.app
déplacer l'application créée dans le dossier
<VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
Il vous faudra peut-ĂȘtre crĂ©er le dossier Numbers et peut-ĂȘtre mĂȘme le dossier Applications.
Copiez un groupe de cellules dans le presse-papiers.
Placez le curseur au point d'insertion de votre choix.
menu Scripts > Numbers > insert_cells
Le script vous demandera de choisir d'insérer vers le bas ou vers la droite.
Il insÚrera les valeurs copiées.
Il s'agira de valeurs fixes.
Des modifications dans le bloc source ne seront pas répercutées.
Merci Ă Scott Lindsey & Ed.Stockly du forum [email protected]
+++++++
Save the script as an Application or an Application Bundle: insert_cells.app
Move the newly created application into the folder:
<startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
Maybe you would have to create the folder Numbers and even the folder Applications by yourself.
Copy a group of cells in the clipboard.
Put the cursor where you want.
menu Scripts > Numbers > insert_cells
It will ask you to select insert to the bottom or insert to the right.
It will insert the copied set of values.
Of course, it will be a 'frozen' block.
Changes in the source one will not be reflected.
Thanks to Scott Lindsey & Ed.Stockly from [email protected]
Yvan KOENIG (Vallauris, FRANCE)
3 février 2009
*)
property theApp : "Numbers"
--=====
on run
try
set avant to the clipboard as text
on error
error "No copied values in the clipboard !"
end try
set avant to paragraphs of avant
repeat with i from 1 to count of avant
set item i of avant to my decoupe(item i of avant, tab)
end repeat
set {rName, tName, sName, dName} to my getSelection()
if my parleFrancais() then
if rName is missing value then error "Aucune feuille ne contient une table sélectionnée."
else
if rName is missing value then error "No sheet has a selected table."
end if
if my parleFrancais() then
set default to "vers le bas"
set toBottom to display dialog "Comment insérer ?" buttons {"Annuler", "vers la droite", default} default button default
else
set default to "to the Bottom"
set toBottom to display dialog "Insert how ?" buttons {"Cancel", "to the Right", default} default button default
end if
set toBottom to (button returned of toBottom) = default
set twoNames to my decoupe(rName, ":")
set {colNum1, rowNum1} to my decipher(item 1 of twoNames)
(* Here we know the starting point of the destination area. *)
set nbLignesSource to count of avant
set nbColonnesSource to count of item 1 of avant
tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
set oldLastColumn to column count
set oldLastRow to row count
if toBottom then (*
Here we insert to the bottom *)
my ajouteLignes(dName, sName, tName, nbLignesSource)
repeat with y from (oldLastRow + nbLignesSource) to (rowNum1 + nbLignesSource) by -1
repeat with x from colNum1 to (colNum1 - 1 + nbColonnesSource)
set value of cell x of row y to value of cell x of row (y - nbLignesSource)
end repeat
end repeat
else (*
Here we insert to the right *)
my ajouteColonnes(dName, sName, tName, nbColonnesSource)
repeat with y from (oldLastColumn + nbColonnesSource) to (colNum1 + nbColonnesSource) by -1
repeat with x from rowNum1 to (rowNum1 - 1 + nbLignesSource)
set value of cell x of column y to value of cell x of column (y - nbColonnesSource)
end repeat
end repeat
end if -- toBottom
repeat with y from 1 to nbLignesSource
tell row (rowNum1 - 1 + y)
repeat with x from 1 to nbColonnesSource
set value of cell (colNum1 - 1 + x) to item x of item y of avant
end repeat
end tell -- row
end repeat
end tell -- application
end run
--=====
on ajouteColonnes(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t
repeat n times
add column after last column
end repeat
end tell -- table of sheet of document of application
end ajouteColonnes
--=====
on ajouteLignes(d, s, t, n)
tell application "Numbers" to tell document d to tell sheet s to tell table t
repeat n times
add row below last row
end repeat
end tell -- table of sheet of document of application
end ajouteLignes
--=====
on getSelection()
local mySelectedRanges, sheetRanges, thisRange, _, myRange, myTable, mySheet, myDoc, mySelection
tell application "Numbers" to tell document 1
set mySelectedRanges to selection range of every table of every sheet
repeat with sheetRanges in mySelectedRanges
repeat with thisRange in sheetRanges
if contents of thisRange is not missing value then
try
thisRange as text
on error errMsg number errNum
set {_, myRange, _, myTable, _, mySheet, _, myDoc} to my decoupe(errMsg, quote)
return {myRange, myTable, mySheet, myDoc}
end try
end if -- contentsâŠ
end repeat -- thisRange
end repeat -- sheetRanges
end tell -- document 1 of application
return {missing value, missing value, missing value, missing value}
end getSelection
--=====
on decipher(n)
local letters, colNum, rowNum
set letters to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
if (character 2 of n) as text > "9" then
set colNum to (offset of (character 1 of n) in letters) * 64 + (offset of (character 2 of n) in letters)
set rowNum to (text 3 thru -1 of n) as integer
else
set colNum to offset of (character 1 of n) in letters
set rowNum to (text 2 thru -1 of n) as integer
end if
return {colNum, rowNum}
end decipher
--=====
on decoupe(t, d)
local l
set AppleScript's text item delimiters to d
set l to text items of t
set AppleScript's text item delimiters to ""
return l
end decoupe
--=====
on parleFrancais()
local z
try
tell application theApp to set z to localized string "Cancel"
on error
set z to "Cancel"
end try
return (z = "Annuler")
end parleFrancais
--=====
--[/SCRIPT]Of course, it is also available on my iDisk:
<http://idisk.me.com/koenigyvan-Public?view=web>Download:
For_iWork:iWork '09:for
Numbers09:Insertcells.zipYvan KOENIG (from FRANCE mardi 3 février 2009 15:54:08)
- 0
dbk9999 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Yes, we were talking about scripts, but that doesn't mean I could be sure the other comments did not also apply to other things. I try to be sure to avoid problems that may come.
You are obviously quite familiar with iWorks and Applescript. I am not. It may be that my questions were unnecessary if I just ASSUMED you were only talking about scripts - but if I always make such assumptions, I'll have more problems than I avoid because a lot of the time the things people say do NOT strictly stay within a very narrow context and it would be a mistake for me to think they do.
I thank you for your assistance.
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
dbk,
Many pains arise from long tables. That is why it's good to keep your tables as short as possible, and especially to use multiple tables for disparate data. Moving Cells Down is generally a bad idea; it indicates a basic failure to plan ahead and could corrupt data records where the assumption was that the data in rows will stay intact. I am not disappointed that Numbers does not make this operation any less difficult than it is.
Jerry
- 0
dbk9999 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
True enough, but there are two factors involved for me. One is that I'm busy converting a whole bunch of files to Numbers (and Pages). Can't restructure them all at once. Heck, I haven't even had the time to convert all over yet. And I have some that being able to insert still seems like the best way for my situation.
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
dbk,
Are you familiar with the Mark for Move / Move command pair? I think it might make things easier for you. I agree that Selecting and Dragging can be tricky and nerve-wracking.
Here's the deal: Click on the first cell to move. Pull the scroll bar down to expose the last cell and Shift-Click the last cell. Now you have a range selected. Then: Edit > Mark for Move. Now, click where you want the top cell in the range to move to and: Edit > Move.
It's easy and stress-free.
Jerry
- 0
dbk9999 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Okay, I did some experimenting with this. Here are some observations to see if I missed anything:
1. The first steps are the same as for if you are dragging (mark the top left and bottom right corner of what you're moving).
2. If I'm ONLY dragging and there is enough empty rows below and the dragging isn't a tiny cell area where I might slip up, I might as well drag.
3. But if there is not enough empty space below, if I mark to move and then place where I want it to start, the move operation will add whatever rows I need to make it all fit.
4. Either method, if there is something below the stuff to move, it will overwrite it.
Does that about cover it?
If so, I can see where I may use both methods depending on the situation. Not quite as easy as I'm used to, but hey, they will be a Numbers '10 eventually!
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Yes, I think that covers it.
Jerry