Can I reference & sort 'chapter-page' in a cell?

Hello!

I'm hoping one of you can help me straight-away. I am working in Excel 2003 (created in E2010) on a collaborative review of a large scientific document. There will be several other people contributing to the spreadsheet.

I would like each entry to reference the chapter and page of the original document. Preferably, that reference would be something like 7-317 for Chapter 7, p. 317 (each of the pages of the 11 chapters of document we're reviewing begins with page 1 so page numbers by themselves are not unique. One caveat is that there are several appendixes. I'd like to designate them similarly, but with an "A" to denote the Appendix number (e.g. A3-23, for Appendix 3, page 23).

So that each collaborator does not have to insert rows so that all our comments are sequential, I would like to be able to sort the comments according to the 'chapter-page' numbers so that when submitted for evaluation, they are sequential.

So far, I have been unable to locate any tips to help me with this. I realize I could separate the Chapter and Page into separate columns, but I'm not sure if I sorted on those two columns simultaneously that the proper chapter-page combinations would be retained.

Is there a formatting I can use so that I can have reviewers designate a Chapter-Page in a single cell so that those cells can be used to sort a large group of comments into sequential order?

Thanks in advance for the help!

Originally Posted by davegugg

Just go to Data -> Sort with all your info selected.

Dave, thanks for the suggestion. Maybe I'm missing something in your response, but the cells in question are not really numbers in that there's a [chapter] [dash] [page] combination for each comment, as described above.

The challenge seems to be in selecting the formatting for those chapter-page cells. If you go through the list of formatting options you get General, Number, Currency, Accounting, Date, Time, ... Text, Special, and Custom. None of those selections seem to let me enter the chapter-page combination, keep it intact, AND allow me to sort by it later.

I found after my original post that I could enter [chapter] [space] [page] and the cells would sort accordingly. This might be the way to go, but it will be easier for my collaborators (I think) to populate those cells just as the numbers appear on each page of document.

Thanks for trying Dave - you've discovered my problem (and my frustration w/ Excel). The form idea is a possibility, but the plot thickens, in several ways.

1. I've never created a form and though I'm sure it would be good experience for me, I need to use my time in other ways as we're already behind on this project.

2. As I mentioned, I have several collaborators on this project. We are going to try a web-based collaboration utility that allows us to work directly in Excel, but share the same document (little bit of a red flag given our circumstances, but I've read good things about it).

3. None of us are terribly experienced Excel users. Simplicity and efficiency will be crucial to our success. The spreadsheet we create is primarily comments, but it will be a lot of work and likely end up being a fairly large spreadsheet. I am trying to make data entry as easy and intuitive as possible to reduce time spent cleaning it up later.

I do appreciate your prompt suggestions! If you come up with anything else, please let me know.

Thanks shg and Dave (again). I thought of splitting chapter and page into separate columns then sorting the comments using both those columns. But then I started thinking, since many of the 11 chapters to be reviewed have several hundred pages, would a sort, even by multiple columns, necessarily keep the proper chapter-page-comment associations?

Put another way, will the rows remain intact if the page numbers are 'unhitched' from their respective chapters and ALL page numbers are put in the sort mix together? Though I've never dealt with terribly complicated spreadsheets before, this is the first time I've had to think really hard about exactly how the sort function works in Excel. It's fairly intuitive on the surface, but it gets a little fuzzy in my mind how it works in a situation like this.

I forgot (until it jolted me awake this morning) that there are Section numbers in the document we're reviewing also! So theoretically, now that a column for them has been added to the spreadsheet, they could help to keep the proper associations if they are used as the first sort field. If chapter numbers are used as the second field and page number as the third field, it seems like it should all hang together if they're all formatted as numbers, right?

Thanks again for your help.