Numbers 3.5 Formula "name of sheet"

Question

Level 1

12 points

Numbers 3.5 Formula "name of sheet"

It keeps happening to me that I need to access the name of a sheet in a formula.

The standard solution in Excel is easily found in several newsgroups.

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1) ,1))

It is equally simple to figure out how to let VBA macros put customised formulas into cells in order to access some data which had been pasted from the clipboard by the same VBA macro. Would anybody let me know where to find a complete documentation of Numbers' automator interface, please?

I am still having a hard time to understand why such useful information is kept secret from me if want to use it in the context of Apple software?

I am not even asking for a complete solution but just for a general idea that would let me continue figuring it out myself.

Thanks in Advance!

MacBook Air, OS X Yosemite (10.10.5)

Posted on Dec 27, 2015 7:00 AM

Reply

Answer 1

SGIII

Level 8

37,562 points

Dec 27, 2015 9:00 AM in response to Automiser

Could you give a general idea of the specific problem you are trying to solve? A screenshot would help. Wayne explains how to do that here.

The rough equivalent to VBA is AppleScript, which can be run within Automator but is often run outside Automator too. Seethis site for examples of AppleScript automation of Numbers and the other iWorks apps. There are other examples at macscripter.net, and of course in these forums.

You can definitely have a script paste in data and also, if needed, insert a formula that accesses that data, though you can't do UDFs (user-defined-functions) in Numbers. They're notoriously slow in Excel anyway, so no big loss.

More information is needed on what you are trying to do.

SG

Reply

Answer 2

Automiser Author

Level 1

12 points

Dec 28, 2015 7:23 AM in response to SGIII

Thanks for considering my problem!

I had already been trying to write it up in a unique way but I am quite new to writing to this community. Essentially I am looking for a formula in Numbers 3.5 which is equivalent to the quoted Excel formula

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1) ,1))

I know how to make screen shots but I do not know which sort of screen shot could tell you more precisely what I need than writing in plain English that I need a formula that returns the name of the sheet my formula is put on. Ideally I would imagine this formula to be built-in bearing the name NameOfSheet() but it does not seem like developers of spreadsheet applications consider it to be valuable enough. I can imagine thousands of applications of such a formula, though. Doesn't it happen from time to time to nearly everybody that they want to use dates as names of worksheets? Isn't there anybody who would then want to put =Weekday(NameOfSheet()) into one of the cells?

BTW I do not think the Excel solution of returning the name of the current sheet militates in favour of Excel's quality. I simply cannot deny that it is rather hard to tell within the glimpse of an eye why the example I was giving returns what I am asking for.

As for my other question, I can tell I already have some AppleScript experience and used some Automator workflows in convenient ways. I had already been quite sure for a while that there is an AppleScript solution to the problem how to paste data into spreadsheets and entering some formulas for evaluation. However, this is nothing I need asap so I am not really concerned about it, yet. Moreover, I already knew of iworkautomation but thank you so much for pointing me to macscripter.net!

Reply

Answer 3

Yellowbox

Level 7

21,945 points

Dec 28, 2015 7:49 AM in response to Automiser

Hi Automiser,

My 2 cents worth. It doesn't solve the problem, but simply throws some light on how Numbers refers between Sheets (Tabs).

Screen shot of Sheet 1.

Screen shot of Sheet 2 showing the formula to add cells A2 and B2 on Sheet 1

in the Formula Editor, drag to select that formula and Copy.

Format B3 as Text and Paste.

Formula in B4 (formatted as Automatic)

=MID(B3,2,7)

That will work for Sheets 1 to 9, then for Sheets 10 and upwards you will need

=MID(B3,2,8)

Regards,

Ian.

Reply

Answer 4

SGIII

Level 8

37,562 points

Dec 28, 2015 8:46 AM in response to Automiser

Automiser wrote:

Essentially I am looking for a formula in Numbers 3.5 which is equivalent to the quoted Excel formula

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1) ,1))

I need a formula that returns the name of the sheet my formula is put on. Ideally I would imagine this formula to be built-in bearing the name NameOfSheet() but it does not seem like developers of spreadsheet applications consider it to be valuable enough.

The short answer is that this is not possible in Numbers. A Numbers formula has no way of retrieving a sheet or table name. AppleScript can of course easily do this and place the results on the clipboard for pasting somewhere with command-v:

tell application "Numbers" to tell front document

set sheetName to active sheet'sname

set the clipboard tosheetName

end tell

You cannot, however, construct a UDF (user-defined-function) in Numbers the way you can construct a UDF with VBA in Excel.

But the more relevant question is why you would want to do this. Generally I think you'll be happier working with a relatively small number of sheets in Numbers, each with multiple tables. Cases like wanting to enter something like =Weekday(NameOfSheet()) into a cell are likely to be relatively rare and easily addressed by simply entering the relevant date.

As Ian demonstrates, Numbers does the filling in of sheet and table references for you when you are building formulas. While constructing the formula, you simply click the relevant cell or range and Numbers adds the table name (and sheet name in front of that, if the table name is not unique in the document).

SG

Reply