This is really annoying, my formula is not picking up words starting with the letter "Y" it will pick "Y" up on its own but not if you start a word with that letter, see my attachment and change the word "Alpha" in column A to "Yoda" or something begining with "Y" and you will see what i mean.
View 2 RepliesI have a cell that is acutally a concatination of several other cells. The formula is something like this....
=A1 & B1 & A1 & C1 & A1 & D1 & A1
Cell A1 contains the formula =char(13). This forces a line break between each data element.
My problem is that on some peoples machines the cell displays with a square at the end. I'm not sure why it does this only on some machines. Is there a better way to do this or is there a way to suppress the square?
I'm trying to validate a string that can have only dots and numbers, but there can't be two dots in a row. String has to begin with a number and end to a dot
So these are ok
1.1.1.1.1.1.
and
2.452443.1.5.21.5.42131.
but
1..1.
and
1.1.t.
and
.1.1.
and
1.1
are not ok.
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 RelatedFor 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)),"")
I have attached a sample file where I want to concatenate data in colums A2:H2 into one column with line breaks. But CHAR(10) function alonwith CONCATENATE is not working. I have formatted the destination cell for wrap text.
View 9 Replies View RelatedI want to create the user defined function in excel in which i will assign the value to a char and after that i should b able to use it in formula.
Eg: for "FL" i will assign 1
for "LF" i will assign 2
for "VV1" i will assign 3
for "VV2" i will assign 4
and so on.......then i want to use this to compare both figures like
i will put the formula like if(VV1 < VV2,true,false) result should b true coz VV1 is having value of 3 and VV2 is having 4.
So basically how we create the custom lists i want to give the priority to my own list and want to use it in formula.
I just found out the the line
Range("a1") = tbox.Text
only grabs the first 255 characters and leaves the rest behind. I have around 200 workbooks with textboxes in them and I need to get them onto the worksheets themselves.
Is it possible to set a PasswordChar for an InputBox?
Basically, I have an input box asking for an admin password to make changes, and I'd like to make it so onlookers wouldnt be able to see the password. Any suggestions on how to do this?
I could make another userform and have the textbox have a PasswordChar, but I'd rather not.
I'm new to this, and trying to get a spreadsheet organised for my nba fantasy team. To save time, (and me having to enter the positions of 300ish people individually) I'm trying to pull out their positions into seperate colums.
The data I am trying to pull is from these:
Jarrett Jack, Ind PG
Gerald Wallace, Cha SF, PF
=IF(FIND("PG",$B2),"PG","")
That is the formula I am trying to use, it finds the PG for some, but when it doesn't it gives me this "#VALUE!" in the cells, instead of a blank as I have tried to input.
Now, there are 5 combinations that I am trying to pull out: PG, SG, SF, PF and C.
There are multiple combinations that need to be pulled out, and placed into my 3 cells allocated (EG - 1 for Pg, 1 for SG, 1 for SF).
I have also found a flaw that takes the C from Cha (being the team) instead of from the position. Can I search for a individual C? I've tried to google these things, but I can't find an answer.
Cell B4: 02/11/2008
Cell B7: Empty
Cell B8: Empty
Cell B9: Empty
Cell B10: Empty
Cell B11: Empty
Cell B12: Empty
Cell B13: Empty
Cell B14: Empty
B4 is a date format and I need to copy each single character from B4 so that it goes into the corresponding cells from B7 through to B14. So it looks like this:
Cell B4: 02/11/2008
Cell B7: 0
Cell B8: 2
Cell B9: 1
Cell B10: 1
Cell B11: 2
Cell B12: 0
Cell B13: 0
Cell B14: 8
I am not sure how to find the position of a character and put it into another cell, is anyone able to help me with this?
is there a way to automate =left(b1,40) and remove commas from the text?
text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.
how I could split a cell's contents into two cells when it contains more than 1024 chars? The code would need to be clever enough to split the data after the nearest full stop before the 1024 char limit.
View 3 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
I need a slight mod to the below code which works perfectly otherwise. What it does is it divides whatever is in column K by 30 and puts in its relative cell in column M( There a bunch of mini tables below each other with blank rows in between).
The thing is sometimes there are characters in column K like "N/A" for instance and the macro crashes since its only designed to take into account numerical and blank cells in column K.
Is there any way to display one-third 1/3 in a Userform label control?
View 9 Replies View RelatedI have this forumala.
=RIGHT(A1,MIN(500,LEN(A1)))
I would like to apply this formula to VBA and call to existing macro so that this is applied to all the cells in Col. A. How can I do that? Or, is there a better way to do it?
Buttom line, I'd like to see only the last 500 characters per cells in Col. A after I run the macro.
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB:
If tool.Worksheets("TRB Database").Cells(A, "R").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or
tool.Worksheets("TRB Database").Cells(A, "AA").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)
This is where I increase the value.
I am having mucho problems because of Excel's 255 character limit when copying an entire spreadsheet to a new workbook.
In a nutshell, I copy a "data entry" page onto a “report” page within a workbook. The entire “report” page is then copied and moved into a new workbook. That allows me to have a stand alone, exported file outside of my main data entry tool. When you copy an entire page to a new workbook, Excel imposes a 255 character limit. Thus, I see truncated responses on the exported spreadsheet only.
I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.
Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.
The purpose of the attached workbook is to calculate the average number of days a customer takes to pay their invoices. There are two tabs - Invoices and Payments. I am trying to calculate the number of days between Invoices "Sales Date" and Payments "Settlement Date".
The Payments tab is raw data exported from Attache financial system as CSV. It is the best I can get out of the system but the columns are all over the place.
I thought maybe from the Invoices tab I could search a row on the Payments tab to match the invoice number, find the cell containing the phrase "Invoice Number" then pick up the date from the following cell which contains the Settlement Date . This would be straight forward if the data from the CSV file consistently landed in the same column in the Payments worksheet, but it doesn't. As you can see, the phrase "Invoice Number" might appear in any one of several different columns.
I work in school which for example offers 20 courses for every student. Therefore I have a spreadsheet which has students name in A and B then the 20 courses spread from C to V.
Each student will study 3, 4 or 5 of the courses and will have value in the corresponding column (their teaching group) the rest of their row will be blank.
For each student create in columns X, Y , Z, AA and AB I would like to be able to pull from the 20 courses those which the student studies. Therefore finishing up with C to V hidden and just the student names and the 3, 4 or 5 courses they study.
Any ideas? if one method is to get them into one cell how do I get them into the 5 columns; i know DATA : Text to Columns but won't have anything to Delimit it with and can't use Fixed width as students will study subjects with different length charactors.
I have a table, which has pairs, e.g like this:
A=1
B=2
etc
how to give the value to variable based on this pairs? I mean something like this:
If left(mystring;1)=A, then
myvar=1
I used the case-structure, but maybe there is a better solution, which can use an array in order not to write all of the items whenever I need it.
I've created a useform for recording stock trades. So it asks the user to enter values for
"Date of trade"
"Time of trade"
"Name of stock"
"Price"
Right now I'm using a calendar control I downloaded from
http://www.fontstuff.com/vba/vbatut07.htm and it looks good, but I'd also like to have an easy way for the user to select the time of the trade (in minutes).
Is there an easy way to have a time picker? Like a dropdown box maybe? I'd like to have date and time picked separately since the date will remain the same for a lot of trades but the time will always change.
I've googled it a lot and some people mentioned using the datetimepicker Access add-in but I'm not sure if that's right for me. Also, I'm using Excel 2007 but the form will also be used on excel 2003 machines, will that affect the choice of solution?
I have a CSV file which contains a list of records about offices. This CSV file is generated from another program.
I read the CSV file with VBA, a SELECT * SQL statement which puts the elements into an Office Object and adds the offices to a collection.
For some reason, when one of the Telephone numbers is read it is being identified as a Null value. If I manually open the CSV file and change it to a different number e.g. 12345 and try re-read the CSV file with SQL VBA the number gets read fine. Every other element in the CSV file reads fine except for this. The number itself is 7736786460.
Attached is the small snippets of code to read the CSV file which is fairly straightforward.
Code:
Sub LoadUnits()
Dim oRs As Recordset
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNECTION")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
[code]...
Copied the below formula 1 column to the left - to pick up numbers 1 column to the left:
=IF(SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7)=0,"",SUMPRODUCT(--(MOD(COLUMN(E7:AX7),3)=2),E7:AX7))
But the below resulting formula still picks up the same columns as the formula above:
=IF(SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7)=0,"",SUMPRODUCT(--(MOD(COLUMN(D7:AW7),3)=2),D7:AW7))
I'm trying to use SUMPRODUCT as a sort of SUMIF/VLOOKUP hybrid. The following formula:
Code:
=ROUND(SUMPRODUCT('[12 GB BUDGET 15 V1 LOCAL VERSION.xlsx]
Revenue'!$G$3:$FM$3=$K2)*('[12 GB BUDGET 15 V1
LOCAL VERSION.xlsx]Revenue'!$G$27:$FM$27);0)
... should check for the value in cell K2 of the current sheet, compare it to all the values in row 3 of the Revenue sheet, and add up any and all values for those columns in row 27 where there's a match in row 3.
I don't get an error, but it's not adding anything up, either.
I have a excel file with names of employees and projects handled by them. In the 1st column is the name of employee and in the second column, its the name of the project. Every employee has approx 10-15 projects made by them.
I have another file with name of all employees in the 1st column. What is want is to insert the name of any two projects (taken randomly from 1st file) ahead of each employee.
I have Workbook with numerous worksheets,on one particular worksheet i require a formula/macro code that will pick up (say) those cells highlighted in red and then transfer that data set and the other selected information on that row to a another worksheet within the workbook.
E.g
Sheet 1 contains columns A to J;
Sheet 2 is where i require the information which is highlighted in red to be uplifted and pasted too.
so if Cell B2 contains the highlighted cell how would i uplift and transfer this along with data in cell C2,H2,I2 and J2 automatically to sheet 2.
I have a question that has been bugging me for a long time and an answer to it can be a big timesaver for alot of excel users specially accountants that do account reconciliations..
lets assume that I have 3000 numbers(amounts) listed on an Excel spreadsheet vertically, and i know for a fact that the sum of most of these 3000 transactions equal a certian amount..lets say $1,200,320.23,
lets also assume that all these amounts on the list are unique..
now..I want to isolate these numbers(amounts) that add up to that $1,200,320.23 by way of highlighting them or any other method. once that is done, I would be left with the amounts that are causing the descrepency in that account. I wonder if there is any function, utility or even VBA code in excel or even another software that can help solve that problem..I hope my explaination was suffecient, let me know if you need further clarification.