How do you fix it if a formula is too long?

Post the formula so possible solutions can be offered.

"mldancing" wrote:

How do you fix it if a formula is too long?

Depends. Post it

--
Don Guillett
SalesAid Software

"mldancing" wrote in message
...
How do you fix it if a formula is too long?

=INDEX(Detail_Pivot!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
10",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
11",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
12",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
13",Detail_Pivot!$B$26:$N$26,0))

I want to include if(isna) in each of the index so that if it's blank, it
will read as 0 and will still add up.

Thanks for your help.




"Don Guillett" wrote:

Depends. Post it

--
Don Guillett
SalesAid Software

"mldancing" wrote in message
...
How do you fix it if a formula is too long?

I want to include if(isna) in each of the index so that if it's blank

What's blank?

The only things that will cause #N/A errors is if Kimberlee Madsen can't be
found or the headers Week xx can't be found or you have #N/A errors in the
table itself.

Need more detail

Biff

"mldancing" wrote in message
...
=INDEX(Detail_Pivot!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
10",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
11",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
12",Detail_Pivot!$B$26:$N$26,0))+INDEX(Detail_Pivo t!$B$27:$N$50,MATCH("Kimberlee
Madsen",Detail_Pivot!$A$27:$A$50,0),MATCH("Week
13",Detail_Pivot!$B$26:$N$26,0))

I want to include if(isna) in each of the index so that if it's blank, it
will read as 0 and will still add up.

Thanks for your help.




"Don Guillett" wrote:

Depends. Post it

--
Don Guillett
SalesAid Software

"mldancing" wrote in message
...
How do you fix it if a formula is too long?

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

From your original formula I have assumed you want 4 weeks worth of data: if
so, try:

=SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4)))

where $A$1 has name e.g "Kimberlee Madsen"
$B$1 has first week (of the 4) e.g "Week 10"

You can add error logic as required.

As Max pointed out, you could shorten the formula by using named ranges.

HTH


"Max" wrote:

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

This formula works! Thank you so much HTH. Can you point me to a link where I
can learn more about offset in the formula. I still don't quite get the logic
of it.

Thanks Max for the pointers too!



"Toppers" wrote:

From your original formula I have assumed you want 4 weeks worth of data: if
so, try:

=SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4)))

where $A$1 has name e.g "Kimberlee Madsen"
$B$1 has first week (of the 4) e.g "Week 10"

You can add error logic as required.

As Max pointed out, you could shorten the formula by using named ranges.

HTH


"Max" wrote:

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Look at HELP in OFFSET function.

To break down my Formula:

(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4)))

Detail_Pivot!$B$27 == this positions the start (reference cell) at your
first data cell

0 == just positions on the row determined by the
SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name

MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding
to the WEEK and we subtract one as its an offset from column B; if the match
is B26, MATCH will return 1 and as our reference cell is B27 we want n offset
of 0 ...hence subtract one.

24 == is the number of rows your data occupies (rows 27 to 50)

4 ==is the number of columns we want to sum

HTH

"mldancing" wrote:

This formula works! Thank you so much HTH. Can you point me to a link where I
can learn more about offset in the formula. I still don't quite get the logic
of it.

Thanks Max for the pointers too!



"Toppers" wrote:

From your original formula I have assumed you want 4 weeks worth of data: if
so, try:

=SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4)))

where $A$1 has name e.g "Kimberlee Madsen"
$B$1 has first week (of the 4) e.g "Week 10"

You can add error logic as required.

As Max pointed out, you could shorten the formula by using named ranges.

HTH


"Max" wrote:

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

One last question on this:

So with this formula, what should the formula be so that it will return
blanks instead of displaying it
as 0 (zero)? I tried several ways but couldn't get it to work. Please help
again.

Thank you.



"Toppers" wrote:

Look at HELP in OFFSET function.

To break down my Formula:

(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Piv ot!$B$26:$N$26,0)-1,24,4)))

Detail_Pivot!$B$27 == this positions the start (reference cell) at your
first data cell

0 == just positions on the row determined by the
SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name

MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1== finds the column corresponding
to the WEEK and we subtract one as its an offset from column B; if the match
is B26, MATCH will return 1 and as our reference cell is B27 we want n offset
of 0 ...hence subtract one.

24 == is the number of rows your data occupies (rows 27 to 50)

4 ==is the number of columns we want to sum

HTH

"mldancing" wrote:

This formula works! Thank you so much HTH. Can you point me to a link where I
can learn more about offset in the formula. I still don't quite get the logic
of it.

Thanks Max for the pointers too!



"Toppers" wrote:

From your original formula I have assumed you want 4 weeks worth of data: if
so, try:

=SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSE T(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$ 26:$N$26,0)-1,24,4)))

where $A$1 has name e.g "Kimberlee Madsen"
$B$1 has first week (of the 4) e.g "Week 10"

You can add error logic as required.

As Max pointed out, you could shorten the formula by using named ranges.

HTH


"Max" wrote:

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---