I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

You are correct in your assumption. Cells A1:L1 represent 12 months Jan- Dec
2009, cell M1 is the total and N1 is a year to date total that is linked to
WS2 which is the exact same spreadsheet however it is the 2008 schedule. N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I hope
this gives enough information to do a formula. Thanks again for your help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

Hi Brian

maybe
=SUM(WS2!$A$1:INDEX(WS2!$A1:$L1,COUNT($A1:$L1)))

--
Regards
Roger Govier

"Brian" wrote in message
...
You are correct in your assumption. Cells A1:L1 represent 12 months Jan-
Dec
2009, cell M1 is the total and N1 is a year to date total that is linked
to
WS2 which is the exact same spreadsheet however it is the 2008 schedule.
N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I
hope
this gives enough information to do a formula. Thanks again for your help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would
allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to avoid
the
nesting restriction? The first part of the formula is below and it
needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12
times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian

Give this a try.

=SUM((INDIRECT("WS2!A"&ROW()&":"&
LOOKUP(COUNT(A2:L2),{1,2,3,4,5,6,7,8,9,10,11,12},
{"A","B","C","D","E","F","G","H","I","J","K"})&ROW ())))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

You are correct in your assumption. Cells A1:L1 represent 12 months Jan- Dec
2009, cell M1 is the total and N1 is a year to date total that is linked to
WS2 which is the exact same spreadsheet however it is the 2008 schedule. N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I hope
this gives enough information to do a formula. Thanks again for your help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

Or even this simplifies it a little mo

=SUM((INDIRECT("WS2!A"&ROW()&":"&
CHOOSE(COUNT(A2:L2),"A","B","C","D","E","F","G","H ","I","J","K")&
ROW())))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

Give this a try.

=SUM((INDIRECT("WS2!A"&ROW()&":"&
LOOKUP(COUNT(A2:L2),{1,2,3,4,5,6,7,8,9,10,11,12},
{"A","B","C","D","E","F","G","H","I","J","K"})&ROW ())))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

You are correct in your assumption. Cells A1:L1 represent 12 months Jan- Dec
2009, cell M1 is the total and N1 is a year to date total that is linked to
WS2 which is the exact same spreadsheet however it is the 2008 schedule. N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I hope
this gives enough information to do a formula. Thanks again for your help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

Roger, your formula is very close. It is adding the first three columns in
WS2 but it is adding those cells even though the values of A1:C1 were 0. It
should not add any values in WS2 unless there is a value 0 in A1 or B1, or
C1 etc.

Roger, your second formula gave the message "a value used in the formula is
of the wrong data type".

Thanks again for trying to help with this.
--
Brian


"Roger Govier" wrote:

Hi Brian

maybe
=SUM(WS2!$A$1:INDEX(WS2!$A1:$L1,COUNT($A1:$L1)))

--
Regards
Roger Govier

"Brian" wrote in message
...
You are correct in your assumption. Cells A1:L1 represent 12 months Jan-
Dec
2009, cell M1 is the total and N1 is a year to date total that is linked
to
WS2 which is the exact same spreadsheet however it is the 2008 schedule.
N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2. I
hope
this gives enough information to do a formula. Thanks again for your help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would
allow
you to identify the first (last?) cell with data and create the sum range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to avoid
the
nesting restriction? The first part of the formula is below and it
needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12
times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian

Hi Brian

then this should do it
=SUM(ws2!$A$1:INDEX(ws2!$A1:$L1,COUNTIF($A1:$L1," "&0)))

Not sure what you mean by my second formula. I only posted one.
--
Regards
Roger Govier

"Brian" wrote in message
...
Roger, your formula is very close. It is adding the first three columns in
WS2 but it is adding those cells even though the values of A1:C1 were 0.
It
should not add any values in WS2 unless there is a value 0 in A1 or B1,
or
C1 etc.

Roger, your second formula gave the message "a value used in the formula
is
of the wrong data type".

Thanks again for trying to help with this.
--
Brian


"Roger Govier" wrote:

Hi Brian

maybe
=SUM(WS2!$A$1:INDEX(WS2!$A1:$L1,COUNT($A1:$L1)))

--
Regards
Roger Govier

"Brian" wrote in message
...
You are correct in your assumption. Cells A1:L1 represent 12 months
Jan-
Dec
2009, cell M1 is the total and N1 is a year to date total that is
linked
to
WS2 which is the exact same spreadsheet however it is the 2008
schedule.
N1
should add the months from 2008 if the same month(s) in 2009 have been
entered. Example, if Jan and Feb 2009 have values $1000 and March to
Dec
are 0 then N1 should be the sum of Jan and Feb of 2008 (A1+B1) of WS2.
I
hope
this gives enough information to do a formula. Thanks again for your
help.
--
Brian


"JBeaucaire" wrote:

Yes, it's possible. It looks like you're trying to adjust your sum
range
based on how may cells have data in the row you're working on. True?

Then perhaps some sort of =ADDRESS function or INDIRECT function would
allow
you to identify the first (last?) cell with data and create the sum
range
from W2 in a single step rather than 8 individual checks.

Would need more info about the values in the cells being checked and
the
complete set of "results" to make sure we get it right.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to
avoid
the
nesting restriction? The first part of the formula is below and it
needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12
times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian

Roger,
As it turns out your latest formula now adds all months (Jan-Dec) in WS2
even if the value in each of the cells in (A1:L1) are 0. I think you are very
close to getting this one. I appreciate your help again.
--
Brian


"Brian" wrote:

I am using the following formula but it will involve nesting 12 functions. I
understand the limit is 7. Is there a way to write the formula to avoid the
nesting restriction? The first part of the formula is below and it needs to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)... (and
so on),0)))))

Thanks for your help.
--
Brian

Hi Brian

I am confused.
At first I thought the cells in row 1 of your sheet would be empty if the
month hadn't been reached.
That is why I first used Count.
Obviously, there must be a formula in there returning 0 until the month is
reached.

The present formula is counting whether cells in A1:L1 are greater than 0.
What is in those cells. It is a very small value which is being displayed as
0?

What result do you get for
=COUNTIF(A1:L1,""&0)
and what do you get for
=COUNTIF(A1:L1,""&0.000000000000001)

There must be something in those cells for the formula to include all 12
cells from WS2
--
Regards
Roger Govier

"Brian" wrote in message
...
Roger,
As it turns out your latest formula now adds all months (Jan-Dec) in WS2
even if the value in each of the cells in (A1:L1) are 0. I think you are
very
close to getting this one. I appreciate your help again.
--
Brian


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to avoid
the
nesting restriction? The first part of the formula is below and it needs
to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian

Roger,

I have entered the number $0 in all cells in row 1 so we are starting with a
cell with the value $0 and not an empty cell. Both of your latest
suggestions give the same value. Is it possible that I can email you the
spreadsheet? Thanks again.
--
Brian


"Roger Govier" wrote:

Hi Brian

I am confused.
At first I thought the cells in row 1 of your sheet would be empty if the
month hadn't been reached.
That is why I first used Count.
Obviously, there must be a formula in there returning 0 until the month is
reached.

The present formula is counting whether cells in A1:L1 are greater than 0.
What is in those cells. It is a very small value which is being displayed as
0?

What result do you get for
=COUNTIF(A1:L1,""&0)
and what do you get for
=COUNTIF(A1:L1,""&0.000000000000001)

There must be something in those cells for the formula to include all 12
cells from WS2
--
Regards
Roger Govier

"Brian" wrote in message
...
Roger,
As it turns out your latest formula now adds all months (Jan-Dec) in WS2
even if the value in each of the cells in (A1:L1) are 0. I think you are
very
close to getting this one. I appreciate your help again.
--
Brian


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to avoid
the
nesting restriction? The first part of the formula is below and it needs
to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12 times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian

Yes.
roger at technology4u dot co dot uk
Change the at and dots to make valid address

--
Regards
Roger Govier

"Brian" wrote in message
...
Roger,

I have entered the number $0 in all cells in row 1 so we are starting with
a
cell with the value $0 and not an empty cell. Both of your latest
suggestions give the same value. Is it possible that I can email you the
spreadsheet? Thanks again.
--
Brian


"Roger Govier" wrote:

Hi Brian

I am confused.
At first I thought the cells in row 1 of your sheet would be empty if the
month hadn't been reached.
That is why I first used Count.
Obviously, there must be a formula in there returning 0 until the month
is
reached.

The present formula is counting whether cells in A1:L1 are greater than
0.
What is in those cells. It is a very small value which is being displayed
as
0?

What result do you get for
=COUNTIF(A1:L1,""&0)
and what do you get for
=COUNTIF(A1:L1,""&0.000000000000001)

There must be something in those cells for the formula to include all 12
cells from WS2
--
Regards
Roger Govier

"Brian" wrote in message
...
Roger,
As it turns out your latest formula now adds all months (Jan-Dec) in
WS2
even if the value in each of the cells in (A1:L1) are 0. I think you
are
very
close to getting this one. I appreciate your help again.
--
Brian


"Brian" wrote:

I am using the following formula but it will involve nesting 12
functions. I
understand the limit is 7. Is there a way to write the formula to
avoid
the
nesting restriction? The first part of the formula is below and it
needs
to
continue from sum(A1:K1) to sum(A1:J1) to sum(A1:I1) and so on 12
times
until it reaches A1.

=if(L10,'WS2'!M1,if(K10,sum('WS2'!A1:K1),if(J10 ,sum('WS2"!A1:J1)...
(and
so on),0)))))

Thanks for your help.
--
Brian