Formula Too Long Issue

M

Max

mldancing said:
How do you fix it if a formula is too long?
Click to expand...

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.
 
M

mldancing

=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_Pivot!$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_Pivot!$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_Pivot!$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.
 
T

T. Valko

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
 
T

Toppers

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)*(OFFSET(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
 
M

mldancing

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!
 
T

Toppers

Look at HELP in OFFSET function.

To break down my Formula:

(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$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
 
M

mldancing

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.
 
T

Toppers

This ....

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

Another option:

Define a range (Insert=>Name=>Define==>Name: e.g. Myformula =>Refers to:
....formula below

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


Then your long formula becomes

=IF(myformula=0,"",myformula)

HTH
 
T

Toppers

It worked OK when I tested it.

If you want send w/sheet to toppers at NOSPAMjohntopley.fsnet.co.uk (remove
NOSPAM!)
 
Top