Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and the
first row being a header row naming these columns, they all have different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd spreadsheets
by filename, in the next column I'd like to list the max value from a given
column from the spreadsheet listed in column A, the next 3 columns would do
the same, listing the max value from a column in the sheet listed in column
A.
As you can see this would involve 1600 or more cells (not including column
A) that I'd rather not fill manually. I'd be happy to fill the first column
listing the 400 other files.

Any ideas welcome.

workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and the
first row being a header row naming these columns, they all have different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd spreadsheets
by filename, in the next column I'd like to list the max value from a given
column from the spreadsheet listed in column A, the next 3 columns would do
the same, listing the max value from a column in the sheet listed in column
A.
As you can see this would involve 1600 or more cells (not including column
A) that I'd rather not fill manually. I'd be happy to fill the first column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR command would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

To add to what Harlan suggested, since you did say,
"400 spreadsheets and increasing".

You can anticipate the names of your future WBs and add them to Column A at
the outset, so that you shouldn't have to go through this procedure again.

However, you should *not* do the final "Edit & Replace" until after these
WBs *do* exist, otherwise you'll be full of #REF! errors (and that's after
you clear all the darn linkage windows).

I use this procedure to seed my main data base and create 5000 rows at a
time.
It's a great time saver.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

Thanks for the help,

The column that I want to "MAX" is in column "E" in the workbook referenced
in column "A" in the new workbook.
The sheetname is the same as the workbook (minus the ".xls").
How do I get this into the Harlan Groves formula below?
I've tried using =LEFT(A2,FIND(".",A2)-1) in column "B" which gives me the
correct sheetname, and putting Harlans formula in column "C", but it did not
work.
After some testing this is what I have now got.
="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"
Which shows thus =MAX('I:\My Documents\Excel\[Julie.xls]Julie'!$E:$E)
if I do it manually it show thus =MAX('I:\My
Documents\Excel\[Julie.xls]Julie'!$E:$E)
The manual one will do the calculation the other will not.
Where have I gone wrong?





"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

It works for me! ... BUT ... I had to change my own procedure to make it
"happen".

All my data base WBs are in XL97 at the plant, and today's test of your data
is being done in XL02 at the home office ... And ... the 2 versions *don"t*
work the same!

I'll assume you're on XL02 or better.

To start, I have this in the "new" sheet:

In A1,
I:\My Documents\Excel

In A2,
Julie.xls

In B2,
=LEFT(A2,LEN(A2)-4)
<Your formula is just as good

This formula is in K2 (as displayed in the formula bar):

="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"

And of course, as you should know by now, what you see in the formula bar
and what's displayed in the cell are 2 *different* things.

Now, digressing from Harlan's original suggestion, *don't* copy and paste
over this formula.
That's why I entered it in Column K.

Select the formula,
Right click and choose "Copy",
Right click in C2, and choose "Paste Special",
Click on "Values", then <OK,
NOW, C2 is *still* selected, AND K2 *still* has the marquee (marching ants),
Click from the menu,
<Edit <Replace
In the FindWhat box, enter
=
In the ReplaceWith box enter
=
Then click <Replace

You should now have your working formula!
Hit <Esc to remove the marquee from K2.

*** Version Difference ***

In XL02, I had to do this all in *one* sequence, while the marquee was still
around K2.

In XL97, the "Edit&Replace" could be done at *any time* after the "copy
values" was performed.

Okay, now this does work for an entire range of formulas, so that's why you
should create your "seed" formula in an out-of-the-way column, and copy down
as many rows as you anticipate that you will need in the future, and then
copy the amount of rows of the "seed" formula over into the "working" area
of the sheet, and complete the copy & edit & replace in a single sequence.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"workaholic" wrote in message
...

Thanks for the help,

The column that I want to "MAX" is in column "E" in the workbook referenced
in column "A" in the new workbook.
The sheetname is the same as the workbook (minus the ".xls").
How do I get this into the Harlan Groves formula below?
I've tried using =LEFT(A2,FIND(".",A2)-1) in column "B" which gives me the
correct sheetname, and putting Harlans formula in column "C", but it did not
work.
After some testing this is what I have now got.
="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"
Which shows thus =MAX('I:\My Documents\Excel\[Julie.xls]Julie'!$E:$E)
if I do it manually it show thus =MAX('I:\My
Documents\Excel\[Julie.xls]Julie'!$E:$E)
The manual one will do the calculation the other will not.
Where have I gone wrong?





"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

Sorry should have said XL2000.
When I do the copy it adds a ' to the start of the formula, find and
replace will not remove it or indeed find it.
Also some of my WBs have an ' in the file name, this seems to be the root of
my problem , I'll have to rename those. So glad I use "The Rename" from
http://www.herve-thouzard.com/therename.phtml so that'll take just a few
seconds. (it took long to open the program that to rename 152 files) :-)
Also some of the sheet names have a ' in them, this also is a problem. .
You can not name a sheet with the last character being an ' (xl complains
about an invalid character), but any where else in the name is OK (talk
about being consistent, NOT!). I'll be renaming those sheets as well as they
only have 1 sheet in them.






"RagDyeR" wrote in message
...
It works for me! ... BUT ... I had to change my own procedure to make it
"happen".

All my data base WBs are in XL97 at the plant, and today's test of your
data
is being done in XL02 at the home office ... And ... the 2 versions
*don"t*
work the same!

I'll assume you're on XL02 or better.

To start, I have this in the "new" sheet:

In A1,
I:\My Documents\Excel

In A2,
Julie.xls

In B2,
=LEFT(A2,LEN(A2)-4)
<Your formula is just as good

This formula is in K2 (as displayed in the formula bar):

="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"

And of course, as you should know by now, what you see in the formula bar
and what's displayed in the cell are 2 *different* things.

Now, digressing from Harlan's original suggestion, *don't* copy and paste
over this formula.
That's why I entered it in Column K.

Select the formula,
Right click and choose "Copy",
Right click in C2, and choose "Paste Special",
Click on "Values", then <OK,
NOW, C2 is *still* selected, AND K2 *still* has the marquee (marching
ants),
Click from the menu,
<Edit <Replace
In the FindWhat box, enter
=
In the ReplaceWith box enter
=
Then click <Replace

You should now have your working formula!
Hit <Esc to remove the marquee from K2.

*** Version Difference ***

In XL02, I had to do this all in *one* sequence, while the marquee was
still
around K2.

In XL97, the "Edit&Replace" could be done at *any time* after the "copy
values" was performed.

Okay, now this does work for an entire range of formulas, so that's why
you
should create your "seed" formula in an out-of-the-way column, and copy
down
as many rows as you anticipate that you will need in the future, and then
copy the amount of rows of the "seed" formula over into the "working" area
of the sheet, and complete the copy & edit & replace in a single sequence.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"workaholic" wrote in message
...

Thanks for the help,

The column that I want to "MAX" is in column "E" in the workbook
referenced
in column "A" in the new workbook.
The sheetname is the same as the workbook (minus the ".xls").
How do I get this into the Harlan Groves formula below?
I've tried using =LEFT(A2,FIND(".",A2)-1) in column "B" which gives me the
correct sheetname, and putting Harlans formula in column "C", but it did
not
work.
After some testing this is what I have now got.
="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"
Which shows thus =MAX('I:\My Documents\Excel\[Julie.xls]Julie'!$E:$E)
if I do it manually it show thus =MAX('I:\My
Documents\Excel\[Julie.xls]Julie'!$E:$E)
The manual one will do the calculation the other will not.
Where have I gone wrong?





"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns
would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

Are here we go, if the filename or sheetname has a ' in it, it doesn't work.
No ' , things work great.
Thanks for you help.
Is there a quick way of renaming the sheets, I'm currently using an macro:-

ActiveSheet.Name = "Sheet1"
ActiveWorkbook.Save
ActiveWorkbook.Close

Which works, but it would be nice to have this run as I open the workbook!!

Many Thanks



"workaholic" wrote in message
...
Sorry should have said XL2000.
When I do the copy it adds a ' to the start of the formula, find and
replace will not remove it or indeed find it.
Also some of my WBs have an ' in the file name, this seems to be the root
of
my problem , I'll have to rename those. So glad I use "The Rename" from
http://www.herve-thouzard.com/therename.phtml so that'll take just a few
seconds. (it took long to open the program that to rename 152 files) :-)
Also some of the sheet names have a ' in them, this also is a problem. .
You can not name a sheet with the last character being an ' (xl complains
about an invalid character), but any where else in the name is OK (talk
about being consistent, NOT!). I'll be renaming those sheets as well as
they
only have 1 sheet in them.






"RagDyeR" wrote in message
...
It works for me! ... BUT ... I had to change my own procedure to make it
"happen".

All my data base WBs are in XL97 at the plant, and today's test of your
data
is being done in XL02 at the home office ... And ... the 2 versions
*don"t*
work the same!

I'll assume you're on XL02 or better.

To start, I have this in the "new" sheet:

In A1,
I:\My Documents\Excel

In A2,
Julie.xls

In B2,
=LEFT(A2,LEN(A2)-4)
<Your formula is just as good

This formula is in K2 (as displayed in the formula bar):

="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"

And of course, as you should know by now, what you see in the formula
bar
and what's displayed in the cell are 2 *different* things.

Now, digressing from Harlan's original suggestion, *don't* copy and
paste
over this formula.
That's why I entered it in Column K.

Select the formula,
Right click and choose "Copy",
Right click in C2, and choose "Paste Special",
Click on "Values", then <OK,
NOW, C2 is *still* selected, AND K2 *still* has the marquee (marching
ants),
Click from the menu,
<Edit <Replace
In the FindWhat box, enter
=
In the ReplaceWith box enter
=
Then click <Replace

You should now have your working formula!
Hit <Esc to remove the marquee from K2.

*** Version Difference ***

In XL02, I had to do this all in *one* sequence, while the marquee was
still
around K2.

In XL97, the "Edit&Replace" could be done at *any time* after the "copy
values" was performed.

Okay, now this does work for an entire range of formulas, so that's why
you
should create your "seed" formula in an out-of-the-way column, and copy
down
as many rows as you anticipate that you will need in the future, and
then
copy the amount of rows of the "seed" formula over into the "working"
area
of the sheet, and complete the copy & edit & replace in a single
sequence.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"workaholic" wrote in message
...

Thanks for the help,

The column that I want to "MAX" is in column "E" in the workbook
referenced
in column "A" in the new workbook.
The sheetname is the same as the workbook (minus the ".xls").
How do I get this into the Harlan Groves formula below?
I've tried using =LEFT(A2,FIND(".",A2)-1) in column "B" which gives me
the
correct sheetname, and putting Harlans formula in column "C", but it did
not
work.
After some testing this is what I have now got.
="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"
Which shows thus =MAX('I:\My Documents\Excel\[Julie.xls]Julie'!$E:$E)
if I do it manually it show thus =MAX('I:\My
Documents\Excel\[Julie.xls]Julie'!$E:$E)
The manual one will do the calculation the other will not.
Where have I gone wrong?





"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns
and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns
would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest way
to load a list of them in col A of a new worksheet would be to use the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External Data
Import Data to load the text file into your workbook. The DIR
command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1, then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into
B3:E401.
These formulas evaluate to strings that look like formulas. To change
them into formulas as a batch, select B2:E401, copy, paste special as
values on top of B2:E401, then run Edit Replace, replacing = with =.
That may seem a do-nothing operation, but it'll effectively enter the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

All Sheets renamed.
created a button to the macro.
Selected all the WBs hit enter and clicked away on the button.
20 seconds later all done.
Will work on RagDyeR's instructions tomorrow, with new eyes without
matchsticks!!
That's all folks, going to bed now.
Once again thanks



"workaholic" wrote in message
...
Are here we go, if the filename or sheetname has a ' in it, it doesn't
work.
No ' , things work great.
Thanks for you help.
Is there a quick way of renaming the sheets, I'm currently using an
macro:-

ActiveSheet.Name = "Sheet1"
ActiveWorkbook.Save
ActiveWorkbook.Close

Which works, but it would be nice to have this run as I open the
workbook!!

Many Thanks



"workaholic" wrote in message
...
Sorry should have said XL2000.
When I do the copy it adds a ' to the start of the formula, find and
replace will not remove it or indeed find it.
Also some of my WBs have an ' in the file name, this seems to be the
root
of
my problem , I'll have to rename those. So glad I use "The Rename" from
http://www.herve-thouzard.com/therename.phtml so that'll take just a
few
seconds. (it took long to open the program that to rename 152 files) :-)
Also some of the sheet names have a ' in them, this also is a problem. .
You can not name a sheet with the last character being an ' (xl
complains
about an invalid character), but any where else in the name is OK (talk
about being consistent, NOT!). I'll be renaming those sheets as well as
they
only have 1 sheet in them.






"RagDyeR" wrote in message
...
It works for me! ... BUT ... I had to change my own procedure to make
it
"happen".

All my data base WBs are in XL97 at the plant, and today's test of
your
data
is being done in XL02 at the home office ... And ... the 2 versions
*don"t*
work the same!

I'll assume you're on XL02 or better.

To start, I have this in the "new" sheet:

In A1,
I:\My Documents\Excel

In A2,
Julie.xls

In B2,
=LEFT(A2,LEN(A2)-4)
<Your formula is just as good

This formula is in K2 (as displayed in the formula bar):

="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"

And of course, as you should know by now, what you see in the formula
bar
and what's displayed in the cell are 2 *different* things.

Now, digressing from Harlan's original suggestion, *don't* copy and
paste
over this formula.
That's why I entered it in Column K.

Select the formula,
Right click and choose "Copy",
Right click in C2, and choose "Paste Special",
Click on "Values", then <OK,
NOW, C2 is *still* selected, AND K2 *still* has the marquee (marching
ants),
Click from the menu,
<Edit <Replace
In the FindWhat box, enter
=
In the ReplaceWith box enter
=
Then click <Replace

You should now have your working formula!
Hit <Esc to remove the marquee from K2.

*** Version Difference ***

In XL02, I had to do this all in *one* sequence, while the marquee was
still
around K2.

In XL97, the "Edit&Replace" could be done at *any time* after the
"copy
values" was performed.

Okay, now this does work for an entire range of formulas, so that's
why
you
should create your "seed" formula in an out-of-the-way column, and
copy
down
as many rows as you anticipate that you will need in the future, and
then
copy the amount of rows of the "seed" formula over into the "working"
area
of the sheet, and complete the copy & edit & replace in a single
sequence.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"workaholic" wrote in message
...

Thanks for the help,

The column that I want to "MAX" is in column "E" in the workbook
referenced
in column "A" in the new workbook.
The sheetname is the same as the workbook (minus the ".xls").
How do I get this into the Harlan Groves formula below?
I've tried using =LEFT(A2,FIND(".",A2)-1) in column "B" which gives me
the
correct sheetname, and putting Harlans formula in column "C", but it
did
not
work.
After some testing this is what I have now got.
="=MAX('"&$A$1&"\["&$A2&"]"&B2&"'!"&"$E:$E"&")"
Which shows thus =MAX('I:\My Documents\Excel\[Julie.xls]Julie'!$E:$E)
if I do it manually it show thus =MAX('I:\My
Documents\Excel\[Julie.xls]Julie'!$E:$E)
The manual one will do the calculation the other will not.
Where have I gone wrong?





"Harlan Grove" wrote in message
oups.com...
workaholic wrote...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns
and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from
a
given
column from the spreadsheet listed in column A, the next 3 columns
would
do
the same, listing the max value from a column in the sheet listed
in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the
first
column
listing the 400 other files.

If these 400-odd files are all in the same directory, the simplest
way
to load a list of them in col A of a new worksheet would be to use
the
Windows DIR command in a console window to produce a text file
containing the list of filenames, then use Data Import External
Data
Import Data to load the text file into your workbook. The DIR
command
would look like

DIR X:\Y\Z\*.xls /b listing.prn

The resulting list will contain the base filenames. If you need full
pathname, you can use formulas to add the drive/directory path.

If the drive directory path were in cell A1, the base filenames in
A2:A401, the columns of interest in row 1 starting with cell B1,
then
enter the following formula in cell B2.

="=MAX('"&$A$1&"\["&$A2&"]<sheetname_here'!$"&B$1&":$"&B$1&")"

Fill B2 right into C2:E2, then select B2:E2 and fill down into
B3:E401.
These formulas evaluate to strings that look like formulas. To
change
them into formulas as a batch, select B2:E401, copy, paste special
as
values on top of B2:E401, then run Edit Replace, replacing = with
=.
That may seem a do-nothing operation, but it'll effectively enter
the
strings as formulas in each cell.

Note: if most of the other workbooks are closed, it'll take a fair
amount of time for all the formulas to calculate. It may take a VERY
LONG TIME.

All Done, many thanks to you both.



"workaholic" wrote in message
...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and the
first row being a header row naming these columns, they all have different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

Any ideas welcome.

Glad you got it squared away.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"workaholic" wrote in message
...
All Done, many thanks to you both.



"workaholic" wrote in message
...
Hi I am in need of help:-

What I have:-
Over 400 spreadsheets and increasing, all of which have 24 columns and
the
first row being a header row naming these columns, they all have
different
amount of rows.

What I'd like to have:-
Another sheet with five columns. 1st column listing the 400 odd
spreadsheets
by filename, in the next column I'd like to list the max value from a
given
column from the spreadsheet listed in column A, the next 3 columns would
do
the same, listing the max value from a column in the sheet listed in
column
A.
As you can see this would involve 1600 or more cells (not including
column
A) that I'd rather not fill manually. I'd be happy to fill the first
column
listing the 400 other files.

Any ideas welcome.