Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    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?
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    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)
    For the YTD ( in D6):
    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....

  3. #3
    5 Star Lounger
    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))

  4. #4
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Excellent,

    where do i find more about "OFFSET" "MATCH" & " INDEX"

  5. #5

  6. #6
    3 Star Lounger
    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

  7. #7
    3 Star Lounger
    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

  8. #8
    5 Star Lounger
    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))

  9. #9
    3 Star Lounger
    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

  10. #10
    3 Star Lounger
    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 over-whelming. 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))

  11. #11
    5 Star Lounger
    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 7-5+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))

  12. #12
    3 Star Lounger
    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.

  13. #13
    5 Star Lounger
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •