Simple enough

Worksheets("Sheet1").Range("A1").Formula = "=FORM001.xls!Entry001"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"insomniux" wrote in message
oups.com...
Hi,
I have approx 100 excel sheets with exactly the same structure, each
containing a form with entered data. Each form holds approx 200
data-items in named cells (eg. Entry001, Entry002, ..). Now I want to
make a separate excel sheet with rows pointing to all the data-items in
all the forms. This will result in a sheet with approxe 100x200 cells
containing references.
I see two possible options, but I do not know how to implement them.

OPTION 1
Is it possible to use a VBA macro to populate the sheet with all the
formulas? Each formula will have the form of:

=FORM001.xls!Entry001,
=FORM001.xls!Entry002,
=FORM001.xls!Entry003,
..
=FORM002.xls!Entry001,
...
=FORM100.xls!Entry200,

The question is how to put a formula (not a string) in a cell (not the
iteration through the numbers).

OPTION 2
In the first column I make a list with numbers (001-100), in the first
row I make a list with cellnames (Entry001-Entry200). Is it possible to
make a generic formula (eg in B2) which refers to the cell in the first
column and the cell in the first row like:
=ReferenceToFileWithNameFrom(A2)!ReferenceToCellNa meIn(B1)

This solution would have my preference, but I do not know if excel
offers this possibility.

Thanks

Insomniux