Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
May 23, 2006
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
View 3 Replies
ADVERTISEMENT
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Jan 5, 2010
I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.
View 4 Replies
View Related
Feb 20, 2008
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies
View Related
Apr 5, 2011
I am in sales and we currently have a excel spreadsheet with call tracking and a sales tracking on it. I would like something a little more inviting. We have quotas that we have to meet. 100 calls a month, 20 quotes to put out, $11k is sales.
I was thinking maybe something that was broke down by week for calls. The sales and quotes, have a graph associated with them. When we type in the dollar amount it will raise the graph that much. I think this will make it easier to manage. I have attached what our call tracking looks like now.
View 3 Replies
View Related
Jan 4, 2005
I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.
View 9 Replies
View Related
May 7, 2012
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
[Code]....
View 6 Replies
View Related
Jul 31, 2014
I am creating a spreadsheet that tracks daily employee attendance.
The title for the E Column is, "Received On Time?"
In the next column, I want to enter a formula that does the below:
If I enter Y, the cell reads "N/A"
If I enter N, the cell reads "ENTER TIME"
If the cell is BLANK, the cell should read "NOT RECEIVED"
So far, I have entered the below function in a cell in my excel spreadsheet, which worked perfectly:
=IF(E3="Y","N/A")
I also want to add in this same cell:
=IF(E3="N","ENTER TIME") AND =IF(E3="BLANK","NOT RECEIVED")
View 2 Replies
View Related
Oct 8, 2013
What function should I use to find the total length of the 3 extracted names (last + first & middle title?) I need one single expression for the entire column.
A
B
C
D
E
Full Name (Last, First & Middle plusTitle - the latter may be separated by either an ! or $ symbol).
Title (extracted)
First and the middle name if the latter exists. (extracted)
Last Name (extracted)
The total length of the 3 extracted names (last + first & middle + title) e.g. 25
6
Brouillet,Amy Anne!Miss
Miss
Amy Anne
Brouillet
[Code] .......
View 6 Replies
View Related
May 18, 2009
I have a query regarding making a Excel Sheet for Daily sales.
here I go,
Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total..
M givin you an example in the Below Sheet.
View 9 Replies
View Related
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
Jul 15, 2014
I am trying to build a sales sheet that will keep all the records during the month but every day it refreshes to a blank sheet, then allowing users to flick back to a previous day and all the data from then is still saved?
I think this is possible in excel as I am sure I have seen a working sheet before but I don't know where to start or even what the process would be called in excel?
View 1 Replies
View Related
Jun 13, 2014
I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls
View 7 Replies
View Related
Jan 7, 2014
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
View 2 Replies
View Related
Aug 1, 2014
I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.
I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.
Here's what I've done so far:
Code:
ThisDate = Range("C1").Value 'the cell with todays date in it
ThisDateName = Format$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it
ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago
ThisDateName7 = Format$(ThisDate, "yyyy-mm-dd") 'formatted correctly
ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago
ThisDateName14 = Format$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago
I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)
I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:
=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15
or even
=C15 - '[pph_tracker_{The Date in Cell $G$1}.xlsm]Sheet1'!C15
which would require no macros at all!
View 3 Replies
View Related
Apr 20, 2012
I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.
This is how my data looks like:
ITEM NAME 4/1/12 4/2/12 4/3/12 etc
item1 10 15 500
item2 5 0 3
item3 0 0 5
etc
I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.
I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.
I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....
View 1 Replies
View Related
Aug 18, 2012
I have the day of the week in B1 Emp Nbr in B2 Hours worked on B11
What I need a macro to do is match B1(day of the week) with the corresponding offset column in F1-R1, Pos and Post the Emp NBR(b2) and Hours(b12) in the corresponding column (F4 and G4)
I then will key in a new Emp and the hours and click the macro to execute the move over to the correct column and down one row.
Do this until I post Emp 999 at which time it will exit the macroOzgrid weekly hours.xlsx
The next day I will change the day in B1 to 2, corresponding to TUE, and it will move over to the correct column (H4 and I4) and post the daily data until I key in EMP 999
Then continue for the rest of the week.
View 3 Replies
View Related
Aug 20, 2013
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
View 1 Replies
View Related
Mar 20, 2014
Once a job is complete on a sales tracking sheet I created, it needs to transfer automatically once I click on a "completed" section of my work sheet.
View 1 Replies
View Related
Jan 5, 2012
I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....
All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.
Column A Column B
201 $200
202 $300
203 $450
View 8 Replies
View Related
Feb 4, 2013
I wanted to get the data from data sheet where i have entered the data manually for sales what have done
Now I wanted is when i enter date i should get the product and price in other sheet
Example:
If I enter date in a1 cell
I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet
And c1 - c10 price (if i sold 10 item @that price)
View 4 Replies
View Related
Jan 14, 2013
I got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.
The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.
So in the new sheet I will have
Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on
View 5 Replies
View Related
Feb 13, 2014
How to Collate and combine lab daily assay data into a single easily manageable file.
Currently i have a spreadsheet for every days assay results. Now this should be combined into monthly and yearly data.
View 1 Replies
View Related
Jan 19, 2009
I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.
Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.
But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.
While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?
View 9 Replies
View Related
Apr 16, 2009
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types:
1 car
2 truck
3 boat
4 motorcycle
For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875-FRD from the raw data file. The second item (cell E3) would return invoice number 7147877-NSN.
View 2 Replies
View Related
Nov 29, 2013
A
B
C
D
E
F
1
DATE
MANIFEST
P'work lbs
Scale lbs
over/short
2
1/1/2001
1
1000
1001
1 (formula)
[Code] ..........
I would like a formula to put in column F that will total the daily over/short.
I would like to place this formula in every row in Column F, but return empty "" if the next row is not empty, if however the next row is empty total the over/short column for the day. Keep in mind that a zero value in column E is not empty.
View 9 Replies
View Related
Jun 17, 2014
I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.
View 3 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
Mar 5, 2012
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
View 3 Replies
View Related
Jun 7, 2014
I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.
So basiclly if the Jan total is $100 in column a, the Feb total is $150 in column b, and the March total is $200 in column c. I would like a formula that will tell me that the average increase in dollars is $50.
View 2 Replies
View Related
