I have 2 excel sheets A and B
Sheet A has full list of (3000) of user names - First Name , Last Name and Email address
Sheet B has few UPDATED users (200) of user names from above list with UPDATED email addresses ( with First name , Last name and email address)
How should I update Sheet A with updates from Sheet B?
I am tracking my sales interactions and am trying to link cells so when I update on one sheet it updates my last "customer touch" on a master sheet. Current formula-
On "sheet1" I selected a cell and used this formula... =sheet2!$b$7
The problem is that I want to keep a history of what was in that sheet 2 B7 cell so i insert a row (making B7 move down and become B8) and enter new info into the new b7. if I go back to sheet1 the formula in the selected cell becomes =sheet2!$b$8 I want it to stay to =sheet2!$b$7 no matter what I do... insert rows, delete rows etc...
in short what i am trying to do is have a cell in sheet1 show my last interaction with the customer and keep a history of in sheet2-infiniti.... with the caveat that my last interaction on sheet 2 etc... is listed first (thus the insert row)
I would like to start by thanking all the people who have got this code to where it is now. There is no way i would have go here withough you.
For those who dont know im trying to get Andy Pope Picture Viewer into a Userform.
There are still some issues, The Listbox on the form needs to conncet with the Graph to move the pictures like on the sheet. And the Code for the Input picture from file or scanner needs to be embedded into the msgbox.
Also the Pictures need to be inseted into the graph when a new picturs is added.
A sheet containing all the daily tasks, and according to the tasks that I need my employees to work on a particular day I write YES in the column. The cells that have YES marked next to them are then reflected in a new sheet with the following formula:
=IF('PLANNING CELLS (P)'!C12="Yes",'JOBS LIST CELLS'!C12,"")
Obviously the formula is different for every cell.
The issue with this is that if i have a list of 50 items and i select only 10 (Marked yes) I have a lot of blank spaces in the new list. I need to remove these blank spaces from all columns automatically whenever updating. Is it possible?
I want create something (a macro), maybe ending in a button to activate or desactivate the update (relatively) of the charts and relative cells in one of various sheets i use!
If the button is turned off the sheet is quiet!
I have created a macro that copies information from various cell on one sheet to another. When I run the macro the screen flickers about 5 times. Is there a way to stop.
View 5 Replies View RelatedWhat I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
I have copied a sheet, moved it to the end and renamed it with a date that is in cell "A1"
Now after that process is finished I need it to update the date in cell "A1" of the newly created sheet with the next day's date.
I am stuck however referring to the previous sheet to update the date value in "A1"
i have two sheets, sheet 1 is a list of some items i sell on my website and the stock quantity.sheet 2 is a list of what my supplier has in stock.i want a quick way of my stock values on sheet 1 in column G to match the stock values on sheet 2 column E.
the product codes are the same (sheet 1 is column E for product codes, on sheet 2 the codes are in column B).
on my website (sheet 1) the products size options (column J) are more detailed than that of my suppliers (sheet 2 column F)
for example, my sheet (sheet 1) will say on some items for example MEDIUM: DRESS SIZE 10-12 but my supplier sheet will just say medium also my sheet (sheet 1) will say ONE SIZE or PLUS SIZE where as my suppliers (sheet 2) will be blank however this means that there is only one stock value to update anyway as there is only one size option
is it possible to magically make sheet 1 match the relating stock values from sheet two?
so on sheet 1, item 10035 Child Gangster Suit in size small would change from 7 to 13 and item 10001 Jumbo Syringe would change from 11 to 20
i would also need the updated values on sheet 1 to be highlighted so i know if any have been missed, for example my supplier may have stopped supplying an item in medium but obviously i would still have a stock value for it on my sheet so therefore that value wouldnt be updated so i would need to be made aware of this so i can then remove the option (which has to be done manually) on my website...
what im hoping to do is when i press macro button"click to sort" on sheet totals
it will automatically input the totals from "TOTALS" underneath the right name and put the date in column a the trouble is the cells are dependant on the sort so if i was to press "click to sort" now it would transfer this data to sheet "PTS AND DATES"
09/02/09 in cell a2
1146 in b2
861 in c2
860 in d2
849 in e2
806 in f2
the very next time i press it , it will put data into row 3.
I have two workbooks. In Workbook1 (96300,Temp.xls) are stored the data as follows:
Item No; Model; Description; Pieces; Unit price; Price Total (Formula = Pieces x Unit Price).
I use this code to get all data from this workbook to another workbook:
Sub SelectAllItems()
'Open connection
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & "96300,Temp.xls;" & _
"Extended Properties=Excel 8.0"
Set cn = New ADODB.Connection
cn.Open strCn
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.Open "SELECT * FROM [súpis$]", cn, adOpenStatic, adLockOptimistic
i = 0
For j = 0 To rs.Fields.Count - 1
ActiveSheet. Range("A1").Offset(0, j) = rs.Fields(j).Name
Next
j = 0
Do Until rs.EOF
For j = 0 To rs.Fields.Count - 1
ActiveSheet.Range("A2").Offset(i, j) = rs.Fields(j).Value
Next
i = i + 1
.MoveNext
Loop
End With
End Sub ...
I have two worksheets in my workbook;
1. Table
2. Raw / Source data.
I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
How to Update the Worksheets field value based on updates done in Main Worksheet.
As i have two different worksheets (Which can be even more then 5) and i want to update the Column Field value of "State" in work sheet "B" & "C" based on the updates done in "State" Column Field value in Worksheet A.
I just want to update the "State" Column Field value in Worksheet "A" and it should update the "State" Column Field value in Worksheet "B" & "C". Only the criteria to match is Number. Based on number i want to search the values in Worksheet "B" & "C" and want that these values also get updated.
I have date on Sheet 1 and Sheet 2
what I want to do is take column A from Sheet 1 and compare to Sheet 2 column A if it's one Sheet 2 then on Sheet 1 column C and D post the results...
ie
Sheet 1
A B C D
ADDYY 64
Sheet 2
ADDYY4644454654
so then Sheet 1 would look like this
A B C D
ADDYY 64 ADDYY4644454654
Macro Button to copy and paste from different sheets, I need to Copy SKU which is in Sheet 1 Column B and Find in Sheet 2 then Copy the UPC on Sheet 2 and Paste to Corresponding SKU in Sheet 1 Under Column C. If an SKU doesnt show on Sheet 2, it should leave Sheet 1 Column C blank and go on with others.
View 3 Replies View RelatedI have a sheet1 with following column name Ab,Bb,Cb,Db,Eb,Fb and sheet two contains Ab,Xc,Eb,Sv,Db,Fb,Gm,Cb,Hb these headings are on 7 line of both excel sheet.
Now I want to check each cell under column name Ab in Sheet1 and compare it with all the cells under column name Ab in Sheet2. If both matches then update Cb,Db,Eb,Fb of Sheet1 to the corresponding columns in sheet2 for that column name.
I have set up 2 sheets with one called "2007" and the other called "Updt" What I would like to do is when I change the value in "2007" Tab to say "2008" or "2009" etc. it would update corresponding cells in the "Updt" Sheet i.e. Cells B2, B3, B4, B5, B6, B7 and B8.
View 6 Replies View RelatedI am entering data (exam results) in one sheet for a form class (each form has a sperate sheet) but want this data to be updated in another sheet called "combined" which is a full list of all the data for the year group of students. How would I do this? I have copied the information into the combined sheet using paste special but it wont update?
View 4 Replies View RelatedI create daily report for dispatch documents to our various branches. Now I am looking for formula (or formula is not possible then VBA code) that will auto update document number and dispatch date in "output" sheet.
Find attached sheet with desired output.
I have 2 excelfiles. The macro should copy the Sheet "SDH" from file "New file.xlsm" and paste it to the file "FileforUpdate.xlsm".
"FileforUpdate.xlsm" has already a Sheet called "SDH". This have to be deleted before past the sheet "SDH" from "New file.xlsm" .
After Copy, delete and past it should save the "FileforUpdate.xlsm" in it's folder and close the WB.
I have been playing around with this and are totally baffled,userform service,enter pl094 in combobox1 ,first part looks up sheet1 and works well.
If I change overhauled date it changes on sheet1 ,but will not write to sheet repairs,has done in the past as you can tell from entries, have tried irow and c.row but for some reason will not write any more entries.
1.
column b should autofill the last number in column i
in this case b12 = £1820.58
and then evertime column i has data in it should go to the next row in column b
2.
i would like column d to be blank until there is a stake in column c
3.
the same for column f, h and i
4.
column l should have the date auto entry after column c has data input
5.
column n should automatically work out how many days this has been running
6.
sheet 2 cell g5 should know how many records are in sheet 1
7.
sheet 2 cell g6 should return all known "y" in sheet 1 column g
and g7 should return all known "n" in sheet 1 column g
column i.........................
Basically I want to set up a workbook, which will have a sheet called "Database" and then sheet1.
I want to use sheet1 as a "add to database" sheet, where users will simply dump in some data into sheet1 and the marco will format it so it matches the headers of the data base and then it will add/update the database sheet with the info (and new info if its new) from sheet1.
Here it is in pictures.
This is basically what the database sheet will look like (but much much much more rows of data)
As you can see this is a job database with some columns for POD, Connote, etc.
This is what the update sheet1 will look like when someone dumps some new data in (I can easily write a macro that formats it to match the database)
So basically I want the VBA to merge this sheet1 data into the database sheet, as you can see, there is simply a few 1's added to various columns for "Car Dudes" and "Robin Manufactures" but "Jimmys Lollies" row is completely new and not in the database.
So the marco would basically match up either the Job number or the HB number of the rows in sheet1 and if there is a match in the database sheet it will update the cells data, and if there is no match it will add the row to the database sheet.
The issue is the data in sheet1 might not be fully complete all the time, it might only have a job number and not a HB number, it might not have a client name or whatever. Basically the data users dump into sheet1 will come from many different reports, many different sources, what I'm trying to do is essentially merge all this data into a common formatted database sheet, for further working.
So in a nut shell, a macro to:
1. Match Job/HB numbers in sheet1 with rows already in database sheet and update row accordingly with any new data.
2. Add any new Job/HB rows from sheet1 into database.
I have a sheet, call it a Master Sheet, where column A either has the word LOAD or DISCH in each cell descending. Each row has pertinent information in it that ranges from A1:L1.
If A1 contains LOAD how can I get that particular row to go to another worksheet called LOAD or another sheet called DISCH same range. I want to use only the Master Sheet without having to copy and paste from one sheet to another.
Excel spreadsheet with approx 50 sheets with a summary page at the front.
Have put in hyperlinks to go to a sheet, but if the sheet name changes, the hyperlink doesn't update to reflect this.
Hence, broken hyperlink.
I have created a pivotchart and placed it on (say) sheet " GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".
Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.
However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day?) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.
The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.
However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the target. name, its blank!
I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.
Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.
I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
msgbox Target.name <---------ERROR (Blank!)
If Target.PivotFields("BusinessArea").CurrentPage = "XXXXXX" And _
(Target.PivotFields("BM").CurrentPage = "AAA" Or _
Target.PivotFields("BM").CurrentPage = "BBB" Or _
Target.PivotFields("BM").CurrentPage = "CCC" Or _
Target.PivotFields("BM").CurrentPage = "DDD") Then
MsgBox "Error - " & Target.PivotFields("BM").CurrentPage & " is not a valid Business Manager of " & UCase(Target.PivotFields("BusinessArea").CurrentPage) & vbCr & vbCr & "Please try again!!", vbCritical, "Error"
Exit Sub
End If
..graph reformat code (irelevant because even If i delete this it still bombs out!"
End Sub
I am doing a project to maintain employees details with Excel as database and HTML/JSP as frontend.
how to start about maintaining EXCEL as Database.
We use an accounting purchasing program that stores it's data in Access format. I have an Excel sheet that does a query to that data source and returns inventory part numbers, descriptions and pricing.
This file is about 3mb. Using this file as a data source (was trying to make it portable so the sales people can take the data with them) I created a quoting sheet, where they can lookup parts and have all the info pull into the quote so they can give customers a quote with fairly current info. At first I had my search routine copy and paste in links to the info, but that created a problem when the sales person wants to open an old quote to see what they gave someone. So then I had it just paste values, which works fine except when the quote has expired and the sales person wants to keep all the same parts but wants current pricing. I was thinking something like index(match) but VBA code wouldn't let me do that. I was hoping to write code for a button that they can click and it fetches current info.
I have two worksheets. one has a list of formulas which i manually enter or change as time goes on. the other spread sheet has the same list of formulas, but I just chose which of those formulas I want to use frmo the drop down list provided to me by using the data validation of the other page. Each row that I enter by selecting the correct drop down options, i would chose a product name alogn with it. so its either Service or Fix. The question i ahve is, is it possible to adjust the manually entered formula and only have the formula rows where the service words are in another column but same row updated?
View 9 Replies View RelatedI need to fill in the third column on sheet 2 from the 3rd column in sheet 1. do I need an IF statement combined with vlookup??
sheet 1
mark l XYZ l 45
katy l ABC l 3
mark l ABC l 6
katy l XYZ l 45
mark l DEFl 4
sheet 2:
mark l XYZ l
katy l ABC l
mark l ABC l
katy l XYZ l
mark l DEF l