12 Replies Latest reply: Jan 18, 2012 6:52 AM by RobbieSnr
RobbieSnr Level 1 (0 points)

I'm working with an Excel spreadsheet that I had produced in Windows. There I was able to allot specific names to particular cells in a table and then reference these cells in another table using these names rather than the normal cell identifiers. For example I could have a table showing my salary payments throughout the year along with the amount of tax deducted and name the cells showing the totals for the year 2011 'Salary11' and 'Tax11'. Then is a new table checking that the correct tax had been deducted I could use these names. The advantage of this was that when I came to do the next year's calculations I could copy

the calculation table and change the names to 'Salary12' and 'Tax12', which would relate to the figures in a new table for 2012.

 

It doesn't seem that this is possible in Numbers or is it?


iMac, Mac OS X (10.7), MacBook Pro, iPhone4, iPad2
  • Badunit Level 6 (11,615 points)

    You can't do it the Excel way.  In Numbers if you have column and row headers and you have data in those headers and you have "use headers as cell names as references" turned on in Preferences, you can reference the cell by name. Example

     

    Column A is a header column

    Row 1 is a header row

    Cell B1 has the word "alpha"

    Cell A2 has the word "beta"

    Cell B2 has the number 5

    In cell B3 I type =alpha beta

    The result in cell B3 is 5

    I could also have typed "=beta alpha"

     

    A column reference would be =alpha, which is the same as B:B

    A row reference would be =beta, which is the same as 2:2

     

    There are some limitations, though. If you have the same word, such as "alpha", in both the header column and header row, it won't make a named reference to that cell.

  • Level 8 (41,790 points)

    Hello Badunit

     

     

    Badunit wrote:

    There are some limitations, though. If you have the same word, such as "alpha", in both the header column and header row, it won't make a named reference to that cell.

    You may put"al" or  "santa" in the header row and "pha" or "claus" in the header column.

    This way the reference will be [al pha] or [santa claus]

     

    Yvan KOENIG (VALLAURIS, France) dimanche 15 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • RobbieSnr Level 1 (0 points)

    Many thanks for this. I think I can see how to use this system - I'll have to do some rearranging of my current worksheets but it shouldn't be too difficult.

     

    I'm not quite sure though what Yvan is getting at.

     

    Regards

    Eric.

  • Level 8 (41,790 points)

    May be a way to say that Santa Claus failed to give us a new version with added features

     

    Yvan KOENIG (VALLAURIS, France) dimanche 15 janvier 2012

    iMac 21”5, i7, 2.8 GHz, 12 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

    My iDisk is : http://public.me.com/koenigyvan

  • Jerrold Green1 Level 7 (29,960 points)

    Robbie,

     

    There's a way to accomplish your goal without resorting to using Header names for your cell references. Here's an example of using the INDIRECT function to grab a location reference detail from a cell entry...

     

    Screen Shot 2012-01-15 at 2.34.13 pm.png

     

    Regards,

     

    Jerry

  • RobbieSnr Level 1 (0 points)

    Many thanks Jerrold for this idea.

     

    I think my best solution will be to use row names along with the INDIRECT function you mention. I say this because the totals I want to use will not always be in the same rows because the number of the constituent parts for each total can vary. I suppose though that the totals need not be directly under the parts they are summing, but could be shown elsewhere in consecutive rows (or copied there). However using row names would help me ensure I copied over the correct totals to the right place in the calculation table.

     

    Regards

    Robbie

  • Jerrold Green1 Level 7 (29,960 points)

    Robbie,

     

    Take a cue from my use of a separate table for summary data.

     

    Jerry

  • RobbieSnr Level 1 (0 points)

    I'm unfamiliar with the concept of more than one table in a sheet. Currently in Excel I have two worksheets that have totals that feed into a third worksheet that does the calculation I want to perform. I've been experimenting with tables in Numbers and as you suggest I can use seperate tables in the one sheet to replace what I did in Excel.

     

    Thanks again

    Robbie

  • Jerrold Green1 Level 7 (29,960 points)

    Robbie,

     

    It may be helpful to think of your small Numbers tables as Named Areas or Ranges, or whatever they're called, in an Excel sheet. Each small table gets a name, just as you can name a range in Excel, and reference the cells in that range by range name.

     

    Jerry

  • RobbieSnr Level 1 (0 points)

    Jerry

     

    Thanks, I'm gradually getting the hang of this. I have one problem though that I think I'll just have to live with.

     

    I have someone who goes over my calculations, and she is still with Windows and uses Excel. I see that if I export a Numbers sheet with several tables in it as an Excel file each table is exported as a separate worksheet. That's fine if I avoid using Header names, it correctly shows the links between worksheets. However if I use header names it produces a link that Excel doesn't understand - if the cell referenced is in what is, say, B6 it's shown as B6:B6 B2:B20 where B2 and B20 are the first and last cells in that column! I've also had a problem with an INDIRECT entry where not surprisingly it can't change the name of a sheet or table in the function.

     

    Regards

    Robbie

     

    PS Sorry for incorrectly addressing you as Jerrold previously!

  • Jerrold Green1 Level 7 (29,960 points)

    Robbie,

     

    I answer to Jerry or Jerrold - no problem.

     

    I hate to see anyone struggling with Numbers trying to make something that will work in Excel. It just seems easier to use Excel for that and Numbers for other, less collaborative and maybe more personally creative, work.

     

    Have you explored the free clones such as LibreOffice?

     

    Jerry

  • RobbieSnr Level 1 (0 points)

    Jerry

     

    Yes, I think you're right - I should just abandon trying to convert this application from Excel to Numbers; it's the only one that I collaborate on. It's not really a problem to keep the Excel version - I run WIndows XP in a VM under Parallels. I had just thought it would be nice if I could run this competely under OSX.

     

    I already have OpenOffice but not really liked it as an alternative to MS Word, or now Pages. I suppose I'd got so used to the MS Office Suite that I found it difficult to switch over and find the bits I was used to. I did use Access a lot and had added a lot of functions and specialised reports to it that couldn't be used in OO.

     

    hadn't come across LibreOffice and was unaware of the change to the 'ownership' of OO. Just been reading up on this - makes interesting reading.

     

    Robbie