I'm having trouble finding a way to condense my formula which searches up to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
.....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew

I would look into using the VLOOKUP function to search on that many
conditions. Look in Excel's help for its syntax.
--
Brevity is the soul of wit.


"Andy777" wrote:

I'm having trouble finding a way to condense my formula which searches up to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew

You might consider using a range of helper cells (12 or whatever) containing
your individual MATCH/VLOOKUP formulas and then just one SUM formula to add
them together.

hth
Vaya con Dios,
Chuck, CABGx3



hth
Vaya con Dios,
Chuck, CABGx3



"Andy777" wrote:

I'm having trouble finding a way to condense my formula which searches up to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(" 2:40"))&"'!A:A"),A1,INDIRECT("'Sheet"&ROW(INDIRECT ("2:40"))&"'!F:F")))

Sums column F on sheets 2 through 40 if column A in sheets 2 through 40
contains the value in A1.

Biff

"Andy777" wrote in message
...
I'm having trouble finding a way to condense my formula which searches up
to
40 sheets for an entry in the first column of that sheet (if it exists),
which matches a value in column A on Sheet1, and then returns the value in
column 6 of that sheet.
An extract of my formula (which works fine) is below:

=IF(ISERROR(MATCH($A1,Sheet2!$A:$A,0)),0,VLOOKUP($ A1,Sheet2!$A:$IV,6,FALSE))+IF(ISERROR(MATCH($A1,Sh eet3!$A:$A,0)),0,VLOOKUP($A1,Sheet3!$A:$IV,6,FALSE ))+
....

My problem is that after about twelve ...+IF(... functions I run out of
space in the formula bar to keep entering them. Does anyone know of a way
to
condense or contract these IF functions so my formula is much shorter? Any
help would be greatly appreciated.

Thanks,
Andrew

Thanks for that guys, really helpful. I think I'm gonna go with the helper
cells to get me a few subtotals to add together for the final total.
Andrew

You're welcome Andy............glad one of the suggestions worked for you.

Vaya con Dios,
Chuck, CABGx3



"Andy777" wrote:

Thanks for that guys, really helpful. I think I'm gonna go with the helper
cells to get me a few subtotals to add together for the final total.
Andrew