Discrepancy Report (logistics Field)
Oct 29, 2009
i am regular viewer of these forum..really this forum gave me many nice information. btw i am wrking for in a logistics field. i am not tats much professional in excel.
every year end i am preparing anual stock taking report in excel. but this time i like to take adavantage of membership of this forum by asking some some tips for this my anual stock taking report. so i am attached a sample file. in this file sheet-1 i put all datas of stock items. in sheet-1
A2=S.No
B2= Item Code
C2= Description
d2= UOM (Unit of messurement)
e2= Closing stock (stock in system)
G2= F2-E2 (Discrepancy)
now i want only Discrepancy (if there is minus and plus...)item lists in another sheet (report sheet). how can get all this data there?
View 5 Replies
ADVERTISEMENT
Feb 20, 2012
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies
View Related
May 21, 2014
I am a Supply Chain intern at a company for the summer. One project I have been given is to create a tool in excel that allows our purchasing people to type in certain inputs (origin, destination, etc.) to calculate the total cost of shipping an item from a supplier in anywhere from china, to somewhere locally. I am gathering quotes for shipping via ocean and truck. How can I get where I enter in one location, say shanghai, china, shipping to another location, say Norfolk, VA, and get the total mileage difference to show?
View 1 Replies
View Related
May 29, 2014
Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)
I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.
View 2 Replies
View Related
Mar 12, 2008
For a set of of paired values A and B, I calculated the Geometric Mean two different ways in Excel (Office 2003). First using the GeoMean function. Second using the equivalent formula,
which is:
(A * B)^.5
(i.e. the square root of A times B).
I was surprised to find that the two methods produced different results 11% of the time.
OK, the discrepancy does not appear until after 10 or 11 decimal places, but I would like to know which method produces the more accurate result. Does anybody know?
calculating the geometric mean using logs (wherein the geometric mean of A and B is equal to the anti-log of the average of their logs) produces discrepancies about 30% of the time, so I assume that is the least accurate method.
View 9 Replies
View Related
Oct 15, 2009
I should disclaim that I'm quite novice at that, so my approach to writing macros is to record individual steps to determine the appropriate method, then copy and paste.
In any event, I have a macro that creates a word doc from content in an excel file. I copied over the code for inserting a text field from the Word Macro Recorder:
View 4 Replies
View Related
Apr 30, 2008
Add Multiple Buttons That Select Row Of Housing Cell. One of my Macro's places a button on a sheet using Top/Left;
Set sbut = Sheets("Listings").Shapes.AddFormControl(xlButtonControl, _
Range("J" & count).Left, Range("J" & count).Top, 50, 12)
When I try to find the row of the clicked button using the following code;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
The code behaves differently in Excel 2002 and 2007. In 2002 it returns the row the button sits on, but 2007 returns the cell above the button. This could be because the workbook is an XLS running in Excel 2007 in compatibility mode? Is there a way to "nudge" the button down a little after it has been placed using .top/.left ? Or is the only way around this something like;
Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Address).Activate
count = ActiveCell.Row
If Application.Version >= 12 Then
count = count + 1
End If
Is this a bug, or something that I am doing wrong ? The workbook needs to be a .xls to ensure that it works in older versions of Excel.
View 4 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Jul 4, 2013
In the following extract, I would like to have today's date placed into Col K when an entry is made into Col J.
Entry into Col J is from a validation list of 3 options. What I desire is that when one of these options is selected ("Complete"), then I would like today's date to be written into Col K.
Currently Col K has a validation list which comprises dates from 1st of each month from July to June.
I need a fixed date as text preferably, so was thinking the VBA function "Date" on a Change Workbook Sub.
The information is currently part of a Table in Excel 2007 that has about 500 records.
IdeasList
*
J
K
2
Status
Month Completed
3
In Progress
*
4
Complete
Jul-13
5
*
*
6
*
*
Data Validation in Spreadsheet
Cell
Allow
Datas
Input 1
Input 2
J4
List
*
=ValidationList_Status
*
K4
List
*
=ValidationList_Months
*
View 2 Replies
View Related
Jun 9, 2006
I have a macro that imports a report. If the Charge Type in column A is BTOREPLX, I need to prefill the field next to it in column B with the text "REPLX", e.g. "REPLXCDROM". I've attached an example of the report.
View 5 Replies
View Related
Apr 23, 2008
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies
View Related
Feb 13, 2013
I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Jan 25, 2013
I have a tab called 'Tab1' in which I have column called 'Col1' and 'Col2'.
I have another tab called 'Tab 2' with columns 'ColA' and 'ColB'. When I enter a text in Col1(Tab1) I would like that text to be compared to data in 'ColA' from Tab 2 and when match found then get the corresponding value from ColB and autopopulate it in Col2 of Tab1.
View 2 Replies
View Related
Dec 28, 2006
in coverting these fields into a date field.
Example 91306 to 060913
I have encloed the file.
View 14 Replies
View Related
Apr 29, 2006
I am trying to count the number of times that data is in a field and in any field the date is greaert than today's date. This date field could be blank and that is where I am having the problem.
Example
See Attached
View 4 Replies
View Related
Jun 3, 2006
I have two data sets one measured and one modelled and I am testing a conceptual model that proposes that only where the tails of the probability distributions of each data set crosses will you experience an event. I'm enclosing an example file which contains both data sets and some descriptive statistics as well as the associated histograms and a diagram of what I'm ultimately trying to achieve. My question is how I go from the data sets I have with such a large discrepancy between them to plotting their probability distributions?
View 5 Replies
View Related
Jun 22, 2012
I want to concatenate two Cells into a single cell BUT have the first field left justified and the second cell right adjusted.
A1 = "John Williams", A2= "Single"
A3 = "John Williams Single"
View 1 Replies
View Related
Apr 16, 2008
I am working in SAP, I am attempting to convert a report in excel and I'm get the following error codes/debugger messages:
1ST Error Message is : Path/File access error: 'VBD1.tmp'
Then I press okay and get a Visual Basic Error:
Run Time Error '1004'
Methon 'Copy' of object'_Worksheet' failed
If I press debug I get a bunch of Visual Basic sheet, with the following highlighted
Set ws = SAPSheets.item(SheetIndex)
View 9 Replies
View Related
Nov 25, 2008
I am trying to customize an aging report, column will be 30, 60, 90 120 + I would like the amount to show under each column. The charge amount for $252.38 each day.
View 9 Replies
View Related
Jul 14, 2008
So this is the problem, there are 65 files that are stored in a folder, each file has 8 tabs, all these tabs within each file are identical in every aspect ( i.e cell ranges and names). The max number of rows in a tab is 81. To break it down:
Tab 1 : Instructions (this will never change and will always be constant among all 65 files)
Tab 2: Row 1 will be the same for all files, rows 2-9 i don't need, except for the info on cell C3 which I need to be copied through S11:S33. S10 needs to equal A3 with the format of R10. And then just have the next files tab info below this table with the same logic being followed.
Tab 3: Same logic as tab 2 but now the range is from S11:S53.
Tab4: Same logic, just copy and paste tables for all files, and doing the same thing with column S hat was done in tab 2 in order to be able to filter tha data.
Tab 5: Same logic, with H1 being filled by the descriptor on Tab2( This will enable one to filter though the information since the descriptor in cell 3 from tab 2 is the short name given to each files which differentiates the data.
Tab 6; Same logic
Tab7: Same logic, but would like a drop down box on right to filter by service centers.
Tab 8: Same logic, but would like to have drop down on rigt in which I can select the given short name ( i.e cell 3 from tab 2 of each file)
This way I can have one master file which encompasses all the information from all 65 files in one file and be able to sort through the data in each tab by the short name description. I need a macro for this since these files get updated in a weekly basis.
Any help on writting such a code would be greatly appreciated, my excel VBA skills are non existent!. I have attached the template of how one of these files looks like. There all the same except with different names of course and all are stored in one folder.
View 12 Replies
View Related
Dec 5, 2009
sometimes pivot tables won't give me the details I want the way I want. any time my boss asks me for a monthly report I have to do everything manually from the pivot table and insert the data on another table where I put the filteres data. I want to be able to get a monthly sales report per store that will populate itself in a daily basis, I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet. Aproblem that I have found in order to use the size as a parameter is that the size comes in the same line as the item description and do not know how to make excel to check just for the size in the description field.
The attached file contains sales data for a better understanding of my case.
View 14 Replies
View Related
Oct 31, 2005
I have a coulmn with results in that are in a decimal point example .01, .02.
At the bottom of the coulumn I want to report the max number and the min
number but it does not. Also in one column my results are listed as <0.05
etc. At the bottom of this column I want to report the max and min but it
does not. I believe because they are not whole numbers. Is there a way to
report these numbers. I tried an IF formula where if <0, max(i12:i36) but
that did not work.
View 12 Replies
View Related
Jan 8, 2008
Analysis of Profit is very impt in business , that is why i keep trying to find way to use excel to generate profit report.
I have been trying to solve this for last one month. even when i bath... But no result.
May i ask any one here know how to convert my data to a report using . Below table said every thing.
Project (10) HIJ1My Raw Data File 2BrandMonthProfit In %3Brand AJan 0730%4Brand BFeb 0750%5Brand AJan 0730%6Brand BFeb 0750%7 8 9I Need Report In New Work Sheet 10 11BrandJan 07Feb 0712Brand A30%30%13Brand B50%50% Excel tables to the web >> Excel Jeanie HTML 4
View 9 Replies
View Related
Dec 9, 2008
i have accumulated a lot of data on an excel sheet now i need to be able to run 2 reports that will spit out the data in a specific way but i have no clue but i have made a word document to show how i would like it to spit out
View 9 Replies
View Related
Apr 30, 2009
I'm working on some homework for a CIS class, that is pretty straight forward...except I don't know how to make a summary report in excel.
View 9 Replies
View Related
Jun 25, 2009
I've three columns excelsheet and want to create Per day report.
Date,Records,Status
I want to count per day records and status.
This will be self-explanatory when you check this screenshot
View 9 Replies
View Related
Jun 6, 2006
1- I 'm working with a table wit many Sheets.
I want to make a Report Sheet with selective ranges(cells)
the selective cells should paste as link ( in order to be updated cells)
2- then how to Zip this worksheet to zip file ( to save it)
3- If I have a Macro and i want to perform this macro to all sheet except one sheet how to that ?
View 6 Replies
View Related
Mar 8, 2007
Just to fill you in on what I am doing, I am am putting a report macro in my spreadsheet that looks to see if a value meets the criteria if it does then it will copy a selection of cells data from that sheet into a new sheet. It will do this for all the sheets so in theory I should end up with a simmilar kind of report function to access. The problem is to when I do this the data does not appear, I do not know why.
Private Sub cmdPoor_Click()
Dim sheet As Worksheet
For Each sheet In Worksheets
If Not sheet.Name = "Main" Then
If Not sheet.Name = "Template" Then
If Not sheet.Name = "Help" Then
sheet.Select
If Range("L44").Value = Poor Then
Dim strName As String
Dim strSurname As String
strName = Range("L41").Value
strSurname = Range("L42").Value...................
View 4 Replies
View Related
