Results 1 to 13 of 13
Thread: FORMULA DRIVEN REPORT

20091019, 13:28 #1
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
Is it possible to have a report formula driven as shown in the attachment. The data is on another worksheet in the workbook. The key for the report is the week number. The data is showing the customers on the rows and the week # and the Month # on the column.
Any thoughts?

20091019, 14:16 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Using your example book for the week (in B6):
Code:=INDEX('DATA SCHEDULE'!$C$13:$BB$17,MATCH(REPORT!A6,'DATA SCHEDULE'!$A$13:$A$17,0),REPORT!$B$2)
Code:=SUM(OFFSET('DATA SCHEDULE'!$C$13,MATCH(A6,'DATA SCHEDULE'!$A$13:$A$17,0)1,0,1,REPORT!$B$2))
I'm working on the MTD....

20091019, 14:43 #3
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
And for month to date (in F6):
Code:=SUM(OFFSET('DATA SCHEDULE'!$C$13,MATCH(REPORT!A6,'DATA SCHEDULE'!$A$13:$A$17,0)1,MATCH(INDEX('DATA SCHEDULE'!$C$9:$BC$9,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)),'DATA SCHEDULE'!$C$9:$BB$9,0)1,1,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)MATCH(INDEX('DATA SCHEDULE'!$C$9:$BC$9,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)),'DATA SCHEDULE'!$C$9:$BB$9,0)+1))

20091019, 16:05 #4
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
Excellent,
where do i find more about "OFFSET" "MATCH" & " INDEX"

20091019, 16:11 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts

20091022, 16:57 #6
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
Interesting. Never used but now I want to understand the hows and whys to see how powerful this triumpherate is.
Thanks again

20100105, 20:54 #7
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
I studied the offset, index, match functions and needless to say they are great for dynamic formulas.
However, I do not understand the YTD FORMULA. the data reference is $C$13 in the Data Schedule. The row is created under the match function which is super.
The following is what I do not understand. The formula calls for 1 for column  WHY?. the height is a zero (0) which is ok. The WIDTH is 1  WHY? and the final expression of REPORT $B$2 ( counts the width of the area).
I would appreciate any light you can cast on these questions.
Thank you

20100105, 21:45 #8
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
The 1 is not the width, it is subtracting 1 from the result of the Match function. For Bill the Match function results in 1. For the offset function, the anchor is already in Bill's row so we subtract 1 to remain in the same row
Here is a color coded explaination:
=SUM(OFFSET('DATA SCHEDULE'!$C$13,MATCH(A6,'DATA SCHEDULE'!$A$13:$A$17,0)1,0,1,REPORT!$B$2))
=SUM(Offset(anchor of 'DATA SCHEDULE'!$C$13,move down the result of the Match function minus 1 rows ,stay in the same column, range to sum is 1 row high, by the value in REPORT!$B$2 columns wide))

20100105, 22:32 #9
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
I will review with my logic . Thanks for the quick come back.
gnite

20100111, 06:49 #10
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
I have been working on dissecting the "Month To Date" formula. I start losing it at the first Match(Index). If possible could you separate the formula and describe the functions. I see importance and studied each one of these three functions ( Index, Match, Offset) as well as Indirect, but the formula is a bit overwhelming. I would appreciate any assistance of teachings you have to offer.
Thank you in advance for all your assistance.
'=SUM(OFFSET('DATA SCHEDULE'!$C$13,MATCH(REPORT!A6,'DATA SCHEDULE'!$A$13:$A$17,0)1,MATCH(INDEX('DATA SCHEDULE'!$C$9:$BC$9,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)),'DATA SCHEDULE'!$C$9:$BB$9,0)1,1,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)MATCH(INDEX('DATA SCHEDULE'!$C$9:$BC$9,MATCH($B$2,'DATA SCHEDULE'!$C$11:$BB$11,0)),'DATA SCHEDULE'!$C$9:$BB$9,0)+1))

20100111, 10:08 #11
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
I've changed the sheet names from Report to r and data schedule to ds in the formulas.
Breakdown of Offset
Offset(Anchor,number of rows to move,number of columns to move,height of array,width of array)
SUM(
OFFSET(DS!$C$13,MATCH('r'!A6,DS!$A$13:$A$17,0)1,MATCH(INDEX(DS!$C$9:$BC$9,MATCH($B$2,DS!$C$11:$BB$ 11,0)),DS!$C$9:$BB$9,0)1,
1,MATCH($B$2,DS!$C$11:$BB$11,0)MATCH(INDEX(DS!$C$9:$BC$9,MATCH($B$2,DS!$C$11:$BB$11,0)),DS!$C$9:$BB$9,0)+1))
Anchor is easy since it's a cell reference.
number of rows to move (for the start of the summation)
Match(Person, in person list,exact match)  1 rows  Bill is in row#1 of the list  1 row since the anchor is in that row.
MATCH('r'!A6,DS!$A$13:$A$17,0)1
number of columns to move (for the start of the summation)
With this MTD formula. You need to determine which month number is associated with the week provided. The match in red determines the column of the week in question in the list, The index statement is: Index(Bring back the value, at this location in the array) Week 7 for example is in column 7 and the corresponding month would be 2 . The Match( ) surrounding the index takes the index's value and finds the loscation for the first week in month 2. :Match(The value from the index, in this array) subtract 1. to get the first column for the offset.
MATCH(INDEX(DS!$C$9:$BC$9,MATCH($B$2,DS!$C$11:$BB$11,0)),DS!$C$9:$BB$9,0)1
height of array
1  no explanation needed.
width of array
Finds the position of the week in question in the array (position 7) minus same formula as the columns to move except 1 is added so that all weeks for that month are added. For week 7 it would be 75+1 or 3 since weeks 5, 6 and 7 need to be added together
MATCH($B$2,DS!$C$11:$BB$11,0)MATCH(INDEX(DS!$C$9:$BC$9,MATCH($B$2,DS!$C$11:$BB$ 11,0)),DS!$C$9:$BB$9,0)+1))

20100202, 12:23 #12
 Join Date
 Jun 2005
 Posts
 386
 Thanks
 3
 Thanked 0 Times in 0 Posts
I have been reviewing this formula and I understand the basics of it. However, I do not understand how " DS!$C$9:$BB$9,0 " fits in to the overall formula intn the first and second parts. Any further insight is very much appreciated.
Thank you.

20100202, 12:34 #13
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
That is the location of the cells where the first part of the Match function. The ,0 should not have been green.
MATCH(INDEX(DS!$C$9:$BC$9,MATCH($B$2,DS!$C$11:$BB$11,0)),DS!$C$9:$BB$9,0)
Match(ThisValue,InThisArray,TypeOfMatch)