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)))

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)))

I've heard that 2007 has a shorter function but for 2003 and lower, you need
to do something like...

if(isna(VLOOKUP(A1,'[File 2]Sheet
1'!$B$1:$J$65536,6,FALSE)),0,VLOOKUP(A1,'[File 2]Sheet
1'!$B$1:$J$65536,6,FALSE))

FYI, you can change $B$1:$J$65536 to $B:$J.

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"JW73" wrote:

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)))

$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)))
|
|

Thanks for the tips, I'll test them out later this evening - I've never
worked with the IFNA function before, sounds like that will clear it up.. on
the machine I've got the system set up on, I'm using $B:$J for the range, my
excel2007 at home reformatted it as $J$65535 - I'll be using the system on a
mix of 2003 and 2007 systems, so it has to be compatible with the older
format.

"JW73" wrote:

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)))

It's ISNA not IFNA however
for good spreadsheet design and much speedier
result you should go with Niek's solution using multiple
smaller formulas and a summary formula. That way it
will be much easier to audit.

--


Regards,


Peo Sjoblom

"JW73" wrote in message
...
Thanks for the tips, I'll test them out later this evening - I've never
worked with the IFNA function before, sounds like that will clear it up..
on
the machine I've got the system set up on, I'm using $B:$J for the range,
my
excel2007 at home reformatted it as $J$65535 - I'll be using the system on
a
mix of 2003 and 2007 systems, so it has to be compatible with the older
format.

"JW73" wrote:

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)))