I have a colum with 350 cells in use, each of these cells contain a 3 digit number. Without having to go into each cell and type is there a way i can put the Letter
"R" infront of each of the 3 digit numbers?
For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).
When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.
=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies View RelatedI need to change a few hundred cells (one column) where the first letter may be a capital letter to a lowercase letter.
So:
GetAwardfromBid to getAwardFromBid
or
SmallLertter to smallLetter
Trying to word this right. I have one cell with a date of 01/01/2010. I have other cells that I want to be equal to this cell plus 1 or more months.
For example A1=01/01/2010
I want A2 to = 02/01/2010 based on one calendar month entered into A1. So if A1 changes 03/01/2010, A2 will = 04/01/2010.
I am trying to make sheet that will give me averages from different groups within a worksheet. In E64 I would like to have the total that is in I4 unless there is a total in I24 or I44. When I made the worksheet, it figures everything great but when I remove the data on following weeks it messes up the averages.
View 10 Replies View RelatedI need column B to self populate cells with "1" if a specific range of room numbers are entered in column A. Is this possible?
Column A, room numbers from all over hospital.
Column B auto populate "1" if rooms ranging from M570 to M590 in Column A
I have a list of names and a seperate list of names with emails.
In column A there is a last name, column B a first name, and then for the second list in column D there is an email.
I need to tell Excel that if column A and Column B both have a replication on the list, then in Column D populate it with the email address on the list.
I know this is confusing so I have attached a sample of my spreadsheet. The email address is in yellow and I need to have it copied to the cells in white. The list is almost 14,500 lines long and it would take forever manually.
I have a spreadsheet with a number of columns from a database export, where values in the column are either "TRUE" or "FALSE," Depending on whether or not the box was checked in the database. I am wondering if there is a way to populate a new cell with the text from the column if the value is "TRUE."
For example:
BOY
GIRL
John Doe
TRUE
FALSE
Is there a way that I could have another cell populated (I guess via function or macro) with "BOY," since the option for boy is true?
Code:
With wshfma
lr1 = 0
.Range("K2:N15").ClearContents 'early late staff rowsource
.Range("C2") = Format(hwmin1, "h:mmA/P") 'text!
With .Range("D2")
.Value = (.Range("C2"))
.NumberFormat = "general"
End With
Cell C2 of worksheet 'fma' is populated by a value linked from a textbox in a userform. It is a time value in text format (eg "8:30P").
This snippit of code is to populate cell D2 of worksheet 'FMA' with the value in C2 (8:30P), and apply the general number format to it. (I know redundant ... but this is just testing).
What is happening though, is cell D2 is actually being populated with the contents of another cell ... F3 to be exact.
I am creating a form where the user chooses a Region from a drop down list and a Level from another drop down list. I have a cell I want to populate with the amount pertaining to the Region and Level from a seperate spreadsheet within the workbook.
The levels for each region go from 1 to 12 and there are 4 regions from A to D. I have separated each region and named them GradeA, GradeB, etc.
I have a text box on a form and wish to update the next empty cell in the column when a add button is clicked the code I'm using is as follows:-
Private Sub AddButton1_Click()
TextBox3.Value = Sheets("Data").Range("A65536").End(xlUp).Select
End Sub
I am entering data into one workbook 1 (WB1) that contains a worksheet for every month. I manually extract some of the data from WB1 and put it into workbook 2 (WB2), which also contains a worksheet for every month. Is there a way to populate WB2 from WB1? For example, I want cell B5 from WB1 to automatically populate B5 in WB2, and so on. I have to update this every day and would like to way to automate it to cut down on data entry errors since they need to match. And both of these files are stored in SharePoint for others to access if need be.
I have attached a sample of the workbooks (yet only have one month in each of these wb).
I am trying to populate a text box in a user form when initializing the form. I have reviewed many posts in this forum regarding this problem, but have been unable to resolve. My code looks like this:
VB:
Private Sub frmFeed_Initialize()
ActiveWorkbook.Sheets("Log").Activate
Range("A1").Select
[Code]....
I am using the form control data validation drop down list with 9 entries in the list. How can I get the entry that is selected in the drop down list to populate exactly into another cell on a separte worksheet? I have linked the drop down list to the cell, but the value is populating as a number rather than the text entry that is in the drop down.
View 7 Replies View RelatedI have a userform which requires the user to enter the date a training course was completed. Initially I used DatePicker as I was running Excel 2010 and had that working, however the workbook I am creating will be used on about a dozen machines, all of which have different versions of Excel. To avoid needing calendar Add-ins to be downloaded and installed for this feature I decided to go the vba created calendar route. I am using one I found on this forum: [URL] .....
It works well for me using the example spreadsheet provided in the post but I am having some difficulty incorporating it into my code. I believe I have imported the appropriate forms and modules because I can get the date populate to the Label Box on my userform but when I try to put the Date into the spreadsheet along with other data such as name and course duration, the name and course duration populate but the date cell remains blank and execution completes without error.
I have tried playing around with the data type thinking I had some issue using the .Value attribute with a String but .Text gives me an error as does converting from String to Number format.
I will attach the workbook but for a quick look, the portion of the code I believe that is not working is in here:
[Code] .....
Training Classes Example Workbook.xlsm
I have two worksheets. The first contains a list of software (some having duplicate listings) in column A and a list of comments in column B. None of the software titles that have duplicates will have comments.
The second worksheet contains a condensed list of software (the previous list without the duplicates) and a column for the comments.
I need to have a formula that populates the second worksheet comments cells with the corresponding comments from worksheet one only if:
(1) The software title in worksheet two matches the software title in worksheet one
(2) The comment cell in worksheet one contains a comment (or text)
I have had only partial success; my obstacle being the duplicate software titles in worksheet one.
I want to create a sort of complex logging system for my work.
As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.
If I type a VLookup formula in Cell A1, the resultset goes into cell A1, as expected.
Is there any way to have a formula in Cell "A1" that populates a different cell, like Cell A6 that contains no formula ?
The reason I need this is that the user sometimes needs to type in manual data into Cell A6 and would overwrite the formulas. I was thinking that if I could have them actually type something into A1, it would populate other cells, such as A6, that did not have any formulas.
I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)
One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.
The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
I would like to do is have 2 listboxes. In the first listbox I would like the name of all the worksheets which contain the words "elective class: " in cell C7.
In the second listbox I would like the name of all the worksheets which do not contain the words "elective class: " in C7. I need this only to source from worksheet 7 onwards however.
The listboxes are called ListBox1 and ListBox2 respectively.
Also I am using this to print and I have a button which currently selects all the data in the first listbox and another button which prints all the selected data. The code being used for this is:
Here is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.
Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84
As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.
I have to loop through a range in A, and if the letter "C" or the number "9" appears in the cell anywhere (it won't be a whole cell value) then I need column B to show "C".
I know how to do a whole value loop, but I'm stumped on a 'find X anywhere' search.
I need to check if a cell ends with {"a","b","c","d","e","S","t","v"}. and if it doesn't diplay a warning
this is what i have
--------C
10----4444s
=IF(RIGHT(C10,1)={"a","b","c","d","e","s","t","v"},"GOOD", "Must enter Suffix Letter")
Is there a way to lookup the first letter of a word in the cell. I am trying to keep my sheet as small as possible for emailing. It would help to narrow down the possible lookup combinations. For example I only need to know if it starts with T, P, or V. I don't need to know the rest of the word. eg TMO, TAEFA, P1284, VTL3D etc.
View 2 Replies View RelatedI have a list of names and I need to know how many names are greater than 6 characters in length. What is the formula I need to enter?
View 9 Replies View RelatedI need an excel cell formula that will detect whether or not the word in a cell contains the letter 'R' or not.
ie. If cell A1 contains a word that contains the letter 'R' then return a '1' otherwise return a '0'.
I am trying to make a formula which will tell me if A1 is a postcode (a letter and a number e.g CV42 6AQ)
In the A column, it looks like this:
CV42 6AQ
FC45 D4D
West yorkshire
PR42 6RD
Etc.
i want it to identify all the postcodes, and NOT "West Yorkshire" because it does not contain a number.
I have one column: in the first cell comes text (beginning either with the letter "L" or "R") and afterwards, in the next cell, comes a number (either "7" or "8"). Basically the column is made up of alternating cells containing either text (code of a movie) or numbers (responses to the respective movies). I want to find a formula which writes either:
- "correct" if the number "7" follows a cell containing the letter "L" or an "8" follows a cell containing the letter "R";
or
- "incorrect" if the number "7" follows a cell starting with the letter "R" or the number "8" follows a cell starting with the letter "L".
Basically the 8 is always correct with an R and the 7 with an L: ...
