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.
I am a building a database where each row represents a record. Each record has a unique identifier number.
In worksheet "Record Search and Amend", I have recorded a macro to look up the data.
The problem is once the record is retrieved I need to be able to amend the record and save it back into the database in the right row. This could be another button to initiate this action.
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.
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.
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
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.
I need to amend this formula so match only cells with min values > 0.
View 3 Replies View RelatedI 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.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)
Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.
I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.
I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.
I have a csv file (Import.csv) containing rows similar to the following:
001032,117.5,19/09/2005,Ref1,Ref2-1,100,44100,#Sales#,,Import routine
001033,129.25,19/09/2005,Ref2,Ref2-2,110,44100,#Admin#,,Import routine
I also have a worksheet with the following list (which will grow):
Cost Centre Codes
AB1DepartmentCode2AdminADM3SalesSAL4StoresSTO5PurchasingPUR
Excel tables to the web >> Excel Jeanie HTML 4
The deprtment codes in the csv file are all contained within '#' markings.
I need a macro that will find and replace all department codes in the csv with the relevant Code from the list, and remove the '#'s. Unfortunately, some of the fields are numeric with leading zero's so I dont want to lose these by opening and re-saving the file in Excel as a csv.
The output for the above is as follows:.........................
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
This is a formula in an existing spreadsheet that I need to amend.
{=SUM(IF(WeekNo=2,IF(Workgroup="REASSIGN - CAP",1,0)))}
‘WeekNo2’ and ‘Workgroup’ are names defined and are offsets that define two separate columns.
I have created a new name ‘realclosetime’ and it is an offset to another column of data.
I have tried to change the existing formula so that it is not actioned if the ‘realclosetime’ is empty. It’s a date and time field and if it is not completed I don’t want to be including that row in the SUM calculation otherwise I end up with N/A’s.
I have a conditional format which uses this format MAX($N$84:$N$94) to colour the cell containing the number but I now wish to amend this formula so that if an identical number also appears further down in the column only the first of the cells containing the identical number(s) will be coloured but none of the others,
View 4 Replies View RelatedI am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and i have to amend a formula based on a new version. The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:
=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44 ,AL44:AL53),10))+1,"th","st","nd","rd","th"))
I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.
I tried this macro, written by Leith, to organize a workbook of over 100 worksheets, all named as a 3 digit number. (001, 002, 007, 004, 018, 12, etc.)
For some reason, there was no order to the sorting. Can the macro above be modified to sort my sheets in numeric order?
I have create a Command Bar, my problem I'm trying to understand is this
1.Can you amend the font colour of the any Captions on the CommandBar?
2.How can I get the Caption to pick up from a range and use the range name as the Caption?
I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code
I am trying to develop some data package for my office and created Userforms to add/input data, and amend/modify data. To input its working fine. But when I want to amend/modify the records its not working. I am new to Userforms, Codes. Can anyone help me by rectify me coding, for which I am attaching the sample sheet.
I have the following SUMPROCUCT in place and it works fine for current purpose:
=SUMPRODUCT(VLOOKUP($E$9,Rota!$C$4:$FU$369,{5,30,53,78,103,137},FALSE))
columns 5, 30, 53, 78, 103, 137 all contain text. However I need to amend some of these to text but I still need the SUMPRODUCT to add up.
Is there a way to amend the above formula so its counts both text and numbers in 5,30,53,78,103,137 ?
Macro below - it now works and selects all the data in spreadsheet and converts to Proper Case
However I also need a macro to select all except the last column of data and convert all except the last column to Proper Case
It is for Post / Zip code info. so wish to keep it in Upper Case.
What I have already is:
Code:
Sub Propercase()
Dim LastRow As Long, LastColumn As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Dim r As Range, c As Range
Set r = Range("A1").Resize(LastRow, LastColumn)
[Code] .....
I was hoping I could easily amend the above to select all except last column.
I have a Macro to protect all the Months sheets in my Time sheet to stop users deleting formulas, This year I've added a new sheet with a yearly calender so users can track Hols etc but when I run the Macro it protects this sheet "2007-2008". How can I amend the Macro so it protect all sheets except "2007-2008". My Macro is below:
Sub ProtectAll()
Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="KITCHENsink19591968", DrawingObjects:=True, Contents:=True, Scenarios:=False
Next ws
ActiveWorkbook.Protect Password:="KITCHENsink19591968", Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub
In my VB6 project I need to loop through several Excel Workbooks with the same chart format and amend the chart data as required from time to time. There are 2 charts in Sheet2 of every workbook. With the following coding, I can only update the chart in the first workbook but not the one in the second workbook and the rest. It appears that there will be no further updates after the first workbook is closed.
Option Explicit
Dim oEquityWB As Excel.Workbook
Dim oEquityWS As Excel.Worksheet
Public Sub AmendChart()
Dim arrWorkbooks() as String'Path & Filename
Dim nn as Integer
For nn = 0 To UBound(arrWorkbooks)
If Not EquityWBOpen(arrWorkbooks(nn)) Then GoTo Next_nn
Set oEquityWS = oEquityWB.Worksheets("Sheet2")
oEquityWS.ChartObjects("Chart 1").Activate................
A.) As a user is entering data into a userform two specific values need to be checked in the existing data sheet.
Textbox1 ( date)
Combobox (operation name) {4 tabs on from textbox1)
They relate to the data stored in columns 'A' and 'E' respectivly.
What I'd like is, as the user exits combobox4 to check if the values already exist. If they do; fill the coresponding textboxes with those values and allow the user to make any amendments, then have it SAVE to the SAME row, would a record number be necessary to accomplish this?
B.) For a future development, Im thinking of applying the same principle to a different project, where 3 values need checking.
Textbox9000 (date) Column 'A'
ComboBox1002 (Staff member) Column 'B'
Combobox1003 (operation name) Column 'C'
Possible complications are that below combobox1003 there will be 12 other comboboxes(a value title, from column header) with associated textboxes alongside(value previously entered by user).
There will be 22 possible values for the comboboxes(the values will be the same column headers) The user will only ever have the choice of making 12 entries though.
Does any one have any spare code laying round for this one..
I have a user form we are using to transfer data into an excel spread sheet. When transferring the date from a text field it is formatted as general, I need this to be a date format DDMMYYY. The cell needing formatting is N2
View 5 Replies View RelatedHow to amend existing code in a workbook that is used by many different users. I am working in Excel 2003. The existing code copies data from certain cells in the active worksheet (which is password protected) into a new worksheet and saves that new worksheet to a temp folder, sends it to a particular email address using outlook and closes it, returning the user to the first workbook. You will see that it also unhides then rehides columns of the first worksheet during this process so that the user never has to see them. Problem here is that none of the users are using outlook anymore and I would prefer that this new spreadsheet that is created just be saved to the users desktop. I have found bits and pieces of what I needed on the web, but I can't seem to put it all together (I usually end up being sent to the debugger just short of having the newly created spreadsheet saved to desktop). Here is the existing code:
Private Sub CommandButton1_Click()
Range("A2:G2").Select
Selection.ClearContents[code]....
Essentially what I need to do is circumvent the whole email bit and save the spreadsheet that is created to the users desktop instead of a temp folder.
[please refer to attached sample data]
The formula in K3 is
"=J15/10"
This formula calculates the average mailing return rate of all the mailings added together. J15 is currently white text to hide it from the end user.
Everytime I add a new row of data I need the formula to update itself automatically. So for example, the next time I add a row of data the above formula has to change to
"=J15/11"
Or if I were to add three rows of data the formula would have been
"=J15/13"
I have 4 columns, If column B (Ref No) is filled in, the adjacent columns, C,D and E become mandatory and an input box pops up on screen one after the other for entry into each column.
My problem is that I require a drop down list and not a input box for the final column (status). So i have created a userform with a combobox dropdown. I am having problems connecting the combobox selection to the final column, and for the selected combobox item to go into the right cell like the input box entry currently does.
Attachment 53209test743.xls
I have attached a sheet and also you can see the code below.
Code in Sheet
VB:
Public SaveVal1
Public SaveVal2
Public SaveVal3
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
[Code] .....
I am currently creating a simple macro to save out files to a specific folder on one of our network drives. Since we will be doing this often I would like to amend the date the the file name saved for sorting/organizational purposes.
Unfortunately I have been running into a few issues, this is what I have tried so far but keep getting a SaveAs error:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation TeamDC Tools" &
"Copy DC Conversion WB_2014 " & Format(Date, "yyyymmdd") & ".xlsx", FileFormat:=xlNormal
I have also tried:
Code:
ActiveWorkbook.SaveAs Filename:="vosinsharedClient Implementation Team
DC ToolsCopy DC Conversion WB.2014_" & _
Format(Now(), "YYYYMMDDhhmmss") & ".xlsx", FileFormat:=56
But still no avail. I believe it may have something to do with the file format but I am not sure. I am running Excel 2010.
I AM TRYING TO SUM OF EACH DAILY INVENTORY ITEM. PREVIOUSLY I USED FORMULA SUGGESTED FROM TEETHLESSMAMA (=SUMPRODUCT(--($A$5:$J$13=A19),$B$5:$K$13)).
BUT THIS FORMULA NOT WORK FOR NEW FORMAT OF INVENTROY DATA. I tried to make some change in it to get the result, which is not working well.