I need to ensure that all text entered into columns G,J,K and L of a specific spreadsheet are all UPPER CASE.
Someone pointed me to this link VBA TO CONVERT TEXT TO UPPERCASE which does it for the full sheet but I need column specific. I am using Excel 2010
To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case.
I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.
how to tackle this in excel vba using a macro. I want to force the first letter of each cell in Column A to uppercase until it reaches a blank
View 2 Replies View RelatedExcel 2010. I have a field that is color formatted based on the value of another field, which is calculated.
The conditional formatting does not refresh when the value is recalculated, but when I go away from the tab and come back, it works fine.
Any simple VB code to refresh the conditional formatting upon a workbook change?
I import a CSV file into Excel where the column title row has column titles that are just one long text string, without any spacing between the words. For example:
CompanySiteDescription
CompanySiteExternalSystemID
IssueNumber
I would like a method (formula or macro) that would add a space-character before each uppercase letter (that's not the first letter in the string or an uppercase letter that directly follows another upper case letter). Thus:
CompanySiteDescription becomes Company Site Description
CompanySiteExternalSystemID becomes Company Site External System ID
IssueNumber becomes Issue Number
Is there Code for forcing all text in a certain range to Uppercase?
Example: A1:E20
if possible I need the code to be just inserted into the sheet code. Maybe were when the user moves to another cell it changes them then.
I am my excel worksheet (excel 2010) I have one cell that changes every day (number). I want this number to open my htm document and replace the same number in a string in the htm and save/close this.
An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx
And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"
I have names that look like this:
SmithJohn and i want to seperate into two cells so it looks like
Cell 1: Smith
Cell 2: John
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
I have two columns (A and B) with the same data. Column A is missing a value that is in column B.
Column A has 11330 rows whereas Column B has 11331 rows.
I am trying to do a comparison on the two columns to determine which value is missing from Column A.
I am using Excel 2010.
I could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.
View 3 Replies View RelatedOn my "Order" sheet, I enter an account no. that looks up the company name, address, etc on my "Accounts" sheet. Below the address info on the "Accounts" sheet is a list of the salespersons with their email address.
After entering the account no., I want to enter the salespersons name and automatically look up their email address. I can make the lookup function work if I tell the formula which column the salesperson is located in, but am lost how to write the formula to locate the salesperson based upon the column no. that contains their company info.
It's not pretty, but this works.
=IF(F1"",HLOOKUP(F1,ACCOUNTS!A1:Z100,ORDER!G9, FALSE),"")
F1 - contains the account no. entered on the order sheet.
Accounts!A1:Z100 - range containing columns of account info.
ORDER!G9 - This is the row number value I obtained by using a match function to lookup the Salespersons name entered in cell F8, then I add 1 to render the value of the row that contains the email address located directly below the salespersons name.
=(MATCH(F8,ACCOUNTS!B1:B100,FALSE)+1)
Herein lies my issue, the salesperson name will not always be in Column B
I can write another Match to obtain the column no, but then I am using another cell to hold a calculated value for my lookup statement.
ACCOUNTSABCD1CODACCT1ACCT2ACCT323 COMPANY 1COMPANY 2COMPANY 34ADDRESS ADDRESS ADDRESS 5 CITY STATE ZIPCITY STATE ZIPCITY STATE ZIP6TELEPHONETELEPHONETELEPHONE78910TOM JONESJANE DOEMARY SMITH11tjones@company1.comjdoe@company2.commsmith@company3.com12SALESPERSON 2SALESPERSON 213salesperson2@company1.comsalesperson2@company2.com14SALESPERSON 315salesperson3@company1.com
Excel 2010
In words, Find Mary Smiths email address, she works at the company with the account number in cell F1 on the order sheet.
I am using Office 2010. I have a list of names in column A and a list of URLs in column B and I'm trying to find which ones have a match or partial match at least.
ex. (the name is in column A and the URL is in column B)
1 youtube www.youtube.com
2 Mr excel www.mrexcel.com
3 Wine Lover www.winelovers.com
4 Brian Jones www.ilovecats.com
In the example, I would like to highlight (or something) #s 1,2,3 as matches.
I have 2 columns directly next to each other each containing dates.
Example 1:
F2 = 5/23/13
G2 = 5/23/14
Example 2:
F3 = 6/6/13
G3 = 4/11/14
I wanted to do a conditional formatting that would highlight the cell in
column G if the Month/Day doesn't match the Month/Day in column F
Is there a way to do this with Excel only looking at the Day/Month and ignoring the year??
I am trying to separate two rows to two columns. How can I do this for +200 data. For example,
1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.
show me how I can do this for Excel 2010.
I have a spreadsheet with standard rows and columns - nothing elaborate. There is a section with a column for each month of the year, with 10 line items showing monthly totals for various business functions. What I would like to do, if possible, is ... instead of showing all 12 columns (making for a very long spreadsheet), I would like to show only 4 of the 12 month columns (in a window) and scroll through the months with a scroll bar.
View 3 Replies View RelatedI wish to select all the cells which contain the word " Available " in column A at once . But i didn't find any option to do so using excel's Go TO Special feature.
I am using Excel 2010.
I have seen in a number of spreadsheets that you can create a drop down list using data validation that hides certain columns depending on what you select. this leads me on to my question...
I have created a Gantt chart for an entire year and what to put a drop down box in C1 that contains four selections (Q1, Q2, Q3, Q4) to represent the four quarters of the year. I want to work it so that when the user selects Q1 it hides the columns where the other Quarters of the year are kept so you can only see that quarter.
If that is possible I would then like a second drop down box that allows the user to select a specific week.
Q1 is columns AW:DI.
I'm using excel 2010.
I have to construct a financial model for Senior Executives to show year to date spent amounts. I have my worksheet as follows:-
Cell A2,A3,A4.. to A100 has - Account Numbers (Ex. A/c. 4100..)
Cell B1, C1, D1.....has Jan2011,Feb2011,Mar2011.......and so on till Dec2011.
Cell B2 onwards, down and to right, all spent amounts by month
What I need is a formula to get year to date number, which will change to Executives requirement.
Cell-ABCDE
1Jan2011Feb2011Mar2011Apr2011
24100100100100100
34101200200200200
44102300300300300
Year to dateMar 2011( Months will be changed)
Account 4101( Accounts will be changed)
Amount should be 600 What Formula ?
My excel version is 2010.
I have a worksheet with
Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)
There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.
I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.
For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.
I am using Excel 2010.
Name
Primary
Secondary
Tertiary
Person 1
x
[Code] .........
I have a table in columns C:J, starting in row 7. I have a macro that selects and copies the whole table (after I filter the table) and pastes to another sheet. Recently I've realized that I can only copy/paste C:I or it will mess up my formulas during the process.
This is the code I have right now, which works beautifully for the whole table:
Code:
ActiveSheet.ListObjects("Table36").Range.AutoFilter Field:=6, Criteria1:= _
"=Alan", Operator:=xlOr, Criteria2:="="
Range(ActiveSheet.ListObjects(1)).Select
Selection.Copy
What I need is to know how to copy everything in the table except column J. I can't just copy a certain range cells because the number of rows is variable. There has to be a way to modify the ActiveSheet.ListObjects("Table36") right?
Using Excel 2010.
How do I freeze the top 6 rows and the first 3 columns in excel 2010 work-sheet so when scrolling they remain static.
View 2 Replies View RelatedI have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?
Excel 2010
I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.
View 9 Replies View Relatedi am trying to come up with VBA code, sheet is attached. i have some columns that have headers but rows are empty . so the VBA should delete all the se columns entirely and leave those that has headers and have data in rows.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVW1AccountUnitFund CodeDepartment
ActivityAnalysisTypecodedeskitemBegin DateQuantityUnit of MeasureAmount
CurrencyJob CodeEntry EventParent Budget Entry TypeOptionsLine CodeFunding
SourceFacilities and AdministrationCost Sharing2
[Code] ........
I have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.
25,449.47
25,451.65
25449.47
The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.
I am sure I need to use the Conditional Formatting, but cannot figure the formula.
I would like to combine 2 columns, containing numbers on it, is it possible?
I have found the other option how to do it
=A2&B2 but then this doesn't work
I have
COL A COL B
12345 0000
The result I want would be= 000012345
I have following sheet:
Data from A1: BU1644
I need to check, if in A1:BU1 are any duplicate words.
All the formulas I found deal with finding duplicates downward (like A1:A1000).
Have not seen any formula which works across (from left to right)
Is there an easy way in Excel 2010 either to tag/ and -or remove the duplicate which I could apply and then just copy downward? The formula must work from left to right, because many words repeat downward.
I have two columns of data; one is in inches, one is in metres. If someone is inputting data from an original source that has the data in metres I want excel to automatically calculate and input the data into the inches column. Additionally, if they input the data from the source in inches, I want excel to convert it to metres and input into the other column automatically. The conversion factor from inches to metres is * by 0.0254. I am no expert but after looking for this on the internet I am sure this requires some sort of VBA event code (although I don't really know the first thing about VBA). The excel columns will be in the same worksheet. I don't have a circular reference like I would do using normal Excel formula? I am using Excel 2010.
View 2 Replies View RelatedFor some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
View 1 Replies View Related