Creating Database For Daily Production Record Multiple Products
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
ADVERTISEMENT
Dec 6, 2013
I have been creating a simple database to hold a record of attendance.
I'm now at the stage where I want to create a worksheet that cannot be modified; but rather is something the user can look at in a glance.
I need forming a SUMIFS formula that will look for monthly periods of the times late, sick, absent etc and add them up to return to one place.
So the dependants will be the month, the employee and the type of record (late, sick, holiday)
I have created a userform that I can use to add in the raw data, so I'm not sure what range I can create, as every time new data is added it finds the next available row.
Here is what I'm using so far: [Code] ........
What the Overview will look like : Overview.PNG
What the raw data looks like : Raw Data to use.PNG
Do I need to introduce a matched up column that has the employee name and date?
View 1 Replies
View Related
Apr 24, 2013
I have daily production sheets (Excel sheets) that I fill out every day indicating the day's production totals. The files are named by the day's date. For example today's sheet (4/24/13) would be 042413.xls. However, I would like to be able to pull certain pieces of information into another separate spreadsheet. For example, my boss asked me how many of a certain product we made over the last month. So, I'd like to pull the product info, the date made, and quantity made into another sheet. And I'd like to be able to do this automatically for a date range that I specify.
View 8 Replies
View Related
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
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
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
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
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
Apr 3, 2013
I have a 2 sheets in a workbook by the name Entry and DataStore. I am entering the daily data in Entry sheet and then manually updating the data in DataStore sheet. Is there a macro to automate this.
I have uploaded the sample file with the expected output comments to the below link:
Free large file exchange service without size limits.
View 8 Replies
View Related
Aug 2, 2009
I have a ListBox in a UserForm that displays multiple branches. I can't figure out the code to delete the selected record? if the user selects "Yes" from the MsgBox?
View 6 Replies
View Related
Dec 24, 2011
I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.
I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).
I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.
View 9 Replies
View Related
May 5, 2013
I am Working on a Query to Update Record in the Database if Exists Otherwise Insert. I have 2 tables in the database Receipt table and Inventory Account Table.
For Receipt Table there is Form which is used to Insert Data into the Receipt Table(SQL TABLE). Behind the Button Click Event with the Receipt Table Insertion Query I Write a Select Query to Get data from Inventory Account Table Group By Part Number and Location in a recordset.
Code:
objmyrecordset.Open "select [Part Number],sum([Quantity Received]) as TotalQuantity,[Move From] from [5_PO_RECEIPT_TABLE_DATABASE] group by [Part Number],[Move From] ", objMyConn, adOpenStatic
objmyrecordset.MoveFirst
Do
strSQL = "select Count(*) from [Inventory_Account_Move_Table] where [Part Number] = '" & objmyrecordset![Part Number] & "' and [Location]= '" & objmyrecordset![Move From] & "';"
[Code] .......
I used the Above Code to Complete my task. The Problem is It is Adding the Correct Values into the Inventory Account Table But It is Not Updating the Previous Records. it sum all the Values in Receipt Table based on Part Number and Location but it insert this record in New Line. I want it to Update the Previous record for same part Number and Location.
View 9 Replies
View Related
Jan 9, 2012
I have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
View 1 Replies
View Related
Mar 6, 2006
i have a payroll grid. it has 3 cells with drop downs that have 39 options.
there codes for payments. i need a fourth cell to show a total dollor amount based of the codes selected.
separate question. i have a list of cells with the same four options (Job Types.ie service call, new connect...) i want to limit the cells mentioned before so that payment codes that do not pertain to the job type can not be selected.
View 9 Replies
View Related
Jul 25, 2014
I have created a table to record the daily hours worked by staff, however ,since the data I'm dealing with is quite large (over 300,000 rows), I need a function/formula to make populating the table quicker and less daunting.
View 2 Replies
View Related
Jan 20, 2007
I have multiple sheets in a workbook, and a want to sum the multiplication of two cells on each sheet on a total sheet. i.e. totalsheet!A1 = sheet1!A1 * sheet1!B2 + sheet2!A1* sheet2!B2....+ sheetN!A1*sheetN!B2
I need to do this for hundreds of cells, so mutiplying them on each sheet is not an option. There are enough sheets that writing out the brute force equation as above is too long. I know you can do total!A1=sum(sheet1..sheetN!A1) to add all sheet A1's together, but multiply and sum?
View 6 Replies
View Related
May 1, 2013
I am doing a list which has the same products returning several times, but with different values. Want to filter/make a new list, with only one of each product and the summed amount of that specific product. Summing the specific amount is not that big of an issue, but the creation of the list is, least in a smart way i have tried this:
[Code]......
This being the last possible entry for the summed list.
My problem is that the formulas is getting too big for my computer :S, since this formula is copied more or less 10 times.
Here is an example of what i want: Product list.xlsx
View 5 Replies
View Related
Jan 2, 2013
I would like to be able to create a reactive, searchable database. I have data that will be broken down into two divisions: Origin and Destination States. I would like to type in an Origin State, then a Destination State, and then have the document provide results for matching data. For example, if company 1 is in the origin state, but not destination state, its data will not be shown in the search result. However, if company 2 is listed in both the Origin State and Destination State, its data will be listed.
Addition - Attached is a small example of the worksheet that I would like to create. In the "SEARCH" tab, I would like to enter the Origin and Destination States. the search would yield all appropriate matches. In this example, if I entered Alabama as the Orign State, and Arizona as the destination, the search would provide the following match:
GreenLine
H&M Bay
Refrigerated Express
View 1 Replies
View Related
Apr 30, 2013
This is my invoice. I want to be able to create a database record of every transaction. In the end I want to be able to export this data into Access, but it needs to be in sequential rows and columns. I want to be able to list the cells on Sheet 1 that have Data in them in the configuration on Sheet 2 that I have listed. What I need to keep in mind is that someone might have 1 item in a transaction and others might have 5. So I need the ShopID and Customer information to appear in front of every item listed in rows 20-27.
A
B
C
D
E
F
G
H
1
ShopID Data
[Code]....
View 1 Replies
View Related
Jul 1, 2013
I am working on creating a medical master database. I have a master sheet with a huge number of columns. I have a couple of guys working with me, and they have their own sheets in the workbook.
So here is what I would like to do: anytime one of the guys enters data into his respective worksheet, I want the data to be copied over to the master sheet in a new entry.
The columns among the worksheets are different, and I would like the data to automatically sort in the appropriate column on the master sheet when it is copied.
I have attached a copy of the workbook.
I have included an example of what I would like to see. For example, if Charles puts in an entry in his worksheet, a new entry is created on the master sheet with the information from Charles' sheet sorted in the appropriate columns.
View 8 Replies
View Related
Feb 9, 2014
create an excel spreadsheet, where i can enter all my products name, unit price etc. so when i am doing an invoice for the customer, if i just type the product name price will show up automatically.
View 4 Replies
View Related
Dec 28, 2009
I am working on a label printing set-up for my work. So far I have completed the userform that formulas will be entered on and printed from. I had an idea of being able to save these formulas for recall later. I included “Save” and “Recall” buttons on the userform. My plan is to have my co-workers click on the “save” button and be able to enter an additional piece of data and have everything saved to another worksheet labeled “database”. If you look at the said sheet you will see a column for “customer”, “color” and then the colorants. Due to the wide variety of colorants and quantities available, I set it up so that I have the colorant and under that Oz and 48ths. I am trying to figure out a code that will populate that across the row.
Then I want to be able to push “recall”, find my customer and then color in a combobox and have that populate my label.
At this time I am stuck. Part of my problem is I don’t quite know the terminology to google.
Attached is the file I am working with. The only sheets that need concern anyone are "main" and "database". The others are from the original file and I will be deleting those once I am done.
View 11 Replies
View Related
Oct 3, 2012
I've got data in two columns (J and G). Column J contains product manufactured by companies in column G. Some products in column J are manufactured by more than one company in column G. I'm trying to create a conditional format to highlight products made by multiple companies.
My thought is that I need a formula that asks if the product is duplicated, AND the company is different, but I'm not sure how to do that.
View 9 Replies
View Related
Mar 10, 2014
I am creating a spreadsheet utilizing the National Vulnerability Database (NVD) from NIST.
I am successfully able to import the xml files and have the xsd mapped fine.
My problem is within each xml files for each records(1000s of records) there are sub pieces to certain record parts. I.e. software versions (that will be different per piece of software) however they will always fall under prod vendor
"
"
Excel creates a new row for each of these.
How can I make it only create the 1 row and comma seperate those?
View 1 Replies
View Related
Mar 20, 2014
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
View 4 Replies
View Related
Jul 19, 2012
I have a multiple sheet document, on the first sheet I have my daily data:
Name
ID#
Ward
TTO
Transport
Transport 2
Date
Time In
Time Out
Time Stayed
ex1
xxxxx
12
xx
Taxi
01/01/12
xx:xx
xx:xx
xx:xx
[Code] ........
This is updated daily and it shows patients that have been through the department, who, when and how long. This is then converted on sheet 2 and shows as:
Date
Number of Patients
01/01/12
2
02/01/12
1
03/01/12
1
My third sheet has a very similar layout to sheet 2 but is weekly, previously i have used the following to find a weekly total:
=SUM('sheet2'!B563:B567)
I now have well over 4000 rows and altering this sum to accommodate for each week is time consuming as sheet 2+ are updated to cover the following 6 months.
Is there a better formula or a way to use the current formula and drag it down to auto fill? Currently trying to auto fill does the following:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B564:B568)
=SUM('DAILY ANALYSIS'!B565:B569)
I want something like:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B570:B574)
=SUM('DAILY ANALYSIS'!B577:B581)
View 2 Replies
View Related
Jun 7, 2009
Can someone tell me what I'm doing wrong for the weekly sums in this spreadsheet? The monthly sums work fine.
PS I can't use pivot tables. This spreadsheet is a quite small part of a more expansive set of worksheets, from which I am pulling data.
View 7 Replies
View Related
May 25, 2007
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
View 2 Replies
View Related
May 1, 2014
I am trying to pick out certain bits of information from the below "example" set of data:
A
1
A
1
B
1
C
1
[Code] .......
My aim is to record the letters that are recorded against both numbers (note: in my data there are more than 2 sets of numbers). For the example above the solution would be:
A
1,2
B
1,2
...because these two letters appear against both 1 and 2.
There are some letters that are duplicated against the same number which is making it hard for me to work out. I don't care if the same letter appears against the same number, I just would like to know instances when a letter appears with a different number, and if possible what that number is.
View 3 Replies
View Related
Mar 16, 2014
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
View 1 Replies
View Related
