I've got a list of numbers between 0 and 1
eg.
0.25
0.59
0.73
0.15
0.99
in column A
if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?
I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.
View 7 Replies View RelatedI currently have data in the form of: "510 E. 96th Street, Suite 400 Indianapolis, IN 46240"
And need a formula to extract only the state abbreviation from the cell, any ideas? I currently have the formula, =MID(O333,(SEARCH(",",O333)+2),2) but it only works if there is one comma for example, it works perfectly on this data:
2K MEDIcal 105 West Torrance Boulevard Suite 106 Redondo Beach, CA 90277
I want to create a macro that checks whether a specified range of columns is hidden, and
a) hide them if they are not hidden, or
b) unhide them if they are hidden,
i.e. toggle the Hide state.
What method can I use to query the hidden state of a selected range of columns and return a boolean value?
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I am manually scrolling through Excel right now to find all blank cells in column L. Some cells have dates, others are blank. IF L is BLANK, I go to row K and copy/paste row K to row L. IF K is BLANK, I got to row I and copy/paste to row L.
Is there a statement I can put into row L to basically say "If blank copy/paste from row K. If row L and K are blank go to row I and copy/paste to L." ???
I have a table of some numbers, and along the left side is a column representing what each row of data is. So in this case, it is a list of letters. What I'm doing is in each column of the data, I find the maximum number, and then I want to have it list below that what letter that maximum number is from. Here is an example:
View 2 Replies View RelatedThe data:
There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.
The formula needed:
I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.
My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.
The furthest I got was this, but it didn't work.
=AVERAGE(IF('A3:A33>=VALUE(D3)
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.
View 3 Replies View RelatedI need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.
View 3 Replies View RelatedI have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.
Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith
So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)
For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc
Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.
Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.
My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".
If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".
to automate a process that split name, address, city, state, and zip.
here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....
1 - If any cells (with data in it) in the "state" column contain any of the following abbreviations in it...
AB, BC, MB, PE, NB, NL, NS, NT, NU, ON, QC, SK, YT
Then all matching row cells in the column named "country" should say "Canada",
AND....(it must also meet this criteria)...
Criteria 2 - If any text in "Country" column is "Kryponite", AND "State" column is blank
If BOTH criteria 1&2 are met.....THEN...
then delete both the country & state columns
If not, keep both columns and highlight in light red any errors to this macro.
Are Boolean variables ALWAYS initialised as FALSE when they are created?
I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:
I believe many people face is to automate a process that split name, address, city, state, and zip.
here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....
I am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
Upon initial display,if my toggle control value is True, I would like it to appear "selected", or down. If the initial value is False, I would like it to appear "deselected" or up. Even though I can successfully detect the state and set the color and caption, the control always appears up, as though it is not selected.
Private Sub UserForm_Initialize()
If AppInfo_Form.debugToggle = True Then
AppInfo_Form.debugToggle.Caption = " Debugging Is ON"
AppInfo_Form.debugToggle.BackColor = &HFF00&
Else
AppInfo_Form.debugToggle.Caption = "Debugging Is OFF"
AppInfo_Form.debugToggle.BackColor = &H8000000F
End If
End Sub
I would be loading data into a spreadsheet, and when there is a phone number I'd like to have it populate the state field. (ex. (212) 123-1234 in the phone field, would populate "NY" in the state field, is this possible? If so is there any brief way to describe how to do so without taking too much of your time?
format a cell depending on the state of a check(tick) box
View 3 Replies View RelatedI have a column that has data like this in it:
cityname, stateAbbreviation, XXXXX
or
sacramento, CA 95814
The tricky part is that sometimes there's a comma delimiting these fields and sometimes there is just a space between these fields.
So, I suppose the script would have to assume a length of 2 chars for the state and when it finds that it knows that what was before that string of 2 chars was the city.
Also, sometimes there is a longer zip with 10 digits code like this:
cityname stateAbbreviation XXXXX-XXXXX
sacramento CA 95814-82202
How might I write some VB code to loop through this column and break up this cell into 3 other cells?
I'm playing with an excel file that has 3 different spreadsheets and basically the same layout just different product lines on each sheet. I need to find a snipet of VB code to change cell color depending on if another cell is used(filled in) or if it is blank. I'm very limited to my VB skills but can tinker enough to get what I need if someone points me in the right direction.
here is an example of what I'm trying to accomplish using the following Columns:
ItemNo[A], QUOTEREQUESTDATE[b], SUBMITTOSALESDATE[C] APPROVALRECEIVEDDATE[D], RELEASETOMFGDATE[E]
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO RED CELL:
[b] USED, [C] BLANK, [D] BLANK, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO ORANGE CELL:
[b] USED, [C] USED, [D] BLANK, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO YELLOW CELL:
[b] USED, [C] USED, [D] USED, [E] BLANK
EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO GREEN CELL:
[b] USED, [C] USED, [D] USED, [E] USED
I would like to be able to have Excel VBA pull down the data from the following website.
The first URL will take you to the website, but you then need to select a state shown in bold from the Radio side of the index window in the middle of the screen. Once at the next screen, this is the table I wish to download into excel.
I want to do this for all the states listed. How can I do this or is it possible since you have to select a state prior to the listing.
HTML Code: [URL] ..... I found this code from anothe thread but it did not work.
Code:
Sub GetData()
Dim x As Long, y As Long, t As Long
Dim htm As Object
[Code] ....
I wrote the following...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Obj1")
If Not Intersect(Target, VRange) Is Nothing Then
Range("ObjDescr1").Select
ActiveCell.FormulaR1C1 = "=IF(Obj1="""","""",(IF(EmplType=""Salary"",VLOOKUP(Obj1,SalaryDevGoalsTbl,2,FALSE),VLOOKUP(Obj1,HourlyDevGoalsTbl,2,FALSE))))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub
What it does, everytime range named Obj1 is selected, range named ObjDescr1 is populated to a Vlookup, depending on which type of employee.
I need the Worksheet Change procedure to handle Obj2 - 5. I want to Change Procedure to find out which named range Obj(n) is effected, and to do the rest on that one. ObjDescr(n) is also to match the value of (n).
How can I do this the most efficient way? I want to cut down on code in ever area possible, so I dont want to just copy and paste for obj1,2,3,4 and 5.
I want to write code for a Checkbox that when checked it fills a cell with a color, but if it is subsequently unchecked, the cell color disappears.
View 9 Replies View RelatedI have a Worksheet_Change event like the one below. When Checkbox1 is ticked the it subtracts and when it's not, it adds:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intValue As Integer
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
intValue = CInt(Target.Value)
If CheckBox1.Value Then intValue = intValue * -1
Select Case Target
' Apples
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + intValue
Case Is = Range("X12")........................
I have a checkbox and a command button on my sheet. I want that, depending if the checkbox is marked or not, I can call two different macros. I don't know if this is possible at all or maybe can be done easier otheriwise, but this would more or less be my idea of what should happen:
Private Sub CommandButton1_Click ()
' If CheckBox1 = marked
Call MacroA
'Else (CheckBox1 = unmarked)
Call MacroB
' End If
End Sub
I am having a little bit of difficulty find ways to effectively incorporate a checkbox panel into a form interface. What I am trying to do is all the user to select using checkboxes which of 31 separate columns will automatically be deleted or not deleted. The method I have been using is to create 31 checkboxes, and then use the value of the checkbox (true/false) to determine a value in an array, which then can be used to delete the column by a separate sub.
However, I am interested in being able to have the checkboxes start checked or start unchecked by values contained in a different array. My problem is that I am unable to reference the checkbox itself until the form containing the checkbox is opened, but at that point I don't know of a way to cause the desired checkboxes to automatically be selected without placing a command button that must be pressed to fill them in. Essentially I want to be able to open a userform that has checkboxes preselected.