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.