• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match Start and End Date to Dynamic Horizontal Ranges and Dynamic Charts

J

jdppep

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

Code: 
=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

[code]=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))[/code]
 
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))
 
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?
 
Luke,


Unfortunately, this is not quite the answer either.


Can I send you the abbreviated document for you to advise?
 
I did forget to mention that the start date and end date cells are meant to be drop-down lists.


Thank you!
 
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?
 
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.
 
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)
 
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.
 
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?
 
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.
 
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?
 
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
 
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!!
 
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!
 
You must log in or register to reply here.
Back
Top