+ Reply to Thread
Results 1 to 4 of 4

Pulling data from sepeate worksheet based on dates

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    Poteau, Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    2

    Pulling data from sepeate worksheet based on dates

    Here's the scenario:

    I have an excel sheet that contains a number of fields ranging from customer shipping information to sales. Each time a customer places an order, I import most of the information to populate the cells, and then finish up by doing a little data entry afterwards. The rest, such as unit cost, tax rate, net sales, etc., are completed automatically. This sheet works perfectly, but I want to be able to take this information and make it meaningful on a separate sheet(tab).

    On the second sheet, I have two drop-down date boxes. In addition, I plan to include a third drop-down box that will allow the user to select the report that needs to be shown based on the dates.

    For example, if the user selects "March 1, 2012" as the first date, then "June 1, 2012" as the second date, and then selects "Shipping" as the report type, then that should pull only all of the shipping information between those two dates from the first sheet and display them in the second.

    I've read through a lot of forums and still haven't found a way to do this. Essentially, all I need is the formula on how to make this work. Once I have that then I can work back through it to figure out how it works. I've seen some answers that show this to a degree, but so far I've been at a loss on everything.

    My skill level with Excel is average. Usually, I can figure out how to do things if I play with it long enough, but this one has really baffled me. As far as scripts and such, I've never delved into that much.

    Any help anyone can provide would be much appreciated!

  2. #2
    Registered User
    Join Date
    02-23-2011
    Location
    birmingham
    MS-Off Ver
    Excel 2003 to 2010
    Posts
    5

    Re: Pulling data from sepeate worksheet based on dates

    Is all the data that is to be filtered on the same sheet? So you want to filter from drop downs on one sheet then show the data on the other sheet that meets the criteria. Can you copy your headings into the thread and lsit the sheet names.

    As an idea, you can look to record a macro to add a filter and then place in some parameters and see the results, then stop recording this then gives you the basic information in the macro which you can adapt.

    If you can state which columns have the dates and which column would have the Shipping in it.

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Pulling data from sepeate worksheet based on dates

    Hi and welcome to the forum.

    If i understand correctly then one way to do this, is using SUMPRODUCT function.

    Take a look to Bob's site for this.

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    06-12-2012
    Location
    Poteau, Oklahoma
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Pulling data from sepeate worksheet based on dates

    Fotis,
    I started to review that page, but I'm going to have to look into it in more depth a little later on. However, it looks like that may be something that I can use with this workbook, especially with the totals that I need to generate. It's something that I'll have to play with tonight. Thanks for the help!

    Healey,

    I attached the spreadsheet here. (I didn't know this forum had that capability, otherwise I would have done that earlier!) It's not the best looking sheet, but right now my main focus is on the formulas and getting everything working right. Hopefully this will help. If I listed all of the headers here, I think it would turn out to be a novella!

    I have three sheets in the workbook:

    The first one, titled "Data Entry" is where all of the raw data is located. The information there will be imported. With a number of things, I have it set up to where I can filter the results.

    The second sheet, titled "Reports" takes the information from the first sheet and compiles it in a more readable format, which, once done, will be something that we can print off. The main purpose of this sheet is to see our financial information at a glance, as well as to have a printable list of who we need to ship to.

    The third sheet, titled "Formulas" is where all of the static information is located. For example, with this particular product, the cost of shipping always remains the same. Instead of putting this in the first sheet, I added it to the formula page so that I can change it quickly if we need to. This is hidden on the first sheet in order to make things a little more readable. A lot of the calculations on the first sheet are hidden as well, and are based on information located in the formula sheet.

    As far as macros, the last time I worked with anything like that was back when Windows 3.1 was around. I wouldn't have a clue where to start with them now.

    Again, thanks for the help! Usually I can figure this out, but at this point, I'm completely lost.

    Sales Tracking Worksheet.xlsx

+ 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