Ho to dynamically create links to other worksheets?

  • Thread starter Thread starter xirx
  • Start date Start date
X

xirx

Hi!

I want to access data from another Excel-File. The name
of according file is the concatenation of "data" and the
(text-) value in Column A.

This works...

A B
-- -----------------------------------------------------------
01 =index('[file01]data'!$A$A; match(...,'[file01]data'!$B$B))
02 =index('[file02]data'!$A$A; match(...,'[file02]data'!$B$B))
03 =index('[file03]data'!$A$A; match(...,'[file03]data'!$B$B))
04 =index('[file04]data'!$A$A; match(...,'[file04]data'!$B$B))

.... but I would like to replace the text-constants in the index
and match functions and let Excel calulate these. However, if
I try e.g.

=index(concatenate("'[file";A1;"]data'!$A$A; ...

the result of the concatenate yields a string indentically to what
I entered before, but Excel obviously handels a (computed) string
differently then manually entered text.

Questions:

(a) How to solve this probelm?

(b) Is there something like an EVAL in LISP or PERL that interprets
computed text (e.g. result of a concatenate) as it would have been
enteredd manually?

Thanks for your help!
 
xirx,

You can use the worksheet calculate event to place the string resulting from the formula in column B
into another column as an actual formula. For example, if you have formulas in B6:B10, this code
will place the new formulas (with links) into cells C6:C10. Copy the code, right-click the sheet
tab, select "View Code" and paste the code into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Application.EnableEvents = False
For Each myCell In Range("B6:B10")
myCell(1, 2).Formula = myCell.Text
Next myCell
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP
 
You want to use INDIRECT

It takes as an argument a complete string that is your reference.
From your example:
Click to expand...

=index(INDIRECT("'[file" & text(A1,"00") & "]data'!$A$A"), .... )

Note that you can add together strings by using the & operator. I also
wrapped your number reference with a TEXT function to make sure that it
always gives you the correct number of digits (this gives a string of
"01" instead of "1")

Hope this helps!

Alex.



Hi!

I want to access data from another Excel-File. The name
of according file is the concatenation of "data" and the
(text-) value in Column A.

This works...

A B
-- -----------------------------------------------------------
01 =index('[file01]data'!$A$A; match(...,'[file01]data'!$B$B))
02 =index('[file02]data'!$A$A; match(...,'[file02]data'!$B$B))
03 =index('[file03]data'!$A$A; match(...,'[file03]data'!$B$B))
04 =index('[file04]data'!$A$A; match(...,'[file04]data'!$B$B))

... but I would like to replace the text-constants in the index
and match functions and let Excel calulate these. However, if
I try e.g.

=index(concatenate("'[file";A1;"]data'!$A$A; ...

the result of the concatenate yields a string indentically to what
I entered before, but Excel obviously handels a (computed) string
differently then manually entered text.

Questions:

(a) How to solve this probelm?

(b) Is there something like an EVAL in LISP or PERL that interprets
computed text (e.g. result of a concatenate) as it would have been
enteredd manually?

Thanks for your help!
Click to expand...
 
No, Alex. This was my first guess. The INDIRECT function however
does only work for a single cell. But I need a range that can
be searched for the index function.

If I put the INDIRECT in cell, seperately, and if the value
of the INDIRECT is e.g. "'[file01]data'!$A$1", I can use
INDEX(A1,1): Index then deliveres the first cell of this
"one-cell-range".

If the result of the INDEX in A1 is "'[file01]data'!$A$A",
the result in the INDEX is a #REF!

Arrrrrgh! - Hell - Stupid!

The problem is the "all of that column" notation "$A:$A"!
It works fine if I use "$A1:$A65536" instead...

You want to use INDIRECT

It takes as an argument a complete string that is your reference.
From your example:
Click to expand...

=index(INDIRECT("'[file" & text(A1,"00") & "]data'!$A$A"), .... )

Note that you can add together strings by using the & operator. I also
wrapped your number reference with a TEXT function to make sure that it
always gives you the correct number of digits (this gives a string of
"01" instead of "1")

Hope this helps!

Alex.



Hi!

I want to access data from another Excel-File. The name
of according file is the concatenation of "data" and the
(text-) value in Column A.

This works...

A B
-- -----------------------------------------------------------
01 =index('[file01]data'!$A$A; match(...,'[file01]data'!$B$B))
02 =index('[file02]data'!$A$A; match(...,'[file02]data'!$B$B))
03 =index('[file03]data'!$A$A; match(...,'[file03]data'!$B$B))
04 =index('[file04]data'!$A$A; match(...,'[file04]data'!$B$B))

... but I would like to replace the text-constants in the index
and match functions and let Excel calulate these. However, if
I try e.g.

=index(concatenate("'[file";A1;"]data'!$A$A; ...

the result of the concatenate yields a string indentically to what
I entered before, but Excel obviously handels a (computed) string
differently then manually entered text.

Questions:

(a) How to solve this probelm?

(b) Is there something like an EVAL in LISP or PERL that interprets
computed text (e.g. result of a concatenate) as it would have been
enteredd manually?

Thanks for your help!
Click to expand...
Click to expand...
 
Back
Top