Identifying Caps So It Doesn't Add To Current $
Mar 1, 2014
In the attachment 1st tab column Q I want it to show no more than the Location Cap (Column M) UNLESS the Current Hourly Rate (Column L) is higher than the Cap.
If the Current Hourly Rate (Column L) is lower than the cap but with the increase from the 2nd tab (Loc Info) makes it greater than the Location Cap (Column M) then I want it to show Location Cap. There were only a few lines that did not work right and i can't figure out why. I have attached one of the lines that were not working right.
Ultimately I want to do the same from 2015 (Column R) and 2016 (Column S) but referencing the previous year.
Attached File : Help2.xlsx
View 6 Replies
ADVERTISEMENT
Apr 20, 2009
Does anyone have a way to change columns of text entered in all caps to the initial capital letter and the rest of the text in small letters.
As in: JONES to Jones.
View 6 Replies
View Related
Sep 6, 2005
Add a column next to the first name. The in that column use the formula
=Proper(A1), A1 being the cell that contains the first name. Then copy that
cell down. Do the same for Last Names. Then keep the cell values, and delete
the original set.
Should be an easier way I think (selecting the entire column and using
conditional formatting) but MS hasn't seen fit to do this yet.
--
John C. Harris, MPA
JCZ Consulting Services, LLC
"Robert Judge" <RobertJudge@discussions.microsoft.com> wrote in message
news:56F8D0DC-3D88-441F-9B47-A5CCDFD133A4@microsoft.com...
>I have an EXCEL worksheet with columns including first name and last name,
>in
> all capital letters:
>
> Column A is JOHN
> Column B is SMITH
>
> How can I change all the names so that, for example, JOHN becomes John and
> SMITH becomes Smith?
View 14 Replies
View Related
Oct 24, 2011
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange
[Code] .........
View 6 Replies
View Related
Mar 30, 2009
I like the proper formula and find it handy, however do you know if there is a formula that would only keep the first letter in the cell at caps and reduce the rest to lower case, ie
A1 = MY DOG IS WHITE
proper(A1) = My Dog Is White
what I want is only first letter caps = My dog is white. I'va had a decent look around but can't find anything that would do this?
View 4 Replies
View Related
Nov 21, 2009
Would like to set CAPS-LOCK On and Off using VBA. Can someone help please?
View 5 Replies
View Related
Dec 22, 2008
It appears that the excel IF statement caps out at eight arguments. Is there a work around for this? I need to enter 29 arguments... Does excel have a CASE or Array function?
Example-works:
=IF((H3="Weight1"),"A",IF((H3="Weight2),"B",IF((H3="Weight3"),"C",IF((H3="Weight4"),"D",IF((H3="Weig ht5"),"E",IF((H3="Weight6),"F",IF((H3="Weight7"),"G",IF((H3="Weight8"),"H",""))))))))
Example-doesn't work:
=IF((H3="Weight1"),"A",IF((H3="Weight2),"B",IF((H3="Weight3"),"C",IF((H3="Weight4"),"D",IF((H3="Weig ht5"),"E",IF((H3="Weight6),"F",IF((H3="Weight7"),"G",IF((H3="Weight8"),"H",IF((H3="Weight9"),"I""")) )))))))
View 14 Replies
View Related
Nov 1, 2002
How do you programatically enable the CAPSLOCK key in VBA? I've tried:
Sendkeys "{CAPSLOCK}"
When I run this, Excel's status bar flashes "CAPS" for about 1 second then disappears. It seems to have no impact on the keyboard for case-sensitivity - which is what I'm shooting for.
View 9 Replies
View Related
Feb 27, 2008
I was handed an Excel Spreadsheet containing a list of names. Within the sheet there are three names (Elizabeth, Amy, and Daniel) that contain upper and lower case letters. The desire is that all names are in CAPS. However, these three names refuse to remain as CAPS when overwritten in the cell. As a note, other names can be typed in those cells and will successfully remain as typed, either U/L case or all CAPS. Another note, if the Replace feature is used, the names remain as Replaced.
View 9 Replies
View Related
Oct 10, 2008
I have a column of cells which looks like this:
"JUMPING LAZY brown fox"
I need to put the non capitals in the front as so:
"brown fox JUMPING LAZY"
View 9 Replies
View Related
Oct 6, 2009
I have looked in conditional formatting, but do not see a way to change lower case to all caps. Is there a way to do this so that when someone types in a cell it returns all caps instead of leaving it in lower case?
View 9 Replies
View Related
Apr 11, 2014
I have a column with names, where i need the 1st letters of the Names to be displayed in CAPS. If its a Single letter also, the same should be displayed in CAPS
View 2 Replies
View Related
Mar 21, 2005
Is there any way to format a cell so that text entered will always display in caps?
View 4 Replies
View Related
Jun 9, 2009
I have the following codes to test Caps Lock whether it is on or off. Instead of showing a msgbox when the Caps Lock is on, I there another way to show comment or other object as well as windows UserForms
View 3 Replies
View Related
Jun 20, 2009
I have a userform contains a label control .. what I want is showing that label when Caps Lock is on and hide it when Caps Lock is off.
View 14 Replies
View Related
Sep 12, 2009
Is it possible to have this type of case sensitive sorting (first all words beginning with a capital later and then all cells beginning with a lowercase letter):
A
B
C
D
E
F
G
a
b
c
d
e
f
g
View 4 Replies
View Related
Jul 29, 2009
how I can format individual cells to put text in caps. I have found how to format the whole worksheet, but I only want certain cells to do it.
View 9 Replies
View Related
May 31, 2007
I am trying to get a custom date format that looks like:
30MAY07
I can get 30May07 with ddmmmyy but can't seem to find how to get the 3 letter month to display in caps.
Excel 2002 SP3 on WinXP
View 9 Replies
View Related
Jan 17, 2010
I want to switch on Caps & Number lock on opening an Excel document. I'm using the following code;
View 10 Replies
View Related
Jan 6, 2013
I have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
View 2 Replies
View Related
Aug 8, 2014
I have a few tasks which I have been doing manually because I do not know if there is a way to accomplish these tasks automatically via some excel formulas.
My first task requires me to take an address, for example: 1234 CAMBIE STREET, and format it such that it is properly capitalized (only capital letters in the front of words) like this: 1234 Cambie Street
My second task involves taking an address, for example: 1234 1st Avenue W, and rearranging it such that the direction is in front of the street name like so: 1234 W 1st Avenue. What makes this task potentially even more complicated is that not all the addresses I am working with require the rearrangement, so I can't simply have a formula that puts the last group of characters in front of the first group of characters because that may screw up addresses that don't require this formatting.
My third task demands that 2 columns of names are combined into one column, while also simultaneously placing a "&" between the names. For example: Jonathan Parkinson | Sarah Parkinson turns into Jonathan Parkinson & Sarah Parkinson. Now I do understand how to use a simple combining formula (=a1&" & "&b1) but it isn't that simple. The columns are not all filled with names. Some pairs may have no names, whereas others may have only one name. Because of this, the formula I used as an example will result in many instances where all I see is a "&", and other instances where I see the first name, followed by an unnecessary "&".
So far, for the majority of these problems, I've been using a combination of manual data input, and the replace function, but I really do hope there is a faster and better way to go about these tasks.
View 4 Replies
View Related
Jun 7, 2013
So I have a list of integers in col A that is a truncated list from col B. I want to select the units in col B that do not appear in col A. I tried this statement
=IF(A2=B2,A2,0)
Since both lists (A and B) are ordered from smallest to largest this only worked until a value that is not in ColA appears in ColB and then all the cells following = O.
View 4 Replies
View Related
Jun 29, 2007
I have a column of cells, of which certain values have the prefix "EmployeeID:". I'm trying to identify the cells containing the prefix by evaluating each cell using activecell.offset and value to no avail. I've also tried to use the selection. find, however the first instance of the prefix is continually selected, and does not move on. I'm assuming that I must use the findnext feature, however I'm not having much success.
View 2 Replies
View Related
May 14, 2014
I have some data that has names but is not consistant i.e
Smith, John
Smith,John
I want a formula that says, if there is a space after the comma do nothing, otherwise insert a space between.
I have figured out the second part so my formula will look like
=if(some logic to identify a space after the comma ,"",already figured out this bit)
View 5 Replies
View Related
Aug 1, 2014
I am trying to develop an IF formula based on 2 variables;
1. The value in column Q = 4
2. The value in column AA is between -10% and 10%
When both are true, it should return a value of "YES" (or "NO" when untrue). I am having trouble with the range part. It could be that the range spans negative and positive integers, but I'm not sure. Here is what I've got at the present time:
=IF(AND(OR(Q2=4),OR(AA2<0.10)),"YES",IF(AND(OR(Q2=4),OR(AA2>-0.10)),"YES","NO"))
View 3 Replies
View Related
Apr 17, 2014
I have two tables,
one table (TABLE1) contains the name and a mile point a
the other table (TABLE2) contains name, mile point 1, mile point 2, and other data
I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from
TABLE1
Line
MP A
SYS
100
8
?
TABLE2
SYS
Line
MP1
MP2
DATA
A
100
0
5
BOB
A
100
5
10
KON
B
100
10
15
B
I want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given
I started with something like: =if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...
The problem is this doesn't work because im comparing one to many.
View 3 Replies
View Related
May 5, 2009
I've collected some data from a GPS logger regarding the speed of an athlete. I want to calculate how many sprints this particular athlete undertook during a training session.
Sprinting is defined as a speed of > 20 kph.
One sprint would be the attainment of one peak >20 kph before decreasing below 20 kph.
It's easy to identify the 3 peaks and thus sprints from the xy scatterplot in the attached file, but I'm struggling to find a way to calculate this.
View 11 Replies
View Related
Sep 30, 2009
I'm trying to identify duplicates with a return of "True" or "False". The attached workbook has column F for results (to read PI2 A if in column L, PI2 B if in column R or Both), but I want to query if everyone in column E is in column L or is in Column R and if so which one.
View 3 Replies
View Related
Oct 10, 2009
i have a spreadsheet that contains dates spread across different rows and columns. How can I identifying dates for this week and next week by colouring the cells?
example dates that fall within this week will be red, next week will be blue.
View 7 Replies
View Related
Jun 10, 2013
I want to sort a spreadsheet based on whether any of the numbers '1', '2' or '3' appear in a particular column.
There are 2 particular rows which I care if they have a '1', '2' or '3' in them, let's say row 17 and 18.
I want to create a formula say in row 20 which would tell me if there is a '1', '2' or '3' in the corresponding cells in EITHER (!) row 17 or 18.
An example to clarify:
Cell A17 = 1
Cell A18 = 2
Then Cell A20 would be = 1 because both A17 and A18 have a '1', '2' or '3' .
2nd example:
Cell C17 = 1
Cell C18 = 0
Then Cell C20 would be = 1 because C17 has a '1', '2' or '3' .
3rd example
Cell D17 = 6
Cell D18 = 12
Then Cell D20 would be = "" (Null - no value) because neither D17 nor D18 has a '1', '2' or '3' .
Note: I am looking just for the numbers '1', '2' or '3' not (!) numbers like 11, 12, 13, 21, 22, 23 etc...
View 4 Replies
View Related