Do you mean something like this? This would put 'Entry###' references in
columns, 200 rows of them with each column referring to different Form###.
Reverse the use of the loop counters in the .Offset() to reverse layout.

But assumes exactly 100 sheets, each with exactly 200 named ranges in them.

Sub FillFormulas()
Dim SL As Integer ' SheetName Loop
Dim CL As Integer ' CellName Loop
Dim anyFormula As String

For SL = 1 To 100
For CL = 1 To 200
anyFormula = "=Form" & _
String(3 - Len(Trim(Str(SL))), "0") _
& Trim(Str(SL)) & _
"!Entry" & String(3 - Len(Trim(Str(CL))), "0") _
& Trim(Str(CL))
'choose cell where you want
'formulas to start as base address
Range("A1").Offset(CL - 1, SL - 1).Formula = anyFormula
Next
Next
End SubSub FillFormulas()
Dim SL As Integer ' SheetName Loop
Dim CL As Integer ' CellName Loop
Dim anyFormula As String

For SL = 1 To 100
For CL = 1 To 200
anyFormula = "=Form" & _
String(3 - Len(Trim(Str(SL))), "0") _
& Trim(Str(SL)) & _
"!Entry" & String(3 - Len(Trim(Str(CL))), "0") _
& Trim(Str(CL))
'choose cell where you want
'formulas to start as base address
Range("A1").Offset(CL - 1, SL - 1).Formula = anyFormula
Next
Next
End Sub

"insomniux" wrote:

OK, I had missed the .Formula property.

What about the generic solution? Any chance?

Bob Phillips schreef:

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