+ 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
    Office 2019 (still learning)
    Posts
    24,598

    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
    Office 2019 (still learning)
    Posts
    24,598

    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)

Similar Threads

  1. [SOLVED] VBA: Pull data based on time frame from Raw data or pivot table to dashboard sheet column
    By Sharonjit in forum Excel Programming / VBA / Macros
    Replies: 55
    Last Post: 12-20-2018, 10:00 AM
  2. Formula to pull Top 10 Keywords based on values in a separate column
    By dyljenx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2017, 11:20 AM
  3. How do I pull and place 'Name' data from Sheet to Sheet based on 'ID'
    By goonshiredgoons in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-13-2015, 03:41 PM
  4. Replies: 1
    Last Post: 03-18-2015, 07:23 PM
  5. [SOLVED] Excel Formula to pull data from another sheet and place cell blank if value is 0
    By szpt9m in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 09-18-2013, 01:07 AM
  6. Replies: 1
    Last Post: 02-04-2013, 11:40 AM
  7. Referencing cells on a separate sheet based on pull down menu response
    By Jenrich in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-03-2009, 03:37 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