I have 4 cols, x rows. I need Column C to check Column B for a numerical value, and if true, return the value in B, and if not true, then return value from A. Column B are functions formatted as general. The reference made by Sheet2!B is numbers formatted as text as to retain leading zeros. Here is what I have so far
=IF(ISBLANK($B10),$A10,$B10)
I've been trying to use excel's Isnumber or Istext function to evaluate a range of cells, but have been unsuccesful so far. Is it even possible? or is the formula just restricted to evaluating only single cells?
For example, if I do, Isnumber(A1:A10), is there any way to make that work?
I am trying to create code that deletes lines in a spreadsheet when the first column is either blank or has text in it. However, when writing the VBA code, ISNUMBER and ISTEXT do not work. Amazingly, ISBLANK does work.
View 4 Replies View RelatedTrying to design a check that makes a user insert an integer between 0 and 90. So far I can use this:
View 3 Replies View RelatedI am making some conditional formats for a document. One that I am trying to achieve is have a group of cells have a background color, but once the data has been added, the color go away.
This is what my conditional format looks like: =ISBLANK($A$1:$H$22) and chose the Green fill background. So, my understanding is, the cells should have a green background if they are blank, but go back to a standard format, once cells have data put in them.
I'd like to search a cell A1 containing for example 'hello_L765'. If the 7th character is L and the 8th character is text(ie not a number) than display yes, otherwise display the 7th character.
View 3 Replies View RelatedI'm trying to validate a cell in Excel 2007 that should contain only two letters.
Formula:
Code:
=IF(AND(LEN(A1)=2,ISTEXT(A1)),TRUE,FALSE)
When the value of A1 is JK, the result is TRUE
When the value of A1 is 12, the result is FALSE
When the value of A1 is 3K, the result is TRUE
When the value of A1 is K3, the result is TRUE
When the value of A1 is 123, the result is FALSE
When the value of A1 is JKL, the result is FALSE
When the value of A1 is 3, the result is FALSE
When the value of A1 is K, the result is FALSE
The LEN function works as expected, but the ISTEXT function does not, whenever the cell contains a number and a letter. I've tested the LEN and ISTEXT functions separately, and get the same results.
Is it me, or is it Excel?
I have a workbook with worksheets named by peoples names. There are cells in my summary sheet that contain all the names of the worksheets (or peoples names).
Each worksheet has a table in it that I am trying to search one column to find a specific name and then report if there is text in another column in the same row. If I use the following formula:
=IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!f10"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!h10"))))=TRUE,1,0)
It works and I'll return a "1" for cells in column F that have text and a "0" for columns that don't.
However, I am trying to sum up the cells in F that have text and when I enter:
{=SUM(IF(ISTEXT(INDEX(INDIRECT("'" & E$4 & "'!F10:F200"),MATCH(INDIRECT("A11"),INDIRECT("'" & E$4 & "'!H10:H200")))),1,0))}
it returns "1" when that should not be the answer. Am I doing something with the array in my INDIRECT formula? I used this similar application for another purpose in this workbook and refereced the array through INDIRECT the same way (but didn't use MATCH or INDEX) and it worked fine. This was the formula:
{=IF(SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0))>0,SUM(IF(INDIRECT("'" & C$4 & "'!H1:H200")=INDIRECT("A5"),1,0)),"-")}
I am summing 3 cells, A3, A4 & A5. Cells A4 and A5 may contain text. I want to write a formula using the IF(ISTEXT funciton which says something like this:
Sum(A3:A5), if cell A4 and A5 contain text then sum just A3. If only cell A5 contains text then sum just A3:A4.
Im not sure exaclty how to construct this.
I have this formula in a cell: =IF(ISTEXT(F5),F4,""). The problem is F5 is visually blank and has this formula =B3. so it returns F4. Is there a way to rewrite the formula or to use another formula, so it will return blank?
View 2 Replies View RelatedI'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?
I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.
I have a rota sheet whereby shifts are written in as start time, finish time, and then a calculation for the hours.
At the bottom I have another SUM formula to calculate total daily hours.
However, within the total personal hours I have a VLOOKUP formula to check if the start time cell has a holiday text reference.
Please see below:
Take the "Sergei" example: he starts at 9am, finishes at 8pm, and the formula automatically calculates an 11 hour working day.
However, in the "Tom" example, the "TH" refers to another sheet which houses a reference of the amount of hours to place in the total hours box if those letters appear, thus imputing holiday hours at the same time.
"=IF(ISTEXT(F24),VLOOKUP(F24,'Hol-Meet Hours'!$F$7:$G$27,2,0),(G24-F24)*24-IF((G24-F24)*24>6,0,0))"
What I am struggling with is to use an IF and ISTEXT formula in the "Daily Hours" formula to ignore any holiday inputs, thus only giving me trading hours.
The only way I could think of doing it was something along these lines:
"IF(ISTEXT(F16),SUM(F17:F31),SUM(F16:F31)...et al"
The issue here is that I need to repeat the formula for every staff member within the same cell's formula, which I don't know how to do (multiple functions within a formula repeated 20 times), and is a right drag.
I'm trying to apply the istext to a cell that has multiple values eg: [car, boat, truck] so I have setup the cell with the formula
=istext(c147 = "car",1,0)
it returns false constantly. Is that becuase of the other values separated by comma's in that same cell?
Is there a way to do this so that I don't have to go through 300+ cells that have multiple values to separate them?
I have test file so i want to program when i click on button, it will check every 10s on Heet2.cell(i,4) until that cell has value (take by hand) then check next cell (Cell(i+1),4). I used timer code (pls view code on test.xls file) but it not work properly.
[URL]
How can I check if a cell has data validation with a VBA procedure?
I want to do this because I am using a procedure to add data validation and I get an error if the cell already contains data validation.
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I have a tab with different city names and then a a series of answers to a number of different questions, what I need to do is create a macro which looks a table on a different tab, and if column b matches any of the words in the table to then print that entire row of answers.
So if the information found in R1!M3:M21 is found in Results!B:B then print that row to sheet R1. If the information found in R2!M3:M22 is found in Results!B:B then print to sheet R2 etc.
This is the only other way I could describe it, however I don't think it can be done as a formula anyway (even though it is somewhat gibberish)
IF(ISNUMBER(SEARCH(R1!M3:M21,Results!B:B))print the row,move onto next row)
The following code displays the message box and loads a userform if the Active cell offset 1, -1 when within G column equals "LAST ROW".
[Code] .....
How can I change it so instead it performs those same actions (displaying the message box, and loads the userform) if the number of rows between the last data entry in G and the last data entry in F is equal to 0?
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'm attempting to use VB in excel. I've created a form and several check boxes with specific options for each one.
when one box is clicked I want the data from that box (whatever the option is) - to appear in a specific cell (ie: cell 17)
I have a database of names and titles (acutally: #, last name, full name, title, meeting) and at this point I have a column using the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A5:A355")) Is Nothing Then
Range("A5:A355").Cells.Clear
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
everything works like I want, except when I use the auto filter, the "a" won't be cleared from cells that are hidden. I need to make sure only one "a" is populated in this column as that's what I'm using with the vlookup function to key the data for my Character Generator.
Maybe I'm not using the best code, I'm new to this and just cobbled together code from a few different places.
Some quick background to what I'm doing, but you may not need it to help. I'm trying to use Excel to populate some cells that will tell our Chyron Character Generator (for those that don't know what that is, it's the device that puts graphics on screen during news programs, etc...) what to put up for lower third titles (and more) for our meetings we cover.
I'm trying to figure out how to conditionally format one cell depending on two separate cells being not blank.
so for one cell it would be:
IF(NOT(ISBLANK($C$5)))
I think I need to add an OR in there somewhere to make it also depend on C6 but I just can't figure it out...
Im using ISNUMBER in a spreadsheet to automatically add a ref number to an entry in the spreadsheet, this is based on the value of a set column. To give you an idea the values are bus services, i.g. 6, 123, 56 etc, when the column has a bus service number entered it auto produces a ref number.
Now the problem is now some service numbers have letters in e.g. X14 X12 36A etc, now these are not recognised and therefore no ref number is given, is there a way of changing my formula to recognise this.
The formula at the moment is as follows
In the ref column (which is locked) =IF(V4=1,COUNTIF($V$3:V4,V4),"")
In column V (which is locked and hiden) =IF(ISNUMBER(D4),1,"""")
The following formula:
=IF(ISNUMBER(F35)="TRUE","",IF(ISNUMBER(E35)="TRUE","X",IF(ISNUMBER(D35)="TRUE","XX","XXX")))
is returning XXX when copied down, where I expected it to return XXX as well as XX, X and " ".
Has anyone got a clue what I'm missing (might be a friday afternoon thing...).
I am faced with the daunting assignment of adjusting a payroll report using infrequently exercised logical expressions. I've been careful to try and build my formula adjustments in intermediate steps, so I could test the results, but I can't seem to get past a dual test. I need to create a commission calculation if there are no values in the two columns immediately left of my active cell,col N79, i.e., if L79 OR M79 contain a value, then do nothing, otherwise, multiply contents of G79 * the commission rate contained in named cell "Split3".
This formula works on a single column test:
=IF(ISNUMBER(M79)," ",G79*Split3)
But I can't seem to construct a formula to test both column cells. I have tried this unsuccessfully:
=IF(OR(ISNUMBER(L79),ISNUMBER(M79)," "),G79*Split3)
The above formula is accepted, but I get: #VALUE, with the explanation:
"A value used in the formula is the wrong data type"
I have a client/sales sheet where I keep outstanding and paid bills information.
I want to get an indicator cell that shows if a bill was paid Y, or Not paid.
Since cell. However, if there is no invoice I want it to show,... nothing.
I used the invoice date cell C4 to see if there is an actual invoice. I thought the following formula would do it. However, if the invoice is paid and the outstanding amount W4 is 0.00 it returns a N, as Not paid????
C4 (date of invoice)
W4 (is amount due - payment =U4-V4)
(this is the formula I thought of)
=IF(ISBLANK(C4),"",IF(W4>0,"N", IF(W4=0,"Y")))
Is the following valid code in Excel VBA?
If Application.WorksheetFunction.isblank( Cells(RowIndex, ColIndex)) Then
I need to create a formula (Sumproduct) that will look at two criteria then total a third column IF the value in the cell of the third column is a number.
The sumproduct will look at one range A1:A100 and see if it matches initials in cell D1, and it will look at the year in column B1:B100 and see if it matches the year in D2. Then it should total the amount in column C1:C100. But the column C to be totaled includes both dollar values and “N/A”. So If I use something like:
=SUMPRODUCT((A1:A100=D1)*(B1:B100=D2)*(C1:C27))
I get the #NAME? I believe because some of the values are N/A instead of numbers. So can I say something like:
=SUMPRODUCT((A1:A100=D1)*(B1:B100=HD2)*(ISNUMBER(C1:C100)*(C1:C2100)))
(I know this probably isn’t even close, but I hope you can see what I want to do)
I know I can create a separate column to show only the dollar values from column C and use the sumproduct to total that column, but is there a why to skip that step?
Can someone tell me how to have 2 options using isnumber.
I'm using ISNUMBER(SEARCH("SUBMITTED",N156)) but I'd like to have "SUPPLY" & "SUBMITTED"
Here is some of my code. It does not seem to work though. can you fix it?
Dim Madrid As Integer
Dim Paris As Integer
For n = 7 to 17
Madrid = Application.WorksheetFunction.IsNumber(Sheets(3).Value("P" & n + 1).Value)
Paris = Application.WorksheetFunction.VLookup(Sheets("Sales").Value("$E:$I"), 3, False)