Dear Bill,

Thanks for the tip, but that's not going to work.
But what if I don't need to have the data in different files (workbooks) and
I can even have it just in different worksheets, in the same workbook. That
is possible and will prevent having to open the data files all the time.
Suppose also that all the data worksheets have the same layout, so that the
same type of data is in the same cell location. I still need a way to, in the
cell function, make the source worksheet name a function of another cell. In
the example below, I'd like to insert only the sheet name on the first row
and get the data in the following rows from certain coordinates of the
worksheet specified in the first row.


Sheet name CC64 CC70 <- introduce only this

Fermentation code SC_CC64 SC_CC70 <- get this from the
specified worksheet
Strain CEN.PK 113-7d CBS288
Operator Andre Andre
Date start 23-Jun-05 28-Oct-05
Date end 29-Jun-05 1-Nov-05
.... ... ...


Is there a way to do that?
Thanks in advance,

Andre

If the source cells are in the same workbook then INDIRECT will work
(since by definition the workbook containing the cells is open).

=INDIRECT("'" & CellContainingSheetName & "'!$A$1")
for example


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup