How to read from rows and columns to link to workbooks and sheets in excel?

Question

Hi,

I am currently analysiing a large dataset of life histories of fossils and need some help.

I have a table that is arranged thus:
along the top I have a row with species names in them (i.e. Cupuladria biporosa"). To the left I have a column with numbers 1-60 that related to fossil locations.

I have a lot of other files that contain all the data I want to colate into this table. They are organised per species (i.e. Cupuladria biporosa.xls). Within each file the individual sheets represent the fossil locations (1-60).

What I want to do for each position I want the equation to give me the value of single cell from the corresponding species file and corresponding location number.

How can this be done without linking each and every cell by hand (there are +30 species and 60 locations!!!)...

Many thanks
Aaron

Answer 1

Aaron,



Let's say in B1 of your table, you have "Cupuladria biporosa". Also, let's say the 1 through 60 down the left hand side start at A2=1, A3=2, etc. You can then build your reference around this, and use the INDIRECT function to use the reference.



Starting at B2, you'd have this formula:

=INDIRECT("'["&B$1&".xls]" & "1'!" & "AB" & $A2+3)



This will build the reference '[Cupuladria biporosa.xls]1'!AB4



Then the INDIRECT function will get the value at that cell reference.



If you have the sheet name 1 in a cell, you can also use that in the formula.

.

Answer 2

Generally if you format one cell with the links that you need for that cell then fill the neighboring cells by copy and paste then you should be in good shape.

Answer 3

I don't quite understand your example, but I think the answer is to use some combination of the VLOOKUP and INDEX functions.