Setting a specific cell identifier

Question

Level 1

78 points

Setting a specific cell identifier

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

Posted on Jan 15, 2012 6:40 AM

Reply

Answer 1

Best reply

Badunit

Level 7

26,000 points

Jan 15, 2012 7:14 AM in response to RobbieSnr

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.

Reply

Answer 2

KOENIG Yvan

Level 8

41,807 points

Jan 15, 2012 8:37 AM in response to Badunit

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

Reply

Answer 3

RobbieSnr Author

Level 1

78 points

Jan 15, 2012 10:06 AM in response to Badunit

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.

Reply

Answer 4

KOENIG Yvan

Level 8

41,807 points

Jan 15, 2012 10:26 AM in response to RobbieSnr

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

Reply

Answer 5

Jan 15, 2012 11:37 AM in response to RobbieSnr

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...

Regards,

Jerry

Reply

Answer 6

RobbieSnr Author

Level 1

78 points

Jan 15, 2012 4:37 PM in response to Jerrold Green1

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

Reply

Answer 7

Jan 16, 2012 3:58 PM in response to RobbieSnr

Robbie,

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

Jerry

Reply

Answer 8

RobbieSnr Author

Level 1

78 points

Jan 17, 2012 10:07 AM in response to Jerrold Green1

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

Reply

Answer 9

Jan 17, 2012 10:13 AM in response to RobbieSnr

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

Reply

Answer 10

RobbieSnr Author

Level 1

78 points

Jan 18, 2012 5:09 AM in response to Jerrold Green1

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!

Reply

Answer 11

Jan 18, 2012 5:48 AM in response to RobbieSnr

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

Reply

Answer 12

RobbieSnr Author

Level 1

78 points

Jan 18, 2012 6:52 AM in response to Jerrold Green1

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

Reply