• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel slicers and pivot tables

J

jose

New Member
A couple of weeks ago I received a "great job" from my regional manager and a "lets aply this to all accounts" new task for me!! I'm talking about the use of slicers and pivot tables dashboard, idea that I got from a post here, and with your help I succesfully completed. but now that I got access to new data and lots more data volume I've run into a couple of new problems, I'm hoping that with your help I can finalize and get promoted (fingers cross, since nobody at my office knows how to do pivot tables much less use slicers.


FYI to get a feel of the issues let me tell you that the data is from shipping and receiving, it's all related to a control number given to all shipments at the time of arrival.


so my first problem is that our system does not match received date with ship date, so in order to know how many receipts I have in a period of time I have to download 1 report just for receipts (COLOR WHITE IN THE EXCEL REPORT), and an additional one for shipments (YELLOW). so I need to find out when a shipment was received and shipped and how many days it stay in the warehouse. I started with VLOOKUP the receipt reference from the shipments database "S2" to look for it from column "C" and pull received date from column "D" then just create a subtraction formula in column "AF" to give me number of days between the two dates. sounds simple but I get errors. dont know what Im doing wrong with the VLOOKUP. need help


Second issue. I need for one of the slicers to be filter by week and another by month, I can get the week and month number with WEEKNUM etc, the problem is that I have one set of week from the receipts report and another from the shipment reports. how can I group the two sets from receipts and shipments to just one, so that when I select from the slicers "WEEK 1" I can view number of shipments and receipts on the dashboard?


I know that it may seem a lot, but i'm learning as I go, not really a tech guy actually I'm pre law, but with your tutorials and simple instructions it all seems so simple.

Thank you in advance for all your awesome support HERE IS THE LINK FOR THE FILE


https://www.dropbox.com/s/kpjnivw0rn819cc/receipts%20VS%20shipments%20other%20accounts%20jan%20to%20may.xls
 
ok, so I got the vlookup figured out, several issues, first one was that data was not sorted, then one data was set as text and the other was as number.. Done


I still need to figure out how to group the two time frames, weeks and month, working on this, I noticed that there are sone receipts that have not ship out, so on those the condition would be to include june 30 date and substract the received date to give a days in storage, thank you for your support
 
jose - note that data doesn't need to be sorted for VLOOKUPS if you use FALSE for the optional fourth argument. Also note that if you don't use that false argument, then Excel performs what's called an 'Approximate' match rather than an 'Exact' match. That is, if you lookup something that isn't in your lookup list, Excel will return the nearest numerical or alphabetical item to your lookup term.


For instance, try this:

Put "Apples" into A1 and "Carrots" into A2

Put this formula into B1:

=VLOOKUP("Broccoli",A1:A2,1)

Note that it returns "Apples" even though we were looking for "Broccoli".


So I suggest you include the FALSE as the forth optional argument, so Excel doesn't give you false matches.
 
Thanks Jeffrey, noted and updated, it actually solve a problem I created by sorting the data, since I had pluged in a couple of formulas they got distorted trowing false information, got a little paranoid at first but I downloaded data again redo Vlookup with the FALSE at the end and wala!! got it..


just still working on how to group those 2 sets of weeks and months, any luck with that?
 
You must log in or register to reply here.
Back
Top