index to a range of cells
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
So you either enter your ID manually and sort it together with the
other data or use a database
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
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.
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.