+ Reply to Thread
Results 1 to 4 of 4

Trying to pull data from a chart for just this pay cycle

  1. #1
    Registered User
    Join Date
    09-18-2011
    Location
    Kansas
    MS-Off Ver
    Excel 2010
    Posts
    40

    Trying to pull data from a chart for just this pay cycle

    Good Morning, everyone!

    As of right now, I have a spreadsheet that records the number of calls that each employee makes. Columns A-G contain person's name and their daily data, with years of old data. I've also got some information out to the right of these columns. In L1, Today's date, as determined by =TODAY(). L2 contains the Monday that corresponds to the start of the current week, determined by =L1-WEEKDAY(L1,3). L3 contains the corresponding Sunday.

    Below that, I have a chart that contains all information from the days between L2 and L3. The formula that I'm using is as follows:
    =IFERROR(INDEX(A$2:A$1141,SMALL(IF($A$2:$A$1141>=$L$2,IF($A$2:$A$1141<=$L$3,ROW($A$2:$A$1141)-ROW($A$2)+1)),ROWS(K$7:K7))),"")

    Now I have no trouble getting my chart to display all of the data between the dates in L2 and L3, so no biggie there, but I'd like to have it display all of the relevant data for the current pay period based on today's date. Our pay period ends every 14th and 29th, so the number of days per pay period can vary depending on long or short months. For some reason, I can't seem to wrap my head around the way that the formula should look for cells L2 and L3 to produce those dates.

    For example, if today is 7/23, I'd like it to return 7/14 in L2, and 7/29 in L3. I want it to return those same dates in L2 and L3 tomorrow and the next day, and I don't want the dates to change until 7/30. Then I want L2 to show 7/30 and I want L3 to show 8/14

    Does that make sense?

    I've attached my current sheet with dummy data.

    Thank you all soooo much!!!


    Sample Data.xlsx

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Trying to pull data from a chart for just this pay cycle

    M2 =
    Please Login or Register  to view this content.
    M3 =
    Please Login or Register  to view this content.
    See the attached file.

    P.s. I used M6 to repair my bike.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-18-2011
    Location
    Kansas
    MS-Off Ver
    Excel 2010
    Posts
    40

    Re: Trying to pull data from a chart for just this pay cycle

    Thank you so much for helping me out! I may be doing something wrong, but when I change the date in L1 now, back to TODAY() or to a future date, I get #VALUE! in both M2 and M3.

    Any idea where I 'm going wrong?

    Thank you!!

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,964

    Re: Trying to pull data from a chart for just this pay cycle

    Did you tried it in my example in #2.

    It works for me.

    Please reply.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to pull data from a chart based on a certain lookup value...
    By gmazz in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-23-2014, 10:04 AM
  2. Pie Chart to pull data from single column
    By Paragoomba in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 07-30-2013, 10:03 AM
  3. Auto pull data to chart
    By rbacon in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-13-2012, 06:11 PM
  4. Chart from multiple tab + Cycle Function
    By seboss in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-28-2012, 11:41 PM
  5. pull from a block of data:Chart problem
    By jtd84 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-03-2010, 05:41 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1