Can you put a formula in a tab name so it will reference a cell for it's name. In other words: I'd like the name of each tab (instead of Sheet 1, 2, etc...) to be referenced to a cell within each sheet. And is there a way to make this a default ?
iMac, OS X Mavericks (10.9), 20" (early 2008)
Hi Sharpiejeff,
I am not clear on what you are asking. What are you trying to accomplish? How would you use this? You can name a tab whatever you want but it is not a cell in a table and you cannot run a formula from a tab.
If I wanted to get to Row 300 in table 1 of sheet 1 I could create a second table (maybe call it "300") and drag it down to row 300. When I choose it from Sheet 1's tab I will wisked down to that table and incidentally row 300 of table 1. Does this help?
quinn
Can you put a formula in a tab name so it will reference a cell for it's name. In other words: I'd like the name of each tab (instead of Sheet 1, 2, etc...) to be referenced to a cell within each sheet.
You can't do this with a formula, but you can change a sheet (tab) name via AppleScript. For example, this script would name sheet whatever you put in A1 of the first table on the sheet. But it would seem easier to just rename the sheet manually. As quinn asks, can you give more specifics on what you are trying to accomplish?
SG
tell application "Numbers"
tell document 1's sheet 1's table 1
set newName to cell "A1"'s value
set parent'sname to newName
end tell
end tell
Thank you. Now I know that the tab of a sheet can't have a formula in it. Not sure why Apple doesn't do that, but who knows the reason.
I Use numbers to create invoices. I want the sheet tab to show the invoice number. I'd like the Tab to reference a cell within the sheet. That way it will automatically be named Instead of just "sheet 2", etc.
Have you tried the script approach?
To use:
That's it. The name in the tab changes to match the invoice number you have in a cell.
SG
property invoiceNoCell : "A1"
property invoiceNoTable : 1
tell application "Numbers"
tell document 1's active sheet'stableinvoiceNoTable
set newName to cellinvoiceNoCell'svalue as text
set parent'sname to newName
end tell
end tell
Hi Sharpiejeff,
I hate navigating through the sheet tabs when there are more than 8 or 9- it is a real pain to scroll through them. If you are wanting to save copies of invoices there are better ways to do that. If you want to be able to refer back to past invoices to mine data, etc. Building a summary table would cover you for that and probably simplify data extraction. I have a generic invoice that will call the relevant data depending on what I choose in certain popups. I save a PDF of the invoice and the data remains in my other sheets.
That said, a script like the one offered by SG could rename your sheets to match a designated cell in the table.
quinn
Not sure what you mean by a summary table, but unassuming its similar to s ledger. Which is what I need. Any suggestions on how to better organize my spreadsheets/invoices into an accounts receiveable /summary table ?
Hi SharpieJeff,
If I understand what you are trying to do, you are trying to bring a large number of scattered tables into a single file/doc. This is a good thing. You should see if you can get SG interested in the specifics of doing that.
I want to suggest that you attempt to future proof your setup by imagining what will happen as your data increases over the years. what may work for a handful of clients could breakdown with 100's. I restart my income/billing spreadsheet every year. There is some carryover but it keeps things from getting too large and I have orgaized things so I can copy a few tables and make a few entries to start the year. I have a purchases/inventory file that I run from year to year because I want to be able to track things over multiple years.
It is hard to suggest how to organize things without the specifics of your situation. A pretty standard format would be to have a main data table that carries dates names and services and then smaller tables that can pull info from that table when you need an invoice or want a balance owed or want to know how often you performed a certain service, etc.. Those could be thought of as summary tables.
quinn
that looks super helpful, thank you. Not exactly sure where start. I think there might be a few steps you left out because you were nice enough to assume i knew what i was doing. Could you give me a little more detail? what do i do after i hit run? Do i go back into numbers to activate the script somehow?
Reason i ask is the tab name now says "missing value". I just don't know how to properly use the script.
Also, to make things more difficult i have 3 tables on the sheet. I only need to reference B2 cell on "Table 3". Wow, i'm a huge pain in the ***. sorry, SG.
Hey Jeff,
Not to worry, you just need to adjust SG's script.
property invoiceNoCell : "A1"
property invoiceNoTable : 1
Those properties are sending it to cell A1 of table 1. You know where you want it to go.
quinn
I'm still lost. I don't know how to engage the "script". Do i need to have my spreadsheet open and select the table/cell i want to reference?
There seems to be more steps in between that y'all must assume i know about. I need an actual step-by-step-by-step. Not sure if you are willing to take the time to help me, but i could really use the help. Sorry i'm not understanding easily.
Reason i ask is the tab name now says "missing value". I just don't know how to properly use the script.
That means the script did work but there was nothing in the cell the script addresses.
As quinn explains you need to change the "A1" and the 1 in the two property statements to point the cell where you are storing the name that you want to apply to your sheet.
There is nothing special you have to do to run the script except to have the document open and click somewhere on the sheet that you are renaming to make it the "active" sheet. You don't need to select a specific cell. Just follow the three steps outlined in the post upthread.
SG
Rename a tab ("sheet 2") by referencing a cell's data
