I have below format in excel and i will be updating scores for each parameter (column B).
I will be inserting a command/click button and when i click on the button macro should take the name, contact No., scores from column B and paste it in sheet 2 in the same workbook in one row, in the same way when I click the button the scores should be taken from sheet 1 column b and paste one below other row in sheet 2 each time i click on button.
Below is how raw data looks like.
Column A
Column B
Name:
ABC
Contact Number:
XYZ
Parameter
Score
[Code] ..........
Output expected in sheet 2 is as below.
Name:
Contact Number:
Parameter A
Parameter B
Parameter C
Parameter D
Total Score
Example Cell G1 = 10:00 want Cell H1 to show 9.15 Example Cell G1 = 9:00 want Cell H1 to show 8:15 Example Cell G1 = 8:00 want Cell H1 to show 8:00 no deduction Example Cell G1 = 6:00 want Cell H1 to show 8:00 no deduction Example Cell G1 = 0 want Cell H1 to show 0
i have product group,product name and the statistics. I'll use an example of students with score. I have these set of data:
A B C D 1 Student Name Score Sum 2 Student1 Anna 48 80=Sumif(A:A,A2,C:C) 3 Student1 Anna 32 80 4 Student2 Tom 30 80 5 Student2 Tom 30 80 6 Student2 Tom 20 80 7 Student3 June 55 60 8 Student3 June 1 60 9 Student3 June 4 60
Now we have 2 students with tied highest scores with 80 Scores. Naturally i want the rank to be as follow: Top1 Anna since she has the highest score "48", and Top2 Tom, and Top3 June.
The problem is, the score data can vary and Top3 can probably have the highest score and he still didnt make the highest score collectively. And there will also going to be other situations as well because im working on a very large data set, and not these 3 students.
the max score is tied, and since im making another column with sum scores, the data is going to be redundant, and hopefully theres a formula to ignore this.
I am using the Format as Table feature in 2010 and I am summing a range of cells (C2, D2 and E2) with the result in F2. I have linked F2 three rows below the table in cell C5.
When I add new data in the 3rd row in the table feature I can get an updated result in F3 but my linked cell does not update as it is now pushed to cell C6.
How can I have the linked cell update with the new total from cell F3 as it moves relative to the table?
if I change a value in the range named "Named_Range" this vlookup does not update.
This formula works but does not allow for any updates. Meaning the vlookup returns the original value even after a cell has been changed. "Named_Range" is on a different sheet but in the same workbook.
Auto calculate is on. I have recalculated the cell manually. I have Office 2010
I have an intensive VB program I've written that the user kicks off by clicking a button. I update a particular cell near the button with progress numbers so they can see what's going on. (This calculation can take up to 10 minutes.)
Sometimes, though, the UI stops updating. I know the calculation is still running because it completes eventually.
I am guessing Excel is getting too many updates to its screen (i.e. that cell) to keep up with the calculation going on in another thread, so it just gives up on the updating.
Is there a way to force Excel to keep updating the screen? I'm willing to give up the 0.01% slowdown in exchange for continuing to see progress. I see no need for the VB calculation thread to hog out the screen updater.
I recently switched to Excel 2010 and have a rather peculiar problem.Every week I update a bunch of charts in different workbooks. By update, I pretty much mean just shifting the range over one column or down one row to incorporate newly added data.
So I right click the chart, select "Select Data", update all the Data series ranges and then I click on the Horizontal (Category) Axis Labels button "Edit" to update the Axis label range.
I do all that then press OK. The data series have updated, but the axis labels haven't. So then I do the procedure again for the Axis Labels, hit OK again and voila: It worked.
But I ALWAYS have to do this procedure twice. It will NEVER update the axis labels the first time around. Even though the little preview window below the edit button show the labels correctly.
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment Is Steward EU ID Location Data Quality GE Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85 Cindy 50 60 100 Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
http://www.excelforum.com/excel-gene...cognition.html and im copying and pasting data from a website ( football scores ) and when i get what should be 1-1 it returns 01-jan and this i dont want i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages wondered if anyone could work out some syntax to use as a macro button? claymation had a go but it doesnt work.
I have a table with a header row of scores 1-10 in B4:K4.
In column A I have a list of locations A5:A68. People in each location were asked to score an event between 1 and 10. The count of their scores is under B4:K4, eg Location 1, 3 people scored 1 (entered in B5), none scored 2 (C5), 6 scored 3 (D5) etc through to the score for 10 in K5.
What I am after is the average for each location so that in L5 I can say 'for all the respondents for Location 1, the average was:...
This may be a bit more complicated than it appears as presumably there will be a requirement to multiply the number of respondents by their scores and then ... ?
But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?
I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
I have 2 excel workbooks - each containing one table.I have 1 Powerpoint Prtesentation with OLE Links to the 2 tables.
All I want is for any update in the excel tables to show in the running PP Presentation. The annoying part is when I set up the linked objects, everything works perfectly and any changes in the excel files update real time in the PP presentation.
As soon as I close the excel file and reopen though, from thereon in I have to update the links manually.
Things I have tried:
A PP Add in called "Update Links" which updated the OLE links on every rotaion of the show. This would be a fine workaround if it didn't stop the slideshow if/whenever someone was updating one of the source files with the "file is already open..." message...So set both source files to shared as PP will only be reading the data anyway. Still the error appears.Inserting some code into PP:
Code:
Sub linkupdate() Dim osld As Slide Dim oshp As Shape For Each osld In ActivePresentation.Slides For Each oshp In osld.Shapes If oshp.Type = msoLinkedOLEObject Then oshp.LinkFormat.Update Next oshp Next osld End Sub
All I want to do is update the links without stopping the slideshow. I have tried numerous PP approaches, but maybe there is an Excel solution that will update links automatically on each save.
I currently use a function very similar to this one with a SELECT query to return a field to excel. I have modified this into an UPDATE query to update a field.
I am getting errors at this point:
Code: If adoRS.BOF And adoRS.EOF Then DBNoteUpdate = "Value not Found" Else DBNoteUpdate = "Action Confirmation"
I believe this area of code was to trap the error that occurs when the lookup field name was not found in the table. I left it in because I still want to trap if the record I am trying to update is not found in the table.
Here is the full code:
Code: Public Function DBNoteUpdate(RecordNumber As String, _ UpdateTableName As String, _ UpdateNote As String) As Variant Dim adoRS As ADODB.Recordset If adoCN Is Nothing Then SetUpConnection
[code].....
The code does work, in that the record is being sucessfully updated by the SQL statement, but its erroring at the point I described above with the error:
Code: Runtime Error '3704': Operation is not allowed when the object is closed.
how 'adoRS' is closed at that point because the call to close is right at the end of the function?
I have two Excel sheets (test-excel-1.xlsx & test-excel-2.xls)The test-excel-2.xls will never be opened. When I update cells in test-excel-1.xlsx and save, I want them to automatically update to the test-excel-2.xls without having to open it. I have tried the "='[test-excel-1.xlsx]Sheet1'!$G$4" but that needs me to open test-excel-2.xlsI also want original values to be pasted in test-excel-2.xls rather than the code (this is later used in another program).
30 people in different countries use a sophisticated VBA Excel file on their local PCs, let's call it myexcelfile.xls. This file contains no custom data, as all data is stored in myexcelfile_backup.xls.
The biggest challenge with this set up is that whenever there is a newer release or bugfix of myexcelfile.xls, I need to send it out per email to 30 people and hope they replace it with my updated version.
Note that newer versions can replace older versions without any data loss, as all data is stored in the backup file.
Idea: with tools like WGET or BITSADMIN, I was thinking of creating a batch file, that would simply download the latest version from a FTP server, and replace the older version with the newly downloaded one.
Constraints: I shouldn't be using any software that is not native to Windows 7the files can sit in different folders on these 30 computers, therefore the update mechanism would have to identify the path they are in.the update mechanism would have to close the file first before running the update. Question: What is the best way to go about this? A DOS Batch file? Or another excel file that is just the "updater", meaning, it's only purpose is to download the new file from the web and overwrite the old file?
I have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue. This is how I'm connecting to Access:
Set cn = New ADODB.Connection cn.Open strConnect Set rs = New ADODB.Recordset rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable And this is the Query I'm using:
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I've got Excel 2002. I am devising a sheet for checking work (sheet A) which feeds onto a separate sheet (sheet B). If I wanted to delete info from sheet A can I retain the info on sheet B? I want sheet A to be used repeatedly for each case but the info for every case to be recorded row by row on sheet B.
I'm currently using Excel 2007 to get information from closed workbooks and updating my file.
First of all I create a table on selected range through "Insert table" command having something like this:
Then I have I piece of VBA code that tries to update every single cell of a column in this case it's updating %LD10 column:
But like you can see in first capture, the code updates every single cell with last value of variable fichero, having, at the end, the same value in whole column.
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
I have to complete his worksheet but now I am in a memory blank. I have the code set up but I guess I have been staring too much at this code...
I have 2 workbooks... One if for data entry and the other one is for a report.
So workbook1: the user will insert information on sheet1 and click a command button that will transfer the information on the next available row in Workbook2.
Workbook2 is strictly for report purpose. So all information transferred from book1 will be in book2.
I need a way, in book1, to have my userform (already set-up) to search for the matching information in my book2 Column A and update the information from my Book1 sheet2.
I have it set up this way:
VB: Dim wbArchive As Workbook Dim wsSrc, wsDst As Worksheet Dim rngSrc, rngDst As Range Dim rngReqNo As Range Dim strReqNo As String
I and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
