index to a range of cells

[OEMK]

I am trying to build a set of worksheets, either in the same or separate
workbooks, which would allow me to do the below:

A) One worksheet has a column that contains a separate bibliographic
reference (text) in each row. It also has a number of other columns
containing various bits of data about each reference.

B) In the same worksheet, another column contains an "index" number
corresponding to the position (i.e. row) of a specific bibliographic
reference in the entire list of references. Every time I add a new reference
and sort the whole worksheet, this index should be updated.

C) In a separate worksheet, I will have listed in a column a number or
series of numbers which will correspond (linked with), the "index" number(s)
of the first worksheet. These should automatically be updated when new
bibilio references are added and resorted.

Questions:

1) The item (C) is fairly easy except that I am not sure how I can link
multiple "index" numbers (separated by a comma) into a single cell. Any ideas?

2) How can I accomplish (B) above? I can't set up the 'index' column as an
array formula because I can't then add a new bilbiliographic reference to the
range. I would think that there should be some type of 'index function' which
would allow me to refer to an existing range of biblio references and return
the number of the reference in the whole range. It should also then update it
if I add a new reference and resort it alphabetically. I can't seem to find a
function that would do exactly this.
Any ideas???


Thanks,
oemk

 

[Frank Kabel]

Hi
this is something you can't achieve in Excel with formulas. You need a
kind of unique key for referencing (much easier to achieve in a
database).
So you either enter your ID manually and sort it together with the
other data or use a database