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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Viks E. - 23 May 2007 10:41 GMT

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".

Reply to this Message

Gary''s Student - 25 May 2007 19:17 GMT

See:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=gsnu20
0723+indirect&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet
.functions&p=1&tid=531f0228-e4b7-497e-8ac5-db30b2953492

Signature

Gary''s Student - gsnu200724

> I have a file Summary.xls.
>
[quoted text clipped - 16 lines]
> =[C2]Sheet1!$A$1
> where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

Reply to this Message

Viks E. - 25 May 2007 21:15 GMT

Thanks a lot! It worked.

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

> See:
>
[quoted text clipped - 20 lines]
> > =[C2]Sheet1!$A$1
> > where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

Reply to this Message

Gary''s Student - 26 May 2007 11:17 GMT

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.

Signature

Gary''s Student - gsnu200724

> Thanks a lot! It worked.
>
[quoted text clipped - 25 lines]
> > > =[C2]Sheet1!$A$1
> > > where C1 contains string "Data1.xls" and C2 contains string "Data2.xls".

Reply to this Message