Yes, I thought of something using INDIRECT, but this would necessitate
all the workbooks being open.

You could build up a string that represents the formula you would like
to apply and then make use of Evaluate in VBA (often you would have a
little UDF called EVAL to be able to use it from the worksheet) - a
Google search for Eval will give you lots of relevant past postings.

Hope this helps.

Pete

JLatham (removethis) wrote:
Confirm that the various worksheets you need to reference are all in the same
workbook or are they in different workbooks? You spoke of forms earlier and
I was thinking of something in the same workbook, but I realize now that your
Form entries that I'd taken as workSHEETs have names as FORM001.xls - with
the .xls indicating a different workBOOK.

Someone such as Bob Phillips may come up with a solution to #2, but I don't
know how to do it. Any formula you'd build up within the cell would not be a
formula, but would be a string representation of one. Might be able to work
something out using 2 tables, one like you've described and a second pointing
in to it using INDIRECT() to get to the data in the other workbooks. I'll
think on it some more.

"insomniux" wrote:

Mm. yes, this is the solution for generation of the fixed formulas
where each cell refers to the right cell in the data-sheet. I was
planning to use this method if the generic method is not possible.

With the generic method I mean: a formula that takes an argument from
the first column and an argument from the first row which determin the
filename and cellname in which too look for the value which should be
returned (like in my example under OPTION 2, see below).


JLatham (removethis) schreef:

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