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.