Creating A Yearly Database File Using Information In Daily File
Feb 7, 2008
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
View 9 Replies
ADVERTISEMENT
Apr 26, 2012
I need building a formula to pull information from a database file. The purpose is to try to interpret the data so that I can analyze a time period along with it's content. I don't know how best to explain it. Basically I have a table and I want it to pull information based on 3 criteria: time, type, and total. I would like for the formula to identify a time frame, then identify a type, and then take the totals and add them together.
Time / Type / Total
11:31 /1 / 4
11:43 / 1 / 6
12:04 / 2 / 1
12:31 / 1 / 3
Time / Type / Total
11:30 - 12:00 / 1 / 10
12:00 - 12:30 / 1 / 3
The top is the database and the bottom is basically what I want to happen on the other table. Pretend the / and dividers for each column.
View 9 Replies
View Related
Jun 3, 2013
I have a 5 major categories of products e.g. Ketchup, Hot sauce ,mayonnaise , pizza sauce etc.
Now each of these individual categories are further divided into number of of subcategories based upon the the sizes and brands.
I want to create a sheet (One sheet) where I have to record production of each product on daily basis.
Like for example if I am producing ketchup, I select ketchup from drop-down list,Now in next column I have to select production size from predefined sizes i.e subcategories. But it should also be from drop-down menu and it should only show me the subcategories of my selected product like ketchup .I think this has something to do with data validation but I am not sure how this model will work.
View 3 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
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
Jul 24, 2014
I use a program called Fishbowl for inventory management. Each day, the program generates a report of all orders fulfilled that day as a .csv file. I want to create a macro that will automatically open this file daily, reformat it, save it as an Excel file and email a .pdf of the reformatted file to my boss.
I can work out the formatting, and emailing. Where I am having trouble is right up front. The program saves the .csv file to a folder with the name Shipments today YYYY-MM-DD HH-MM
The report is generated each day at 18:00 so that part of the name remains the same, but the date changes daily.
If I ran the macro at 18:10 each day, is there a way to code it so it can open the right file?
View 3 Replies
View Related
Jun 25, 2014
I have a challenge trying to get excel to recognize yearly intervals.
Let me explain further:
Interval Percentage Yr1 Yr2 Yr3 Yr4 Yr5 Yr6 Yr7 Yr8 Yr9 Yr10 Yr11 Yr12
3yrs 10%
I want excel to insert the 10% into the cells every 3yrs.
I have attached a dummy workbook detailing my issue
Interval problem 1.xlsb‎
View 2 Replies
View Related
Aug 6, 2008
I have a report send to me daily. And I want to have a macro to save this report in the daily folder, such as “c:
eports8052008”, so tomorrow 's folder would be “c:eports8062008”.
All the daily folders already exist. Just need to change the file path. I tried some codes including sPath and format(now(), “mmddyyyy”), get error message.
View 9 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
Aug 11, 2009
Basically its a invoicing system. I tried with all the products in the same excel file but then I realized if i update the product list it wont in all excel files for each customer. So i thought one external access database (or excel file) that I could refer to from an excel file.
So what I want to do now is when I want to add a new product to an invoice, I click a button (add) and it opens a small window with the list of products which has been taken from say an access database or excel file. Then I select the product and click insert.
How would I go about doing this? Are there any simple example I could follow?
View 14 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
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
View Related
Jul 10, 2009
I have created a 'price list' database in ACCESS. Then in EXCEL I created a pivot table which retrieves data from one of the database queries (the query was saved as a .dqy file).
I emailed the file containing the pivot table to a colleague who is on the same server. He saved the excel file on he desktop & renamed it. When I update the databse file on a shared public drive on the server, he is able to 'refresh' his desktop file successfully !!
View 5 Replies
View Related
Feb 7, 2014
Sorting duplicates. In a big database
column A consist of 2000 names
column G consist of 2050 names
column M consist of 2020 names
How to identify duplicates using vlookup ?
View 14 Replies
View Related
Jul 18, 2014
i have database where are some data for several shops i need macro which will copy data for exactly that shop which i will choose in summary sheet, for better understanding i also attached excel file.
View 1 Replies
View Related
Oct 27, 2008
I use the below code to specify where a database is:
View 2 Replies
View Related
Nov 13, 2008
Let's say I have a database called testTemplate.mdb in c: which is a template-like database where i import data to. Is there a way for me to copy the testtemplate.mdb and past it to my desktop with file name test.mdb. I don't want to copy any data from the database, just the table's + queries. I would like this to be done via a macro... In order for me to execute other macro's to copy data from excel to that test.mdb file on desktop.
View 2 Replies
View Related
Jan 24, 2014
I've been trying to make this work for hours and hours and am finally giving up. Most of this code was found on the internet and I've attempted to make it work for my project but I keep getting errors. I use VBA with Excel quite often but never with Access before. Currently I am getting an "Object doesn't support this property or method" error at rs.Findfirst.
I have an excel worksheet that mimics the access table with five fields, an ID field, lastName, FirstName, DeptID (int), Email. I want to search the Access table for a match on the email field, and if it doesn't find the match, to add a new record using values typed into the excel sheet.
Code:
Sub UpdateDB()
Dim cn As Object
Dim rs As Object
[Code]....
View 2 Replies
View Related
Sep 12, 2006
I've been looking around to find a solution for my problem and as a last resort I have decided to make a post, and I will get straight to the point. I have 3 xl Files of relevance:
1 - is my "database" which consists of multiple sheets with different information in each. Each sheet is correlated with each other by one common ID. 2 - is my "template" which is 2 sheets, which functions as a report. The template serves as the Report which will be printed. The data from each row from the database can fill the spaces in the template.
3 - "the tool" xl file is where i have my macros and the mapping for the which columns from the database belong to which cells in the template.
Basically what I'm trying to get to work is: User opens Tool clicks "create Reports" and the tool should then open the database, pick the range of rows from the database put the data into the relevant cell in the template save the "template" with the info on it, close it and do it again with each row of data from the database. So if I pick rows 4 to 34 in my tool, it should create 30 xls files from the template and fill in the data from each row into each newly generated "report".
View 4 Replies
View Related
Aug 29, 2013
I've a xls file ready to upload to the my server through phpmyadmin and im using csv with load -date to upload it.
But my biggest problem here is how to input html tags in a excel cell, because when uploading it every word is combined without < p > or < br >.
There is on the internet an option to convert word text to html, and when i copy and paste from each cel to the online converter, I get then the < p > and < br > codes.
But I have a lot of cells in Excel.
View 2 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
Oct 16, 2007
I need a macro to get the info written in the Comment field (In the file properties field). Attached is a pic to explain a little better.
View 2 Replies
View Related
Jun 5, 2008
I have a macro that lists all documents in a selected folder and am looking to add the author/owner, created on date, last saved date as shown in the document properties.
The code I'm using is: ....
View 9 Replies
View Related
Oct 10, 2011
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies
View Related
Dec 15, 2009
I'll get straight to the point. I have a file with an (.ASC) extension that has information that I need in my workbook. The first problem is that in the .ASC file ...
a) there are french characters that don't translate into excel when I open it up
b) the date format is dd/mm/yy and excel doesn't seem to like this and only converts it as mm/dd/yy for those it can and the rest it leaves as text.
When I manual open the file problem b) disappears but if I use a macro problem b) pops up.
Right now I'm using this code to open the file
View 12 Replies
View Related
May 9, 2014
I have some information on two different spreadsheets. I would like to join all the information in only one file.
Some information is common, some not. Is there any way to join all the information in only one spreadsheet without using macros?
View 2 Replies
View Related
Dec 31, 2009
I have a daily log for work that keeps track of purchases and returns among other items and I was wondering if there was a way I could have all this information get put into a log that will amend everything for each week, month and year.
View 14 Replies
View Related
Mar 2, 2010
I've got numerous vba-coded spreadsheets in use with lots of different users, and have found that some users try to hide errors of their own making by blaming the spreadsheets (I know - what is the world coming to!)
I've decided to add a script which creates a log file each time one of my spreadsheets is used - opening a log which relates to the user's spreadsheet and recording username, date/time, path & file, and the vba being used.
I'll probably add an "on error" script to save copies of all open workbooks too.
The issue I have at the moment is how to record the name of the macro being run?
Here's my code so far: ....
View 9 Replies
View Related
Jun 21, 2014
I was wondering if it is possible to protect a macro to be used only if a *.txt (for example) file wit some certain text is available. like a key to run hidden in a text. The file is situated in a company network, and i want to protect it from taking the file out of the company. I can do it to check for presence of a file - if the file is not available - the macro exits. but if someone break the password that is protecting the code will see the blocking point immediately. this is why i want to use a key....
View 9 Replies
View Related
Jan 21, 2009
My company use a program called Bartender which is a barcode printing program. Bartender has the ability to extract information from an Excel sheet which is really useful and prevents repetetive keying in of information. We have found that if you simply print the Excel sheet when Bartender next uses the file it says it is corrupt and simply copying the data into a new spreadsheet and resaving it as the same name does not resolve this issue.
View 3 Replies
View Related
