How to change a file name in formula =[Data1.xls]Sheet1!$A$1
 

I have a file Summary.xls.

There 100 files Data1.xls, Data2.xls, ... , Data100.xls
Data files are identical. All files contains a value in cell A1.

I have created a formula in Summary.xls to get a value from Data files.
=[Data1.xls]Sheet1!$A$1
=[Data2.xls]Sheet1!$A$1

So far, so good.

My question is:
How to change formula, so file name "Data1.xls" can be used indirectly.

E.g. in Summary.xls I have a column C with file names.
So, I would like to change formula =[Data1.xls]Sheet1!$A$1 to
reference file name in my column C, like
=[C1]Sheet1!$A$1
=[C2]Sheet1!$A$1
where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

How to change a file name in formula =[Data1.xls]Sheet1!$A$1
 

See:

http://www.microsoft.com/communities...5-db30b2953492
--
Gary''s Student - gsnu200724


"Viks E." wrote:

I have a file Summary.xls.

There 100 files Data1.xls, Data2.xls, ... , Data100.xls
Data files are identical. All files contains a value in cell A1.

I have created a formula in Summary.xls to get a value from Data files.
=[Data1.xls]Sheet1!$A$1
=[Data2.xls]Sheet1!$A$1

So far, so good.

My question is:
How to change formula, so file name "Data1.xls" can be used indirectly.

E.g. in Summary.xls I have a column C with file names.
So, I would like to change formula =[Data1.xls]Sheet1!$A$1 to
reference file name in my column C, like
=[C1]Sheet1!$A$1
=[C2]Sheet1!$A$1
where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

How to change a file name in formula =[Data1.xls]Sheet1!$A$1
 

Thanks a lot! It worked.

It is almost fine.
Well, it is difficult to keep 100 workbooks open at the same time...


"Gary''s Student" wrote:

See:

http://www.microsoft.com/communities...5-db30b2953492
--
Gary''s Student - gsnu200724


"Viks E." wrote:

I have a file Summary.xls.

There 100 files Data1.xls, Data2.xls, ... , Data100.xls
Data files are identical. All files contains a value in cell A1.

I have created a formula in Summary.xls to get a value from Data files.
=[Data1.xls]Sheet1!$A$1
=[Data2.xls]Sheet1!$A$1

So far, so good.

My question is:
How to change formula, so file name "Data1.xls" can be used indirectly.

E.g. in Summary.xls I have a column C with file names.
So, I would like to change formula =[Data1.xls]Sheet1!$A$1 to
reference file name in my column C, like
=[C1]Sheet1!$A$1
=[C2]Sheet1!$A$1
where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

How to change a file name in formula =[Data1.xls]Sheet1!$A$1
 

There are a couple of alternative approaches using VBA. This is possible
because all you want is a single number from each file. For example, the
code could open each data file sequentially, get the data, store it locally
and then close the data file.
--
Gary''s Student - gsnu200724


"Viks E." wrote:

Thanks a lot! It worked.

It is almost fine.
Well, it is difficult to keep 100 workbooks open at the same time...


"Gary''s Student" wrote:

See:

http://www.microsoft.com/communities...5-db30b2953492
--
Gary''s Student - gsnu200724


"Viks E." wrote:

I have a file Summary.xls.

There 100 files Data1.xls, Data2.xls, ... , Data100.xls
Data files are identical. All files contains a value in cell A1.

I have created a formula in Summary.xls to get a value from Data files.
=[Data1.xls]Sheet1!$A$1
=[Data2.xls]Sheet1!$A$1

So far, so good.

My question is:
How to change formula, so file name "Data1.xls" can be used indirectly.

E.g. in Summary.xls I have a column C with file names.
So, I would like to change formula =[Data1.xls]Sheet1!$A$1 to
reference file name in my column C, like
=[C1]Sheet1!$A$1
=[C2]Sheet1!$A$1
where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".