5

I'm using the Numbers app to make a database or catalogue of stuff. For example, a list of all wines I'll try this year. And for each column, I'll have the name of the wine, a rating, the kind of wine, and a few other items.

If the first row of my spreadsheet is a header, and the first row is 1, then every item in the spreadsheet will be off by 1. To me, this makes absolutely no sense. Can I start the spreadsheet with the first row being 0 instead of 1? Or, can I have Numbers not use a row number for the header row?

If I could name the columns, this wouldn't matter. But since Numbers names the columns A, B, C, etc, I have to use a header row to name (or label) the columns, and this leads to the first actual item being in row 2 instead of row 1... and this leads to every single sort being off by one, since the top item is a label in the header, which gets listed as being #1.

Using column A for an index won't work unless there's a way to lock the order of column A, because the index would get sorted each time any other column gets sorted.

Improve this question
9
  • 1
    All spreadsheet applications have rows that start at 1 and columns that start at A. These are references for use in calculations and do now show when printing or even when the table is not selected. Do you need numbering? If so, why not use Column A and start numbering 1 - … at the first row that is not a header? Perhaps you can explain a bit further how everything will be off by 1? Do you have a sample screenshot to share with fake data? That may help us to understand your viewpoint. Thanks!
    – bjbk
    Jan 2, 2016 at 23:31
  • Also, are you using the header for just a title area, or data field names? The row numbers do not affect the data in any way, they are just references. Cheers
    – bjbk
    Jan 2, 2016 at 23:37
  • I'm using the header for column titles. I'm not doing any calculations. As I explained in my post: "I'm using the Numbers app to make a database or catalogue of stuff. For example, a list of all wines I'll try this year. And for each column, I'll have the name of the wine, a rating, the kind of wine, and a few other items." If I use Column A for numbering, the numbers will be wrong every single time I move an item (a row) up or down the list.
    – 2oh1
    Jan 2, 2016 at 23:44
  • Here's an example of a fake spreadsheet: Rows would be Wine Name, Vinyard, Year Bottled, My Rating, Extra Info. Obviously, I'd want to put that info at the top, in a header row, as labels for each column. And obviously, I don't want that row of header labels to count as #1 when I sort.
    – 2oh1
    Jan 2, 2016 at 23:50
  • 1
    You're just going to have to get past the offset issue and using a Index in column A helps. Additionally, Numbers will recognize the first Row as a Header Row when it Sorts, so that shouldn't be an issue.
    – user3439894
    Jan 3, 2016 at 0:32

3 Answers 3

Reset to default
8

So, this is easy to solve in Excel... I don't have access to Numbers at the moment, but I'll give you the solution I'm aware of and you can confirm whether it works in Numbers like it does Excel.

You can create a column with the following formula:

=Row() - 1

This will always give you one less than the "default" row number, which is what you are looking for. It also won't be affected by sorting.

A complete reference of functions that are available in Numbers can be found HERE, including the ROW function.

enter image description here

Improve this answer
1
  • 1
    I wouldn't call a column with a formula like that an "Index". The purpose of an Index is to maintain the integrity of the record set or in this case the info in that given row especially when sorting info. If the Index doesn't sort with the rest of the record set or row then you can't maintain the integrity of the record set or row from a database perspective. I'd still have a proper Index, hide it you want, and then have a column called Item Count or something to reflect it's static info referring to the number of records/rows vs the record/row number and use that formula there instead.
    – user3439894
    Jan 3, 2016 at 17:25
0

=row()-1 works but if you insert a line above your list it will need to be adjusted for each row added (for example, =row() - 2). I recommend:

  1. Start with typing the number 1 in a column for your list separate from the numbers on the side numbers (as recommended for the row function).

  2. Then, in the box below, type = then click on the box where you put the 1 (right above) and then type + 1 and then hit enter.

  3. You can do this for each subsequent box or just click on the second box, use the copy function (ctrl c or command c if you use mac), and then use the paste function (ctrl v or command v if you use mac) in the box below. And repeat.

  4. Then you can insert lines above your list and it will keep the numbers straight.

Improve this answer
0
-1

=A2+1 works fine so long as you don't do operations such as Sort or cut and insert rows. Of course, even with =row()-1 or some variant thereof, you will have operations which will require you to fix the column using a fill down type operation. So it really depends on what type of manipulation of your data your likely to do in the future as to which is easier. If you use merged cells then the +1 method would be better since you might have two or three rows merged and the row()-1 method would key off the first row in the merged cell. Depending on how complex your header row is you could use a text box or other element which floats above the cells - and then make row one tall enough to contain your headers as well as your data - and then row 1 would be the first data row and your headers would also appear. Setting the text box formula to = would even allow you to have them be dynamic and easily referenced by pointing at the same source the text boxes point to.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .