$B1:$J100 should have been $B$1:$J$100 in both formulas.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Niek Otten" wrote in message ...
| It's best to keep it simple; don't put everything in one formula, do it step by step.
| If possible, remove the spaces in filenames and sheet names.
| Do the lookup tables really go down as far as row 65536? If not, use the used area as argument for your VLOOKUP.
|
| I started on a new sheet, with the value to be looked up in A1.
| In B1:
| =VLOOKUP($A$1,[File1.xls]Sheet1!$B1:$J100,6,FALSE)
| in C1:
| =IF(ISNA(B1),0,B1)
|
| And so down the columns for each file. Now you can sum C1:C12
|
| It is not necessary to spell out all the filenames if you change the formula in B1 to:
|
| =VLOOKUP($A$1,INDIRECT("[File"&ROW()&".xls]Sheet1!$B1:$J100"),6,FALSE)
|
| Now you can copy the formula down to row 12 and the filenames will adjust automatically. The files need to be open in Excel,
| though.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
| "JW73" wrote in message ...
|| I'm having some difficulty getting the nested formula below to work the way I
|| want it to, compiling data from 5 separate files, for now - it'll be 12 at
|| year end. All of the files are set up similarly - same columns are in each.
|| I'm totalling up all column 6 data and dividing by all column 4 data to build
|| a year to date percentage. It works great for references that have data in
|| every monthly file - unfortunately, not all data reference points exist in
|| all monthly files, so for months where the reference point doesn't exist,
|| VLOOKUP returns a #N/A value. For some reason, SUM will not ignore this
|| non-numeric value, so it returns a #N/A result as well. Is there a way to get
|| the SUM formula (or possible another formula) to ignore the VLOOKUP #N/As so
|| that I can build an percentage based on the data available? Or, would there
|| be a way to get VLOOKUP to return a 0 result if it finds no data?
||
||
|| =SUM(VLOOKUP(A1,'[File 1]Sheet 1'!$B$1:$J$65536,6,FALSE),(VLOOKUP(A1,'[File
|| 2]Sheet 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 3]Sheet
|| 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 4]Sheet
|| 1'!$B$1:$J$65536,6,FALSE)),(VLOOKUP(A1,'[File 5]Sheet
|| 1'!$B$1:$J$65536,6,FALSE)))/SUM(VLOOKUP(A1,'[File 1]Sheet
|| 1'!$B$1:$J$65536,4,FALSE),(VLOOKUP(A1,'[File 2]Sheet
|| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 3]Sheet
|| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 4]Sheet
|| 1'!$B$1:$J$65536,4,FALSE)),(VLOOKUP(A1,'[File 5]Sheet
|| 1'!$B$1:$J$65536,4,FALSE)))
|
|