+ Reply to Thread
Results 1 to 6 of 6

Trying to create a dynamic report that pulls sales data from another tab based on week

  1. #1
    Registered User
    Join Date
    04-17-2015
    Location
    Memphis, TN, USA
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Trying to create a dynamic report that pulls sales data from another tab based on week

    Hi,

    I'm trying to create a report that will allow me to select the location from a List, And then the WEEK, and then it populates the fields based on some formula. I know it has to do with Indexing, i believe, but I haven't been able to make it work yet.

    I want Sum(TotalRev), Sum(profit), Sum(goals) to be populated by selecting The store, then Week. It should then see the data for that time period, sum it, and populate the cells. Weeks need to be set Monday through Sunday. Is that possible?

    I've attached the sheet, and the cells that should contain the lists are highighted in yellow, the cells that should populate, in green.

    Thank you very much for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,048

    Re: Trying to create a dynamic report that pulls sales data from another tab based on week

    You need to explain where those numbers are coming from for at least one store.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    04-17-2015
    Location
    Memphis, TN, USA
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Re: Trying to create a dynamic report that pulls sales data from another tab based on week

    In the excel workbook I've provided, the second tab has all the data.

    Basically, I want to turn the dates into a list. I want to be able to pick a store,pick a week(for that, i would need to turn the days in
    to a list of WEEKS, not per day. And the columns next to it would display Sum(GOAL), Sum(Actual Sale),Sum(diff), Sum(profit), for that specific week.

    Here's the data for one store:
    Display:
    Store Week Goal ActualSale Diff Profit
    1 2-6-17 42849 23985 -18864 10645


    Store Date Goal ActualSale Difference Profit
    1 2/20/2017 3442.12 942.92 -2499.2 349.59
    1 2/21/2017 3187.1 443.04 -2744.06 172.74
    1 2/22/2017 3902.69 3024.69 -878 1341.67
    1 2/23/2017 4220.89 7509.46 3288.57 3667.59
    1 2/24/2017 8704.42 8620.67 -83.75 3517.1
    1 2/25/2017 14781.5 3444.61 -11336.89 1597.3

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Trying to create a dynamic report that pulls sales data from another tab based on week

    Hi,

    Here is my solution.

    You will need to do a few things first to prepare your worksheet.
    First, add a column next to your "Day" column. Use the function "WEEKNUM" to return the week number associated with your date. Determine on what day your week starts.
    Second, create a "Data" sheet. It needs to have two columns of data. A "Store" column for your store numbers and a "WeekNumber" column containing 1 to 52.
    Third, create named ranges for your "Store" and "WeekNumber".
    Fourth, on Sheet1 change B4 DV to reference "=store" and C5 DV to reference "=weeknumber".
    Fifth, use SUMIFS function to your hearts content for your Sum(GOAL), Sum(Actual Sale),Sum(diff), Sum(profit).

    Attached is an example using your data.

    Hope this makes sense and is helpful.

    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-17-2015
    Location
    Memphis, TN, USA
    MS-Off Ver
    MS Office 2013
    Posts
    11

    Re: Trying to create a dynamic report that pulls sales data from another tab based on week

    this is great. thank you very much. just what I needed.

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Trying to create a dynamic report that pulls sales data from another tab based on week

    You are very welcome. Glad I could be of some assistance.

    Cheers

+ 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] Making Daily Sales Planner recognize which month and which week to pull sales data from
    By Topher53180 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2014, 10:33 AM
  2. A report in excel which pulls data from postgresql db
    By bhavikumehta in forum Access Tables & Databases
    Replies: 1
    Last Post: 04-20-2013, 12:27 PM
  3. [SOLVED] Sales report from sales data sheet if i select the date
    By loki7431 in forum Excel General
    Replies: 4
    Last Post: 02-06-2013, 09:43 PM
  4. Need to create a report showing 3 day / 14day / 28day customer calls based on "last week"
    By TopherBrowne in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-27-2012, 10:30 AM
  5. linking weekly sales report to monthly sales report
    By sueatcigaretshopper in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2012, 09:22 PM
  6. copy week total in weekly sales worksheet to appropriate week in monthly sales
    By Sandy2976 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-15-2009, 01:04 PM

Tags for this Thread

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