Results 1 to 3 of 3

Thread: Using a date range and data from another sheet but using a from and to date cell in the new sheet to manipulate the data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2014
    Location
    London Uk
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using a date range and data from another sheet but using a from and to date cell in the new sheet to manipulate the data

    I am using the formula below successfully on a workbook at (sheet 3) which gives me the number of contracts by the person named at E3 between the dates I enter a C8 & C10.


    =SUMIFS(Test,Daterange,">="&$C4,Daterange,"<="&$C6,Manager,$E$3)

    I now want to use the same base information on a separate summary summary sheet (Sheet 1) which draws all the information from sheet 3, but uses the start date at (C4) and end dates at (C6) from cells within sheet 1 (NOT sheet 3). Therefore the results would stay exactly the same the same dates are shown on both sheets, but would change when the dates are set differently between the two sheets.

    The reason I need this, is that multiple users use individual sheets and therefore set the date ranges on their own sheets to suit their purposes, however on the summary sheet (sheet 1) I simply want to capture the information between dates without interfering with their settings.

    I hope I have explained this sufficiently as any help will be greatly appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Using a date range and data from another sheet but using a from and to date cell in the new sheet to manipulate the data

    Would this not do it?

    Code:
    =SUMIFS(Test,Daterange,">="&Sheet1!$C4,Daterange,"<="&Sheet1!$C6,Manager,$E$3)
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    May 2014
    Location
    London Uk
    Posts
    201
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using a date range and data from another sheet but using a from and to date cell in the new sheet to manipulate the data

    Hi JLGWhiz,

    Thank you for trying to assist me, unfortunately this didn’t work for me.

    Perhaps if I try to explain what I am trying to achieve.

    I have 20 separate worksheets, all are individually named by users name.

    Each sheet contains input data across a large number of columns. This data starts from row 16. I want to draw a summary from some of the data held on the individual’s sheet.

    For example in Column D (from 16 down) dates are entered. In Column F (from 16 down) a named individual is entered and Column G (from 16 down) a status is entered, E.g. “Complete” “Awaits” “Cancelled” etc. And finally, at $E$3 on the sheet, is the team users name.

    I want to pull this information onto a Management Summary Sheet, based on the criteria above, but want the date range to be controlled by using a start date in cell $C$4 and an end date in cell $C$6 on the Management Summary Sheet.

    If you are able to help with the correct formula I can then adapt your formula to suit the differing criteria and for each user.

    I hope my explanation assists you to assist me.

    Cheers and thanks again

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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