Change selected cell references to absolute

If I correctly understand what you want to do, another way to do it that does not require changing everything to absolute addressing is as follows:

1. Copy the 12x32 table of formulas and paste it on the new sheet (temporarily).
2. Select the range of cells you want to "copy and paste" from this newly pasted table (but don't do copy/paste)
3. After selecting the range, click and hold inside the selection box until the cells "lift" out of the table.
4. Drag and drop them where you want them in the other table on this sheet.
5. When done, delete the 12x32 table from this sheet (or cut and paste it onto the next sheet to do the same thing with the next range of cells)

If your goal is to make all references in a selected range absolute references then this script below should do that for you.

Copy-paste into Script Editor (in Applications > Utilities

Select the cells where you want to convert to absolute references.

Click the 'run' button in Script Editor

Notes:

• This will work for formulas like your B2x(-1), which will be converted to $B$2x(-1).
• But if there is a function name in front, e.g. something like SUM(B2:B5) it will inserts an unwanted $ in front of the SUM, so you get $SUM($B$2:$B$5), which Numbers will tell you (rightly) is not a valid function name.
• As always make a backup of your work first before running this, as it does modify the original contents.

SG

The script:

tell application "Numbers" to tell front document to tell active sheet
	tell (first table whose class of selection range is range)
		repeat with aCell in cells of (get selection range)
			set formulaString to formula of aCell as text
			if formulaString is not "missing value" then
				set newFormula to my insertDollarSigns(formulaString)
				set value of aCell to newFormula
			end if
		end repeat
	end tell
end tell

-- Handler to insert $ before the first alpha character and before the first numerical character in each sequence
on insertDollarSigns(inputString)
	set modifiedString to ""
	set inAlpha to false
	set inNumber to false
	repeat with char in inputString
		if (char is in "ABCDEFGHIJKLMNOPQRSTUVWXYZ") then
			if (not inAlpha) then
				set modifiedString to modifiedString & "$"
				set inAlpha to true
			end if
			set inNumber to false
		else if (char is in "0123456789") then
			if (not inNumber) then
				set modifiedString to modifiedString & "$"
				set inNumber to true
			end if
			set inAlpha to false
		else
			set inAlpha to false
			set inNumber to false
		end if
		set modifiedString to modifiedString & char
		set prevChar to char
	end repeat
	return modifiedString
end insertDollarSigns

Mea Culpa - I misunderstood your directions. If I make a duplicate of the whole sheet, in order to restore the information which gets lost when I move the selection, then I can go back to my original sheet (actually another duplicate of the original, because I'm cautious) and add a second table to that sheet. And in that case, drag and drop, from one table to the other does retain the references. Yay!

I was actually attempting something along these lines with the creation of my Master Template because someone else told me a month ago that cell references are maintained when referring to cells in another table or sheet. I just couldn't figure out how to drag and drop from one sheet to another (forgetting about tables). And I was using option-click so I wouldn't lose the original data (which as you point out, doesn't work).

But drag and drop from one table to another works. And the extra effort to restore the original data after each move (each selection is repeated from 3 to 5 times) is trivial. Thank you for making that suggestion!

(I also discovered I was signed in under the wrong Apple ID when I asked my question, if you're wondering about the name change.)

Franny327 wrote:

Thank you. I gave it a try, but it still gives me trouble with not maintaining the cells references to the master group, once I move it to a different positive relative to the master (like below the master instead of to the side).

That is odd. It always keeps the cell references intact (unless you also hold Option while dragging, which copies the range of cells/formulas vs relocating it). If a cell references Sheet 1::Table 1::C5 and you drag-drop it elsewhere, it will continue to reference Sheet 1::Table 1::C5.

Can you give a specific example of what you want? Do you always want preserve rows and preserve columns, e.g B2 become $B$2, etc. ?

SG

Thanks for the kind feedback. And the upvote!

SG