If I enter the city "Dunn" it auto capitalizes it. If I type my name "mark" in there it doesn't. I don't want it to auto "do" anything. How can I turn it off? I've tried copying into a new book and that didn't work so it doesn't seem to be formula driven. The issue is happening on a coworkers machine, but I cant seem to recreate it on mine. Hers is 2007 while mine is 2010.
View 9 RepliesI have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
I am trying to lock out cells if a value is entered in another cell in Excel 2010. For instance, when entering a value into N7 (merged Columns N7,O7,P7 into one - not sure if that matters) I want Q7 (same thing, merged Q7,R7,S7 into one) and T7 (merged T7,U7,V7 into one) to be locked out.
So, if N7 has anything typed into it, I would like Q7 and T7 (merged) to be locked.
If that's possible, I would also like for it to happen from rows N7 - N21 ...
Is this hard to do? My understanding is that it's only possible through VBA ... am I accessing the script correctly? (right click the 'sheet 1' tab [named Issues log], select 'view code').
For Example...
In Excel:
Question Answer
What is his Name? John
How many apples did he buy? 8
How much did the apples cost? 50
In Word: John is in cell b2, 8 is in b3, 50 is in cell b4
John bought 8 apples totaling $ 50 dollars.
Based on the information typed in the answer column (excel spreadsheet) I would want a word document to automatically generate a sentence.
I have searched and found many examples of code that will lock cells once data has been entered. But for some reason, I cannot get it to work in my workbook. The weird thing is, I have successfully got them to work on blank workbooks. I unlock a range I want people to be able to edit, I protect the workbook, I enter the code, and it works. But I do the same thing on my workbook, nothing locks.
I've tried locking all the cells on a sheet, and only unlocking a small range, as in the examples (A1:A10), and I cant get it to work. I've tried not pre-protecting the sheet, I've tried 4 or 5 different examples of code. If it wasn't for the fact I've gotten it to work on blank workbooks, I'd think VBA was disabled or something. I can't post a copy of my workbook for you all to look at because its on a stand-alone computer at work (and the USB is disabled for info-sec).
Excel Version 2007
I have a table created in Excel 2010 by a SQL query. the query pulls 3 columns of data - Resource Name, Contract Company, Labor Category. I then add a column called "KEY". KEY is derived using VLOOKUP. VLOOKUP is matching the Contract Company from the table created with SQL to a table called "Rate Key" in another worksheet in the spreadsheet based on the matching the Contract Company. That all works fine. If the table changes due to changes in data coming from the SQL db, the column I've added using VLOOKUP recalculates correctly. Then I add 2 more columns. One is called RATE - which is a number that I manually type in. The last column is called RCode and is calculated by the RATE times the KEY. Whenever I refresh the SQL query, all of the the calculated rows work fine; however, the data in the RATE column that I manually enter does not move - it stays with the row regardless of whether or not the name changes when the new SQL data comes over. How do I get the values manually entered in the RATE column to move with the correct row when the rows change?
View 2 Replies View RelatedMy Excel 2007 has "auto complete with a drop down list". When I type in the first character, a drop down list appears listing all the entries in that column tha start with that character. I click on one of the entries and the cell is "auto completed" with that entry. Sure is handy. I try to find that functionally in Excell at work, Office Professional 2010, and no bueno for kaki.
View 3 Replies View RelatedI have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VBA code
View 4 Replies View RelatedI would like make a cell in a report auto update with the most recent data entered in another cell from an input table either in the form of a formula or code to ensure that the most recent data is recorded and reported.
View 3 Replies View RelatedI have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 I want to create an auto summary or something.
Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.
Also I am using Excel 2010.
I've only recently began to use excel, but I've really dived deep into it, I'm clueless when it comes to VBA but now I'm stepping into that realm. Anyway, I'm using this code....
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
Me.Sort.SortFields.Clear
Me.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Me.Sort
[Code].....
Code:
Credits to VOG
Link: auto-sort for excel 2010.... to auto sort 2 columns of data. However, it is extremely tedious to enter data as it sorts itself every time you enter a new data point. Moreover, using any random number generator to fill the columns takes ages! Is there anyway to make it so the code only runs when the worksheet is refreshed (F9 pressed.)
It copies one of the work books, Saves it and then emails this as an attachement to a few people.
All users are on Excel 2010, however the file originally was created in Excel 2003. The File Extension Was .xls as was the attachment.
First time I sent the email this year it came through corrupt. No changes had taken place in the workbooks or the code.
I have now updated the spreadsheet and saved it as a .xlsm
I have also updated the code so the attachments are .xlsx
The email attachments are always corrupt.
Code:
Sub copy2()
Application.ScreenUpdating = False
Dim fname As String 'filename
Sheets(Array("To Order", "WO To Chase", "PO To Chase", "Contact", "WO Report")).Copy
fname = Year(Date) & " " & Month(Date) & Day(Date) & " Chase & Order" 'filename!!
ActiveWorkbook.SaveAs FileName:= _
[code]...
where the corruption might come from? Its not via the email as if I go to the saved file its corrupt as well.
I have workbook I would like to auto save to PDF copy file in different location every time the original file is save maybe some VGA code is this possible ???
View 1 Replies View RelatedSee post below. I want to have the column listed as per to auto sort to the largest to smallest value. This value will change throughout time.
I have data in columns a-n and rows 3-14. I want column "l" or the 12th column to auto sort from largest to smallest value.
Recently ran into this problem where when I select(highlight) multiple cells, it automatically copies the highlighted cells.
[URL]
This auto-copy also occurs periodically when I select(highlight) a single cell.
Notes:
1. I do not have a copy hotkey on my mouse, as this also happen when I use the trackpad.
2. This only happens in excel. When I select multiple items on my desktop, it does not auto copy.
I have made a custom list in Excel 2010 so it auto-increments the alphabet using the auto increment pull down/copy square on the bottom right of a cell in Excel. My question is I have some data in a sheet such as this
600
600
600
600
601
601
601
601
602
602
602
What I want to do is add a letter of the alphabet to the end of these numbers and have the alphabet auto increment based on the data above like this:
600
600A
600B
600C
601
601A
601B
601C
602
602A
602B
I thought it would be simple since I now have a custom list but every formula I try fails.
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010
[URL]
I have a question on how I could populate data using a combo box selection in Excel 2010.
For example, I have a table with values in Sheet 1, & below that table there is a combo box whereby another table of values can be populated based on the selection of the combo box.
Maybe to make it clearer...
Table 1
Name | Address | Phone number
Andy | Avenue 2 | 999
John | Road 5 | 998
Combo box (selection of names): John
Data derived from combo box - Table 2
Name | Address | Phone number
John | Road 5 | 998
how I could solve this Also, do let me know if this can be done without the use of VBA.
Code:
ActiveSheet.Range("$A$1:$AM$14502").AutoFilter Field:=1, Criteria1:= _ "30/06/2013"
When I run the above on my Worksheet to Filter for Rows not equal to 30/06/13 this works fine
I want amend the Criteria1 to a string that is derived off a value in my worksheet I have done this as below where Range("D8").Value is 30/06/13
It is deleting al my data and not keeping the rows with 30/06/13 in it.
Code:
Dim rng As Range
Dim LastQtrDate As Date
Dim LastQtrDateString As String
LastQtrDate = shtControlTab.Range("D8").Value
LastQtrDateString = "" & LastQtrDate
[Code]...
I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.
While all of the above activity is going on, new customers are continuously being in added in G4, G5....
Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.
I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...
Auto lock cells after data entered and SAVED.
Auto Lock Selected Range of Cells After Saving
I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.
This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.
This is in Excel 2010
I have, what I believe a fairly complex request.
I have 3 fields that will be entered manually per row
Frequency in weeks = FQ Example FQ = 4 weeks
Duration in hours = DUR Example DUR = 3 hours
Start Date in Date Format = SD Example SD = 01/15/14
I have a series of columns starting at December 30, 2013. This represents the week of December 30th 2013.
It continues on until I hit the last week in December of 2015 which is 12/28/15
There are multiple rows with the 3 entries listed above (FQ, DUR & SD).
I need a function or macro that takes the Start Date (SD) for the specific row and finds it's location in the series of columns (for the example of 01/15/14, it would land on week 01/13/14). The program would enter the Duration (DUR) in that Column's Row (if that makes sense).
The program would then add the Frequency (FQ) to the Start Date (SD) and look for the next column (or Week) that it would fit in and once again enter the Duration (DUR) in that cell.
It would do this until it hits the 12/28/15 end date.
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
I am looking for a way to auto sort the info in column A based on the numerical account number. The auto sort also should move the rest of the info in the row along with column a. I want this auto sort to happen once a new entry has been entered with all the information in columns A -L.
Basically the goal is someone with very little knowledge about excel or computers in general can enter information in all 12 columns and then the table will auto sort itself based on the account number.
Is there a way to auto insert a cell Comment when a particular value is entered and then have the Comment copied to a cell in another worksheet in the same workbook?
The value entered into the cells i want this function to work with is from a Validation list. If the cell's value was to change, for instance the user going back to a cell to change the value for whatever reason, I would like the previous comments to remain and then give the user the choice to edit comment or not.
I've had a look in the Forums using search but nothing close seems to be forthcoming.
setting up a worksheet that forces a link to another worksheet when text is entered.
For instance, I will have several columns in the first worksheet (Sheet 1). For each entry, a tally ("X") will be added under the applicable columns. Most of the column headings are pretty straightforward. To keep things uncluttered, I want one of the columns to have an auto-link (?) feature so that when the user adds an X in this one column (we'll call it Column D), the user will then be auto-linked to another worksheet (Sheet 2) in the workbook. Sheet 2 will have room for more information regarding Column D, Sheet 1.
I am trying to build a Calendar that pulls all of it's entries from data submitted via user form. To be more clear, I built a basic user form in VBA where I can submit this data: name, brief description, Time/duration, and date. Instead of the data I enter into the form populating in specific cells in my Excel workbook, I would like for the information to be populated into a calendar based on the date.
View 1 Replies View RelatedI am building a budget spreadsheet and would like the date to automatically populate in column H when data is entered into rows J through AN, or alternately update column H when a sum total is populated in column I
I need to repeat this for rows 4 through 63
I was trying to use the following code, but cannot get it tweaked right.
[Code] .....
I have 30,000 lines and in each line I have words that have all capital letters.
What I want to do, is make only the first letter in each word capital, and the rest lowercase.
I have a web query that returns a city and state, but the last character of the state is lower case. I need VBA code to change the last char to upper case. Here is what I have so far:
Code:
tmp = Cells(4, 1).Value
Mid(tmp, Len(tmp), 1) = UCase(Right(tmp, 1))
Cells(4, 1).Value = tmp
Is there any easier way to do this without 3 lines of code?
I am trying to use VLOOKUP to auto-populate a description from an entered part number. After checking up on how to do this in several different places I applied this formula to the relevant cell but all that it returns is #N/A.
I am very confused as all seems to be correct, but I am new to this and I am sure I am missing something silly. :P
On entering a part number into cell C13 on sheet 'Stores Receipt' it should search and find that number in column A on sheet 'Product List', it should then return the adjacent description from column B on sheet 'Product List' and show this in cell C17 on sheet 'Stores Receipt'.... Sounds simple hey! :D
The formula used is: