+ Reply to Thread
Results 1 to 5 of 5

Is it Possible to pull data from a column and place in a separate sheet based on a week.

  1. #1
    Registered User
    Join Date
    01-17-2019
    Location
    falmouth
    MS-Off Ver
    2019
    Posts
    31

    Is it Possible to pull data from a column and place in a separate sheet based on a week.

    Hello everyone, I am not too sure where this post should be so i will begin by asking here.

    I have a column of data that displays sales that i have made next to the date that it was made. I am looking for an easy way by clicking a button on the sheet to display the sale s that have been made in the working week eg. Monday - Saturday. I would like this to be displayed through a list box on a userform, i am confident on how to achieve the latter part thankfully from help on this forum, i am stuck however trying to understand how to collate the data on a separate sheet when i call for this information. is this at all possible and where would i start?

    Many thanks Oly

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,911

    Re: Is it Possible to pull data from a column and place in a separate sheet based on a wee

    If you want to "click a button" and use a "list box on a userform", then you would need to use a macro for that.

    However, you could achieve this using formulae. You could select the week commencement date on the second sheet and a formula on the main sheet would identify those records which fell into that week, and then other formulae on the second sheet would fill the appropriate data.

    It would help in any case, if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-17-2019
    Location
    falmouth
    MS-Off Ver
    2019
    Posts
    31

    Re: Is it Possible to pull data from a column and place in a separate sheet based on a wee

    Thanks for your reply, i have attached a Test Book, this has 3 sheets. "Daily Totals" "Week Totals" & Test Sheet.

    Again the idea is to be able to pull up a userform (shown in my "Test Sheet" Sheet) That displays the current weeks totals.

    The daily totals are inserted with a userform i already have in place ( not on the workbook) They are submitted daily, i am however stuck with collating the daily totals . I would like the Formula i use to be specific to the system date so that it will dynamically change depending what day and week we are in.

    I guess i need a formula that looks at the colum data then based on the current date only adds values that lie on a specific day within a specific week within a specific year? But all this has to happen automatically within the cell on "week Totals"

    I hope i have made this clear?
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    01-17-2019
    Location
    falmouth
    MS-Off Ver
    2019
    Posts
    31
    Good morning, any thoughts on this as I've been trying now for 3 days and can't get anywhere? Thanks in advance

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    18,911

    Re: Is it Possible to pull data from a column and place in a separate sheet based on a wee

    Add this formula to cell F2 of your Daily Totals sheet:

    =IF(A2="","-",WEEKDAY(A2,2))

    then copy this down as far as you like. You can call the column Day_num in F1.

    In the Week Totals sheet you should insert a new column A, call this Week_num in cell A3, and then fill the column with a sequence 1, 2, 3, 4 etc., starting in A4. Then in B4 (under Monday) you can use this formula:

    =SUMIFS('Daily Totals'!$D:$D,'Daily Totals'!$E:$E,$A4,'Daily Totals'!$F:$F,COLUMNS($B:B))

    Copy this across and down, as required.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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