MacMost Now 258: Using Multiple Tables In iWork Numbers
iWork 09's spreadsheet program Numbers isn't like the old-fashioned spreadsheet programs you may be used to. You can create small tables and design a sheet with each table having its own numbers and formulas and also using numbers from other tables.
Want to know more about how to use Numbers on your Mac? Check out this MacMost course!
Related Subjects: iWork (42 videos), Numbers (191 videos)
Not sure what you mean. Pressing Tab will move your focus to the next cell to the right. But you want it to move to a cell in another table? How would it even know which cell to jump to?
John Ireland
13 years ago
Nice presentation but what is the advantage of separate little tables as opposed to having all of the info on one spreadsheet?
Two reasons:
1. Looks nicer (makes data more layout-centered): you can move multiple linked tables around a page without being bound to a glorified electronic ledger sheet with columns of set size vertically, or rows horizontally.
2. Saves space (sizes tables of data to the data within it and allow you to do other things with the available space (other tables, charts, graphics, and other layout items).
Lindy
13 years ago
Over 2 years later people are still benefiting from your explanations. You make it so much easier to understand... and now when I go to the manual I'll know what they are talking about. ;-)
thanks so much, Lindy
Peggy
12 years ago
How to I get the manual? Do I need to download it?
I can't make a formula with numbers of cells from different tables! Why???
When I click on the cell of a different table, it stops writing the formula!
Could you help me, please?
Thanks!
Not sure what you could be doing wrong. Click on the cell, type = to start the formula, click on a cell in another table and it should insert that cell's reference.
George C
12 years ago
Is it possible to link one table to another, so that the data flows through them? Similar to linked text boxes in InDesign. I have an exercise table that I'd like to break up into months in separate tables, but keep the running totals "linked". If not, what's the best way to achieve this?
You would do that in InDesign because you are creating physical sheets of paper. No need for that here. Just keep adding more rows to the table -- make it as long as you need.
George C
12 years ago
Thanks Gary. But what I am trying to avoid is having an extremely long table, I'd rather be able to break it down by month but have all the data linked. I'm new to spreadsheets so I'm not even sure if what I am asking is possible.
I don't need to print these out, the InDesign reference was just a way to explain what I want to do within Numbers.
No, I don't think you can do that. Why not try the long table? With scrolling it shouldn't make a difference to you -- you would either have to scroll down the table or navigate to another table, but one long table would be less work. Either way, there's no way to flow one table to another.
George C
12 years ago
Thanks again Gary!
Peggy
12 years ago
This may be about the same question. We do a fish fry weekly. I want there to be several sheets, the first sheet being the running totals, i.e. income, expense # of dinners served, etc. Then I want a sheet for each month, with the totals, and I want those totals to go on the first sheet, for a running total for the year. On each sheet, I have figured out how to for example, take the total amt. of $ made, subtract it from the food bill & help and put that amount in the profit cell. I want all of that to go on my first sheet automatically that I have broken down by month
Sounds like you know what you want. So why not do it that way? Numbers is a very versatile tool. You could probably do what you want in all sorts of different ways. Your way might not be the same as my way, but it doesn't make either one wrong. You can also evolve your model over time as you learn more about using Numbers.
Peggy
12 years ago
I guess what I am asking is this. I have started my project. My first sheet is Jan-Dec., and I want all of the monthly totals to automatically go to that page. So, I have started that sheet, I just made the spreadsheet & have rows for Jan thru Dec., then a total. Then going down I have everything broken down. How much of each dinner we sold, how much profit, subtract that from food bill, etc., then it gives me a total profit. How to I get all of that to go on my first sheet automatically?
Peggy
12 years ago
that sounds so confusing, ha. Sheet number one I want to be a running total for all of the sheets following. I will have the running total sheet, then I will have 12 sheets after that, one for each month of the year. I want each of those sheet totals to go on to sheet number one.
You'll need to create a formula that is just a link to the cell on each column. So on sheet 1 you will have a cell that has =X where X is created by clicking in the cell on sheet 2. Then the next cell will be =X where X is created by clicking on the cell on sheet 3. And so on.
Tim M.
12 years ago
I have a spreadsheet with ±20 Sheets. It is a catalog of sorts. If I were to make "Sheet 1" the Master, is there a way to tie the Master Sheet in with the 20 remaining sheets, so that if I delete, resize, or relocate something on The Master Sheet, the balance of sheets also reflect the change...therby eliminating rework of 20 sheets separately?
Every sheet is laid out the same, and contain 4-6 tables each.
There's no way to do that. Your use of the word "catalog" tells me that you probably should be using a database for this, not a spreadsheet. Or, at least just combine your 20 sheets into 1, if the data represents the same kinds of things.
Tim M.
12 years ago
Thanks for the quick reply. Started out as a small project and now the 20 pages..! Filemaker will be next, but my deadline will not give me the time to "learn" the program. Stumbled upon your website for the first time today....looks like a keeper. Thanks again!
I was hoping you'd show how I could move from one cell in the first table, to a assigned cell of the next table, by using the TAB key only.
Is that possible?
Not sure what you mean. Pressing Tab will move your focus to the next cell to the right. But you want it to move to a cell in another table? How would it even know which cell to jump to?
Nice presentation but what is the advantage of separate little tables as opposed to having all of the info on one spreadsheet?
Organization. Nicer printouts. Nicer display. And the use of header and footer rows to perform calculations (See 475: http://macmost.com/adding-up-amounts-in-iwork-numbers.html)
Two reasons:
1. Looks nicer (makes data more layout-centered): you can move multiple linked tables around a page without being bound to a glorified electronic ledger sheet with columns of set size vertically, or rows horizontally.
2. Saves space (sizes tables of data to the data within it and allow you to do other things with the available space (other tables, charts, graphics, and other layout items).
Over 2 years later people are still benefiting from your explanations. You make it so much easier to understand... and now when I go to the manual I'll know what they are talking about. ;-)
thanks so much, Lindy
How to I get the manual? Do I need to download it?
Go to the Help menu while running Numbers.
I can't make a formula with numbers of cells from different tables! Why???
When I click on the cell of a different table, it stops writing the formula!
Could you help me, please?
Thanks!
Not sure what you could be doing wrong. Click on the cell, type = to start the formula, click on a cell in another table and it should insert that cell's reference.
Is it possible to link one table to another, so that the data flows through them? Similar to linked text boxes in InDesign. I have an exercise table that I'd like to break up into months in separate tables, but keep the running totals "linked". If not, what's the best way to achieve this?
You would do that in InDesign because you are creating physical sheets of paper. No need for that here. Just keep adding more rows to the table -- make it as long as you need.
Thanks Gary. But what I am trying to avoid is having an extremely long table, I'd rather be able to break it down by month but have all the data linked. I'm new to spreadsheets so I'm not even sure if what I am asking is possible.
I don't need to print these out, the InDesign reference was just a way to explain what I want to do within Numbers.
No, I don't think you can do that. Why not try the long table? With scrolling it shouldn't make a difference to you -- you would either have to scroll down the table or navigate to another table, but one long table would be less work. Either way, there's no way to flow one table to another.
Thanks again Gary!
This may be about the same question. We do a fish fry weekly. I want there to be several sheets, the first sheet being the running totals, i.e. income, expense # of dinners served, etc. Then I want a sheet for each month, with the totals, and I want those totals to go on the first sheet, for a running total for the year. On each sheet, I have figured out how to for example, take the total amt. of $ made, subtract it from the food bill & help and put that amount in the profit cell. I want all of that to go on my first sheet automatically that I have broken down by month
Sounds like you know what you want. So why not do it that way? Numbers is a very versatile tool. You could probably do what you want in all sorts of different ways. Your way might not be the same as my way, but it doesn't make either one wrong. You can also evolve your model over time as you learn more about using Numbers.
I guess what I am asking is this. I have started my project. My first sheet is Jan-Dec., and I want all of the monthly totals to automatically go to that page. So, I have started that sheet, I just made the spreadsheet & have rows for Jan thru Dec., then a total. Then going down I have everything broken down. How much of each dinner we sold, how much profit, subtract that from food bill, etc., then it gives me a total profit. How to I get all of that to go on my first sheet automatically?
that sounds so confusing, ha. Sheet number one I want to be a running total for all of the sheets following. I will have the running total sheet, then I will have 12 sheets after that, one for each month of the year. I want each of those sheet totals to go on to sheet number one.
You'll need to create a formula that is just a link to the cell on each column. So on sheet 1 you will have a cell that has =X where X is created by clicking in the cell on sheet 2. Then the next cell will be =X where X is created by clicking on the cell on sheet 3. And so on.
I have a spreadsheet with ±20 Sheets. It is a catalog of sorts. If I were to make "Sheet 1" the Master, is there a way to tie the Master Sheet in with the 20 remaining sheets, so that if I delete, resize, or relocate something on The Master Sheet, the balance of sheets also reflect the change...therby eliminating rework of 20 sheets separately?
Every sheet is laid out the same, and contain 4-6 tables each.
There's no way to do that. Your use of the word "catalog" tells me that you probably should be using a database for this, not a spreadsheet. Or, at least just combine your 20 sheets into 1, if the data represents the same kinds of things.
Thanks for the quick reply. Started out as a small project and now the 20 pages..! Filemaker will be next, but my deadline will not give me the time to "learn" the program. Stumbled upon your website for the first time today....looks like a keeper. Thanks again!