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

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

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

What do you mean?

--
HTH

Bob Phillips

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

"insomniux" wrote in message
ups.com...
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

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

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

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

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

You're right. I used confusing terminology. The formula in the cell
should refer to a named cell in another workbook.

The formula INDIRECT() does the trick! Only concern I have is that my
workstation does not have sufficient resources to open all 101
workbooks at once. Since the final dataset is static, I can use some
less elegant ways to copy the values to another workbook.

Thanks to all who helped

Insomniux


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

I think it's going to take some effort just to open the book consolidating
all of the data from those 100 others. Consider the number of cells
involved: 100 x 200 = 20,000 and with 20,000 linked cells trying to refresh
and calculate, could be a somewhat slow file to open.

But it still shouldn't be too difficult to code up a solution similar to
what I did earlier - just a matter of realizing that it should be workbooks
vs worksheets. If you'd like to pursue that method and wanted to 'take it
off-line' to work with, I'd be willing to try to assist. You can reach me at
HelpFrom @ jlathamsite.com (no spaces).

"insomniux" wrote:

You're right. I used confusing terminology. The formula in the cell
should refer to a named cell in another workbook.

The formula INDIRECT() does the trick! Only concern I have is that my
workstation does not have sufficient resources to open all 101
workbooks at once. Since the final dataset is static, I can use some
less elegant ways to copy the values to another workbook.

Thanks to all who helped

Insomniux


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

Not easy to do.
You need external help for this, in VBA, you can't access closed workbooks.
But you can use ADO objects in a UDF to accomplish what you want.

Try this, for example:

Function getFirstValue(wb_path As String, wb_name As String, name As String)
As Variant
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & wb_path & "\" & wb_name & ".xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
oRS.Open "Select * from " & name, oConn, adOpenStatic
getFirstValue = oRS.Fields(0).Value
oConn.Close
End Function

The first argument is the path to your files, the second the filename
(without extension) and the third is the defined name you are accessing.

I have no clue how heavy this approach is on system resources, or about its
execution speed...
--
Regards,
Luc.

"Festina Lente"


"insomniux" wrote:

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

Forgot to say that you need to add the reference to "Microsoft ActiveX Data
Objects 2.8 Library" in the VBA Tools menu...
--
Regards,
Luc.

"Festina Lente"


"PapaDos" wrote:

Not easy to do.
You need external help for this, in VBA, you can't access closed workbooks.
But you can use ADO objects in a UDF to accomplish what you want.

Try this, for example:

Function getFirstValue(wb_path As String, wb_name As String, name As String)
As Variant
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & wb_path & "\" & wb_name & ".xls;" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
oRS.Open "Select * from " & name, oConn, adOpenStatic
getFirstValue = oRS.Fields(0).Value
oConn.Close
End Function

The first argument is the path to your files, the second the filename
(without extension) and the third is the defined name you are accessing.

I have no clue how heavy this approach is on system resources, or about its
execution speed...
--
Regards,
Luc.

"Festina Lente"


"insomniux" wrote:

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