simplifying a complex formula and selecting a changing range

[sarlo00]

I have a sheet (call this sheet Sheet2!) that I've had help on in several other posts which looks like this:

Book2
	A	B	C	D	E	F
1	THEABCCOMPANY	BCA	-	ITSCustomPPO	-	0
2		BCC	ITSCustomPPO	ITSCustom	1	5
3		BCD	-	-	-	0
4		BCE	-	-	-	0
5		BCF	-	-	-	0
6		BCG	-	-	-	0
7		BCH	ITSCustom	-	2	0
8		BCJ	-	-	-	0
9		BCK	ITSCustom	-	-	3
10		BCN	ITSCustomPPO	-	-	914
11		BCO	-	-	-	0
12		BCP	ITSCustomPPO	-	-	8
13		BCQ	ITSCustomPPO	-	-	16
14		BCT	-	-	-	0
15		BCV	-	-	-	0
16		BCW	ITSCustomPPO	-	-	30
17		BCY	-	-	-	0
18		BCZ	ITSCustomPPO	-	-	532
Sheet2

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

I now need to create a sheet that summarizes this information. One formula in particular is giving me problems:

=IF(""=($A2),SUMIF(Sheet2!$C$1:$C$18,INDEX($B1:$B$94,MATCH(TRUE,($A2:$A$94<>""),0)-MATCH(TRUE,($A2:$A$94<>""),0)+1,0),Sheet2!$F$1:$F$18),IF(AND($A1=Sheet2!$A1,Sheet1!$B1=Sheet2!$C1),Sheet2!$F1,"Error"))

I want to know if there is a way to simplify this formula. It is in column C of this sheet which is Sheet1!.

Book2
	A	B	C	D
1	THEABCCOMPANY	ITSCustomPPO	1505
2		ITSCustom	3
3		-	0
4		-	0
5		-	0
6		-	0
7		-	0
8		-	0
9		-	0
10		-	0
11		-	0
12		-	0
13		-	0
14		-	0
15		-	0
16		-	0
17		-	0
18		-
19	TheDCECompany
Sheet1

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

Most Companies in Col A have only one value in Col B. The formula says, if there is a blank cell below the company name, sum the total of all the different values associated with Col B. If not, return the corresponding cell in Sheet2!.

My main problem is that the number of choices in Sheet2! column B will change monthly. I need a way to select the SUMIF range without selecting it manually.

=IF(""=($A2),SUMIF(Sheet2!$C$1:$C$18,INDEX($B1:$B$94,MATCH(TRUE,($A2:$A$94<>""),0)-MATCH(TRUE,($A2:$A$94<>""),0)+1,0),Sheet2!$F$1:$F$18),IF(AND($A1=Sheet2!$A1,Sheet1!$B1=Sheet2!$C1),Sheet2!$F1,"Error"))

I cannot figure out a way to select this range without directly referencing it.

 

[Aladin Akyurek]

Re: simplifying a complex formula and selecting a changing r

Does the column B in Sheet2 always list 18 items per company?

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

No. Some companies have only 2. Also, The ACB Company may have 20 next month or it may have 15. That is why I added the MATCH(TRUE, portion of the formula, which counts the number of rows before the next company name.

 

[Aladin Akyurek]

Re: simplifying a complex formula and selecting a changing r

No. Some companies have only 2. Also, The ACB Company may have 20 next month or it may have 15. That is why I added the MATCH(TRUE, portion of the formula, which counts the number of rows before the next company name.

Why not repeat the company name in Sheet2? Such a change in your set up would allow you to use efficient formulas for summarizing?

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

If I did repeat the company names in Sheet2, is there then a way to select the range of rows for that company? Could I then use
Sheet2!$A$1=Sheet1!$A$1:$A$200 in somw way?

 

[Aladin Akyurek]

Re: simplifying a complex formula and selecting a changing r

If I did repeat the company names in Sheet2, is there then a way to select the range of rows for that company? Could I then use
Sheet2!$A$1=Sheet1!$A$1:$A$200 in somw way?

sarlo00.xls
	A	B	C	D	E	F
1	Company	Method	Total	Start	End
2	THEABCCOMPANY	ITSCustomPPO	1505	1	18
3		ITSCustom	3	1	18
4		-	0	1	18
5		-	0	1	18
6		-	0	1	18
7		-	0	1	18
8		-	0	1	18
9		-	0	1	18
10		-	0	1	18
11		-	0	1	18
12		-	0	1	18
13		-	0	1	18
14		-	0	1	18
15		-	0	1	18
16		-	0	1	18
17		-	0	1	18
18		-	0	1	18
19		-	0	1	18
20	TheDCECompany	ITSCustom	#N/A	#N/A	#N/A
21
Sheet1

Formulas...

C2:

=IF(B2<>"-",SUMIF(INDEX(Sheet2!C:C,D2):INDEX(Sheet2!C:C,E2),B2,INDEX(Sheet2!F:F,D2):INDEX(Sheet2!F:F,E2)),0)

D2:

=MATCH(IF(A2<>"",A2,LOOKUP(REPT("z",255),$A$1:A1)),Sheet2!A:A,0)

E2:

=IF(ISNA(D2),#N/A,MATCH(IF(A2<>"",A2,LOOKUP(REPT("z",255),$A$1:A1)),Sheet2!A:A))

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

Aladin,
Would you please re-post your last table. There is an error on the page that won't allow me to view each formula on the sheet. Also,
why would you lookup a cell with 255 Zs?

 

[sarlo00]

Re: simplifying a complex formula and selecting a changing r

Would someone help me understanding the use of REPT("z",255)?

LOOKUP(REPT("z",255),A1:A20) returns the contents of the lowest row with text data in it?

MATCH(REPT("z",255),A1:A20) returns the row number of the largest text string in Column A?

Or does it return the row number of the last text string in Column A?