this must be very simple but I dont see it.
= IF(A2 = 50;F2;A2)
this gives me circular reference. All I want is if the value is = 50 to change the cell value, otherwise to leave the old value intact, as it is.
If I write something like this:
= IF(A2 = 50;F2)
the cell is assigned FALSE, and the old value is overwritten.
How can I avoid setting to the FALSE value, without having the ciruclar reference error ?
How to leave a cell truly empty if the criteria of my IF statements is untrue. Currently, I'll write something like:
[Code] ....
But for some reason, when I copy and paste the resulting range of values elsewhere (to rid myself of the formula that determined them), the cells that did not return a value (where the statement is FALSE), are not recognized by a "Go To Special > Blanks" request, until I select all of the "empty cells" and clear them manually. Yet when I try to do a search on the same range for an empty space, I get no hits.
I need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
View 7 Replies View RelatedI've been looking for a way to get the needed result without using macro but i think it can't happen. I'm using excel for forecasting
I need a formula or script to Vlookup a value for me from another sheet if depending on the date.
If the Today date is > then the Date mentioned in Cell A1+15 days i want A2 till A167 to run a formula that would lookup a certain value in another sheet and replace the current value in A2 till A167
For example
A1= August
A2 = 20
A3 = 40 ......
I want when im in 15 August a formula that will replace the foretasted quantity 20 with the real quantity found in sheet 2. If I am not 1 August i don't want the formula to run.
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View RelatedI need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
Due to work, I need to creat the captioned file for our company.
View 2 Replies View RelatedI have a Piece of code that works when a cell changes its value. I want to try and get it to work when only cells in coloumn B change. I thought the best way would be with using an IF statement but i am unsure on the code to write and get it to work. This is the code that i have at the moment.
View 4 Replies View RelatedI want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
I have created the If statement below, in a nutshell its comparing current month performance to last month, stating if it has increased or decreased by last month and how much percentage by
example output would be 'This has decreased by x%"
my problem is when it states the performance has increased , the x% is coming as a negative number (-x%)
what can I do to change this to positive?
Below is the formula
=IF(INDEX('Red Activity & Performance'!$E$45:$J$45,MATCH(PERFORMANCE!$AK$31,'Red Activity & Performance'!$E$44:$J$44,0))
I have 2 columns of figures. One column has a defined name of 'Estimated Value' and the other column 'Actual Value'
My IF statement should simply read "=If(Estimate>Actual,Text color RED, Text Color Black)
My attempts at finding the code for the True/False statements are legion and 'Conditional Formatting' does not appear to work
how can i change font color depended on (if statement)?
View 1 Replies View RelatedI tried Conditional format and no luck. (Office 2010)
What I'm trying to do VB is:
If Cell B:B has value of "402" or "up"
and Cell C:C is less >100
Then Change color on Cell C:C to "yello, red, etc" (if C value is <100 no color change)
I have a statement in a cell in which there is a date field.
Is it possible to apply Vlookup on the statement and change the Date value keeping the statement same ???????
I'm trying to write an If statement that only changes the cell if it's true.
If its false it leaves what is in the cell alone.
This is what I have so far:
HTML Code:
=if(a5>=today(),"payment",----if not, don't change what is in the cell-----)
I need to create a formula which will change the contents of the cell to one of 3 options based on the value of another cell
For example if I was using B2 as the cell to reference I need to say IF B2 = 0 then say "Closed", IF B2 = 1 then say "Open" and if its any other value then say "Error".
I'm having some trouble having all 3 if statements nested correctly.
I don't want to do this as a macro just as a formula.
i am trying to automate altering a DAT file. Basically, each line of data in the DAT file contains an entity name, an account name, and a value. for some of the accounts, i need to change the entity name. for all other accounts, i can leave the entity name alone. if i copy the DAT file into Excel, and use text to columns, how can i create a macro to compare the accounts to a range of cells containing a list of the accounts i need to alter the entity name for, and then change the entity name if there is a match?
the account does not need to match perfectly, the accounts i input only needs to contain the reference account. the list of accounts and the entity name i need to change to are in cells on another sheet. i thought about doing a loop to run through each line of data, but i am not sure how to do that.
I am using MS Excel 2010 want what to change a number of cells if a value is true e.g.
Row one has the headings Prz, Stat, Val, andPts
Row two has the following values :-
(Prz) a2 =2, (Stat) b2=1, (Val) c2=$30, (Pts) d2=10
How do I do the following in Excel :-
IF B2=1 THEN A2+1 AND C2+30 AND D2+5
so that the above cells are changed to
(Prz) a2 =3, (Val) c2=$30, (Pts) d2=10
Perhaps a bit cryptic but here is where I'm looking for.
Cell A1 gets the value YES (or NO) assignment by the user.
Cell B1 has some calculated value let's say 4 based on the formula If(A1="YES";2+2)
Now If the user assigns "NO" to A1 the value of Cell B1 becomes FALSE (return value of second part of the If statement)
I do not want the value of B1 changed if A1=NO and to stay the same (4)
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
Is there a way to do a left formula where you can have it pull the whole cell except the last character? Basically, I have a sentence that looks like this:
apples, oranges, pears,
And I want to drop that last comma. But the length of the each cell varies from row to row.
the below code adds a new row with data to the last blank row on the sheet how do I change it so that it leave the last row and inserts above it.
the last row is a sum of the columns ....
I have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
I Use the following Macro to import data from a .txt(As attached) into excel. This results in the also attached Excel sheet. I am almost happy with it, but I don't want the Return_number4501856 Totals row in there. As a matter of fact I don't want any break between the different return numbers. Does anyone know how to add this to the Macro?
View 2 Replies View Relatedis it possible to have some sort of form that stays on top and is showing a total price as the user is working? It would have to move down the screen as the user moves down also. I dont have the workbook started, just needed to know if its possible and if so, of course, how to do it!
View 2 Replies View RelatedI'm looking to pull into Column B the last name only from the "full name" column (column C).
Right now, in Column B I have the formula =UPPER(MID(C3,FIND(" ",C3)+1,50)) to pull in the last name from the full name that is in column C. The problem is that when column C has a middle initial or a nickname, column B ends up having the middle initial and the last name. For example, if Column C has John "Buster" Jones, I get "Buster" Jones in column B but I only want Jones; or if Column C has Mary T. Smith, I get T. Smith in Column B but I only want SMITH
Is there a better approach so I only get the last name?
I have an Excel file with over 5000 rows all containing URLs
I only need the URLs containing a particular word in ther such as
if the URL contains ".edu" leave it, and if not, delete the data.
Is there a way to do this by VBA?
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am writing a very simple Do While loop where VBA searches for the first nonempty cell in a column starting from cell C3. The error I get is "Overflow". I suspect that when all cells are empty it goes down the column till the last cell, which is around 65,000. How can I put a restriction on this loop? I need the search to be restricted to range C3:C95, because my data ends in row 95. In other words, the code essentially should do this: go from cell C3 to cell C95 and search for the first nonempy cell in this range. When you find the first nonempty cell, assign name "x" to this first nonempty cell.
View 6 Replies View Related