Pointer-like behaviour in Excel possible to build formulas?

[Kris Coverdale]

Hi,

Apologies if a similar question has been posted previously - I've
searched the archives with no luck...

The summary: is I'm trying to use a formula of the following form:
=[somefunction(filenames!A1)]Sheet1!$B$5 where the 'filenames'
worksheet contains a list of filenames to be opened, so that when I
cut and paste this into a column, cell B5 from sheet1 from a number of
different excel files is pasted into the column. Kind of like using a
pointer I guess - I want to know if this is possible and what function
I could use to do it.

The background is:
I've been given several 1000 excel files that my company sent out as a
questionnaire to people. What I have to do is collate this
information into a single worksheet / table. The data is always laid
out the same in each file, the only thing that changes is the
filename. However the data is set out 'prettily' rather than tabular,
so i can't simply pull out the data I want.

I was thinking of picking out the info I want using formualas of the
form: =[file1.xls]Sheet1!$B$5 =[file1.xls]Sheet1!$C$5 etc. and
then changing the filename in each row of data to access the next file
in turn. As I have 20 cells worth of data to pick out, that means
typing 20 * several thousand filenames in...

I was wondering if I can enter each filename only once in another
worksheet somewhere and using a reference to that sheet to fill in the
filenames. So I create a worksheet called filenames. cell A1
contains filename1, B1 contains filename2 etc.

Then in my original table I would use a formula of the following form
to construct the formula: =[somefunction(filenames!A1)]Sheet1!$B$5

Is this possible in Excel 2000 and what function could I use to do
this? It feels pretty similar to using pointers when programming, but
I'm not sure if the theory holds water in Excel...

Many thanks for anyone who managed to stick with me this long.
Kris

 

[Charles Williams]

Hi Kris,

I would handle this by:

putting the several 1000 excel files in a directory somewhere

write a VBA sub to

open each excel file in the directory in turn (use DIR to get the filenames)
copy the information from the 20 cells & paste the values into a workbook
close the excel file


hth
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Frank Kabel]

Hi Kirs,

you can use the function INDIRECT. e.g.
=INDIRECT("["&A1&"]Table1!A1")
there A1 stores your filename (e.g. "Test.xls")

HTH
Frank

 

[Harlan Grove]

you can use the function INDIRECT. e.g.
=INDIRECT("["&A1&"]Table1!A1")
there A1 stores your filename (e.g. "Test.xls")

This only works when the files are open, and if the OP really does have 1000 or
more files, this is a grossly impractical approach even if it were possible to
open 1000 or more files at the same time.

 

[Harlan Grove]

...
...

putting the several 1000 excel files in a directory somewhere

write a VBA sub to

open each excel file in the directory in turn (use DIR to get the filenames)
copy the information from the 20 cells & paste the values into a workbook
close the excel file

...

Not necessary to put all the files in the same directory. Putting them all in
subdirectories below a common parent directory would be sufficient, then in a
console session run the command

dir parent-directory\*.xls /s/b/-p > parent-directory\filelist.txt

Also not necessary to open these files or use VBA. Just import the file listing
created by the previous command, and use it to build external references as text
formulas. The results would be text strings. Once done, copy the range of text
formulas and paste-special as values on top of itself. Then with the entire
range of these text strings selected, Edit > Replace = with =. This effectively
enters all of these text strings as formulas in their respective cells. Do this
just before going home because it'll take a long time to pull in values from
1000+ files.

 

[Charles Williams]

There are problems with some Excel versions when you have links to 1000s of
files, see for instance

http://support.microsoft.com/default.aspx?scid=kb;en-us;248201&Product=xlw2K

and also there are problems with large numbers of links:

It might be wise to work in steps, which would be quite easy to do with
Harlan's solution.

(personally I dont use external links if its avoidable ...)

regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Harlan Grove]

There are problems with some Excel versions when you have links to 1000s of
files, see for instance

http://support.microsoft.com/default.aspx?scid=kb;en-us;248201&Product=xlw2K

and also there are problems with large numbers of links:

It might be wise to work in steps, which would be quite easy to do with
Harlan's solution.

...

You're right.

A hybrid approach would likely be best. Using text formulas evaluating to
external reference formulas that themselves refer to a single cell containing
the 'current' workbook pathname, then copying the range containing these
formulas along with the workbook pathname and pasting as values into the next
available block in a table, then using Edit > Replace = with = in this newly
copied block to make the formulas live external reference links to evaluate
them, then converting the block to values. Wrap this inside a loop that changes
the workbook pathname on each iteration.

 

[Kris Coverdale]

you can use the function INDIRECT. e.g.
=INDIRECT("["&A1&"]Table1!A1")
there A1 stores your filename (e.g. "Test.xls")

Hi,

Thanks to both Frank & Clive for the solutions. Clive's works - I had
a meddle with VBA for the first time in ages (which was good) and got
it going that way.

Big thanks to Frank though - that was exactly the bit of functionality
i've been looking for in Excel for ages (i've even played with
indirect a few times) without being able to quite suss it out. So
that's great - I can now sort out all those pesky Excel problems that
have been bugging me for ages!

Thanks muchly for both your help.
Kris

 

[Kris Coverdale]

A hybrid approach would likely be best.

Hi,

Thanks Harlan - I've now got something based on your suggestions
running - haven't had chance to load test it with 1000s of files yet
(we're apparently still waiting for the surveys to be returned, but
think it should stand up fine now.

Cheers
Kris