1. 

   jdppep
   

   Member
   Joined: Nov '11
   Posts: 10

   1. I am tracking census information in horizontal dynamic ranges on an Input! sheet. The information is being compared among two organizations. Changing the layout is not prudent in this case.

   The date is in row Input!3:3 and the values are in row Input!5:5 for organization 1 and Input!32:32 for organization 2. A separate Graphs! sheet contains several line graphs which I would like to automatically populate based on start and end date cells. Start date is located in cell Graphs!C:3 and end date is located in cell Graphs!F:3.

   I'm nearly certain this requires some combination of OFFSET, COUNTA and MATCH functions in named ranges.

   2. A separate Weekly Comp! sheet needs to sum the previous 7 days (by row) based on an input date from the Input! sheet.

   The date is in row Input!3:3 and the values are in row Input!5:5 for organization 1 and Input!32:32 for organization 2. Column A contains labels. the "Date" cell is located in Weekly Comp!C:5.

   Thank you for your assistance!!!

   Posted 1 year ago #
2. 

   Hui
   

   Excel Ninja
   Joined: Jul '09
   Posts: 5,037

   Jdppep

   Firstly welcome to the Chandoo.org Forums

   Q1.

   Ive used 2 Named Formula for the dates
   StartDate: =Graph!C3
   EndDate: =Graph!F3

   For your Date Range
   =OFFSET(Input!A3,0,MATCH(StartDate,Input!3:3,0),1,MATCH(EndDate,Input!3:3,0)-MATCH(StartDate,Input!3:3,0)+1)

   For your Organisation 1 Range
   =OFFSET(Input!A5,0,MATCH(StartDate,Input!3:3,0),1,MATCH(EndDate,Input!3:3,0)-MATCH(StartDate,Input!3:3,0)+1)

   For your Organisation 2 Range
   =OFFSET(Input!A32,0,MATCH(StartDate,Input!3:3,0),1,MATCH(EndDate,Input!3:3,0)-MATCH(StartDate,Input!3:3,0)+1)

   Q2.

   Do you mean Sum the previous 7 days by Column ?

   If you do try this:
   WeeklyDate: ='Weekly Comp'!C5

   =SUM(OFFSET(Input!A5,0,MATCH(WeeklyDate-7,Input!3:3,0),1,7))

   Posted 1 year ago #
3. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,274

   First, excellent write-up of your workbook layout and problem. =)

   Now, onto question 1:
   We'll need to define 3 named ranges. Let's tackle the date range first. We'll call this range, "DateAxis". Are the dates in Input!3:3 spaced 1 day apart, or are there gaps? If one day apart, we can use this:
   =OFFSET(Input!$A$3,0,MATCH(Graphs!$C$3,Input!$3:$3,0)-1,1,Graphs!$F$3-Graphs!$C$3)

   If there are gaps:
   =OFFSET(Input!$A$3,0,MATCH(Graphs!$C$3,Input!$3:$3,0)-1,1,MATCH(Graphs!$F$3,Input!$3:$3)-MATCH(Graphs!$C$3,Input!$3:$3))

   Now, the next 2 named Ranges are much easier. "Org1" range is defined as:
   =OFFSET(DateAxis,2,0)
   "Org2" is defined as:
   =OFFSET(DateAxis,29,0)

   These are now ready to be plugged into chart #1.

   For question #2, sum for org1:
   =SUM(OFFSET(Input!$A$5,0,MATCH('Weekly Comp'!$C$5,Input!$3:$3)-1,1,-7))
   sum for org2:
   =SUM(OFFSET(Input!$A$32,0,MATCH('Weekly Comp'!$C$5,Input!$3:$3)-1,1,-7))

   Posted 1 year ago #
4. 

   jdppep
   

   Member
   Joined: Nov '11
   Posts: 10

   Hui,

   Thank you for the quick response and welcome! Unfortunately, I have either entered this incorrectly or I am still missing something.

   Can I send you the abbreviated document for you to advise?

   Posted 1 year ago #
5. 

   jdppep
   

   Member
   Joined: Nov '11
   Posts: 10

   Luke,

   Unfortunately, this is not quite the answer either.

   Can I send you the abbreviated document for you to advise?

   Posted 1 year ago #
6. 

   jdppep
   

   Member
   Joined: Nov '11
   Posts: 10

   I did forget to mention that the start date and end date cells are meant to be drop-down lists.

   Thank you!

   Posted 1 year ago #
7. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,274

   The drop down list will not affect the formula.
   Can you elaborate on why neither of these are the answer? Stating "it didn't work" doesn't help us very much.

   Also, a key question, were the dates on the INPUT sheet 1 day apart, 1 month, random, etc? And, are the dates selected in drop downs limited to the dates given on the INPUT sheet?

   Posted 1 year ago #
8. 

   jdppep
   

   Member
   Joined: Nov '11
   Posts: 10

   The dates on the Input! sheet are 1 day apart. The dates in the drop-downs are limited to the dates given in the Input! sheet.

   The graph is displaying the data range 1 day behind. For example, I choose an end date of 11/3/2011, and the graph will shift my range forward one day. Instead of 11/1's data being represented on 11/1, it is shown on 11/2.

   Posted 1 year ago #
9. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,274

   Ah, I see. Need to just add 1 to the width to account for the inclusion of end points.

   =OFFSET(Input!$A$3,0,MATCH(Graphs!$C$3,Input!$3:$3,0)-1,1,Graphs!$F$3-Graphs!$C$3+1)

   Posted 1 year ago #
10. 

    jdppep
    

    Member
    Joined: Nov '11
    Posts: 10

    That included the end point in the drop-down list; however, the data is still shifted 1 date beyond the date it should match up with.

    Posted 1 year ago #
11. 

    Luke M
    

    Excel Ninja
    Joined: Nov '10
    Posts: 4,274

    This formula:
    =OFFSET(DateAxis,2,0)
    simply states to shift 2 rows down. I'm not sure how the columns are getting messed up...is there anything else "unusual" in your Input sheet? Extra text somewhere maybe?

    Posted 1 year ago #
12. 

    jdppep
    

    Member
    Joined: Nov '11
    Posts: 10

    Luke,

    Unfortunately, I have not found any extra text anywhere.

    It is returning an invalid formula error when I use the formula you have provided to accomodate for gaps:

    =OFFSET(Input!$A$3,0,MATCH(Graphs!$L$2,Input!$3:$3,0)-1,1,MATCH(Graphs!$L$2,Input!$3:$3)-MATCH(Graphs!$O$2,Input!$3:$3))

    (I have changed the drop-down cells to startdate: L2 and enddate: O2)

    The selection is not matching the graphs. If I select a more current start date, the data ranges shift forward. The end date only allows it to display more dates inbetween.

    Posted 1 year ago #
13. 

    Luke M
    

    Excel Ninja
    Joined: Nov '10
    Posts: 4,274

    Hmm, even more curious...
    Since you don't have gaps, go ahead and change the formula back to:
    =OFFSET(Input!$A$3,0,MATCH(Graphs!$C$3,Input!$3:$3,0)-1,1,Graphs!$F$3-Graphs!$C$3+1)

    Now, within the Define Name dialogue, when you select each of the 3 names (you may need to click on formula) is XL highlighting the correct section of the workbook?

    Posted 1 year ago #
14. 

    Hui
    

    Excel Ninja
    Joined: Jul '09
    Posts: 5,037

    Can you please post your data

    Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

    I suspect that your dates are text and not dates

    Posted 1 year ago #
15. 

    fashinefa
    

    Member
    Joined: Nov '11
    Posts: 6

    Sounds great, I think you've done pretty well for a first time. Next topic, I would really suggest more on traffic generation. Excited to hear the next one. Burberry shoulder bags

    Posted 1 year ago #
16. 

    jdppep
    

    Member
    Joined: Nov '11
    Posts: 10

    I apologize for the delay, was on vacation!

    http://hotfile.com/dl/135219515/a7b8672/Daily_Census_Shared_Sample.xlsx.html

    I have another question as I proceed further into this assignment. I would like to automate the sheet 'YTD COMP FY11'. Is there a way to automate the summation of months as data is input into the 'Input' sheet?

    Thanks!!

    Posted 1 year ago #
17. 

    jdppep
    

    Member
    Joined: Nov '11
    Posts: 10

    Luke,

    To answer your previous question, yes Excel is highlighting the correct selection.

    Please review the hotfile Hui requested. I remain close, but no cigar!

    Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics » Excel for Beginners - Tutorials » Excel Formula Examples » Array Formulas » Excel Charts » Pivot Tables » Project Management using Excel » Excel Dashboards » Excel VBA » Advanced Excel

Our Products Full Training Programs » Excel School » Excel Dashboard Classes » Excel VBA Classes » Excel Financial Modeling » Excel for PMs Crash Courses » Excel Formula Course » Excel Dashboards Course Downloads » Excel PM Templates » Excel Formula E-book

About us » About Chandoo.org » Welcome » Best of Chandoo.org » FREE Excel Downloads » Contact Details » Excel Forums