I'm trying to use a formula for column F, starting in cell F6, for column M, starting from M26. The formula works in Column F but after I tried to use it in column M with adjustments, it doesnt work. Attached is the file.
View 3 Repliesthe autosum is not working on one cell which has a formula. All the other cells with formulas are summing correctly. The forumla i used is: =IF(C21="No","-1","") If i enter "No" in C21 the value -1 is return.
However the autosum does not seem to pick up this -1 and only returns 0.
All the answers I can find for this address either the auto-calculation setting or formatting the cell as a number. I am using this same formula in a bunch of cells to calculate totals: =SUM(E12:E17). It works find all over the sheet, except in one box. There is no difference in formatting or data type or anything.
Not working: notworkingexcel.png
Working: workexcel.png
The numbers being summed from the cells above the one in question are referenced from another sheet if that is relevant.
I am trying to write formula in a cell using vba code, but i m not getting the expected one.
[Code]......
In the above code, i m trying to insert the formula where it fetches the address of a cell using some variables.
Here r = 1, c = 1 and resOffSet = 7
From the above code im expecting cell adress like A1 , B1 etc but i am seeing "0" in the cell value.
Formula to cell with macro not working
I'm trying to enter this formula into cell in VBA macro.
I am using the worksheet change routine for conditional formating of more than three items.
The problem is that it only changes if I manually type within the page and does not update if the cells are updated via formulas.
To explain a little more, I have three sheets that I combine into one to help find the best rate from three companies using the following formula:
=IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" All",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=UG!C4),FIXED(PMI!C4,2)&" PMI/UG",IF(AND(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(PMI!C4,2)&" PMI/GE",IF(AND(MIN(PMI!C4,UG!C4,UG!C4)=GE!C4,MIN(PMI!C4,UG!C4,GE!C4)=GE!C4),FIXED(UG!C4,2)&" GE/UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=PMI!C4,FIXED(PMI!C4,2)&" PMI",IF(MIN(PMI!C4,UG!C4,GE!C4)=UG!C4,FIXED(UG!C4,2)&" UG",IF(MIN(PMI!C4,UG!C4,GE!C4)=GE!C4,FIXED(GE!C4,2)&" GE","ERROR")))))))
Please feel free to condense this if you like.
So when I update a reference on one of the first three sheets, the cells update on this sheet but the cell colors never change. This is the code for the worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel1 As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
I am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies View RelatedI have a problem with the code below. The code pastes values into column c, depending on values in Column A.
The problem is that, at the end I want it to go to column d and execute the R1C1 formula, but the lastrow1 variable is always set to 1 ( I went through the code pressing F8) so the formula won't go down the column.
Code:
If Range("A" & i).Value = 2 Then
Worksheets("deacc").Activate
Call SelectRndRow
Worksheets("sheet1").Activate
Range("c1").Select
Selection.End(xlDown).Select
[code]....
why my datedif function is not working.
View 3 Replies View RelatedI currently have a workbook (Unconfirmed Application Audit 02) which is the result of an audit of applications on PCs. I have come up with a macro which will insert new columns into the sheet and fill those columns with data, should the application listed, have a replacement specified in the AppRM file. The macro works fine for column G, in that it pulls through the new software manufacturer name but column H should pull through the new product name and I should display the new product version and they don't. To me, it looks like the exact same formula in each column apart from the column reference to get the information from.
View 7 Replies View RelatedI have a Age calculating formula in an excell cell, which calculates the age as on another date.
The forumala is =IF(E4="","",IF($C$1="","",ROUNDDOWN(($C$1-E4)/365,0))) where E4 is the date of birth and C1 is the date in which I want the age calculated to be.
The above formula works very well on my computer, but does not on any other computer. If I access it from my sent items or from a shared drive, it works perfectly on mine, but not on any other computer!!! I get a #value error message on all other computers.
I have tried the foll:
If i enter =ISNUMBER(C1) where the age has to be calculated, I get #Name?
If I enter =ISNUMBER(E4), i get TRUE
The above is on the sheet where it does not work ( on others computer ) and i get TRUE for both on mine. Also both have the same date formats and same time zones...
where the issue with the following formula is?
=IF(AND($E$2="N",$AA$550),"BT1",IF(AND(Z5="F",Y5>=0),"CLEAR",IF($AA$5
Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day
Is there any formula, or mix of formula’s I can use to automatically calculate this?
I am trying to use a countif and formula but can't get it to work. This part of the formula works fine =COUNTIF(J:J,"
View 9 Replies View RelatedI am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.
This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.
A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....
I have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me.
View 9 Replies View RelatedI am trying to create a formula that if the date column falls between 01/01/2014-01/25/2014, it returns January as the value.
View 10 Replies View Relatedsee the attached spreadsheet, I am getting an error message with the attached formula
View 2 Replies View RelatedI am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this
[Code] .....
Full formula is below:
[Code] .....
I am not sure where the error is in the formula. Also I would like to add one more condition to it.
Another problem- I created Drop Down using Data Validation. Now I need to make the Drop DOWN ICon visible. It becomes visible only when I click on the cell.
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.
Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.
View 3 Replies View RelatedI have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
View 4 Replies View RelatedI have 2 lists. One column has a about 200 values of 7 digit numbers. The second column has about 500 cells with text. This text should include one of the 4 digit numbers from the first list. If it doesn't, I want to purge it.
The 7 digit numbers always appear in the same spot in the column 2 figures. So say the number I'm looking for is 1122 and the second list has a cell with L2871122BD. Perhaps there is an easier way to do this, but my plan was to.
create a 3rd column that has formula =mid(B1,5,4) which should isolate the 4 digit value i want to compare to. Then create a 4th column that has the formula =ISNUMBER(MATCH("*"&C1&"*",A:A,0)) to check the newly created MID number to the numbers in column one. Afterward I would simply arange from small to large and delete all of the false entries in column 2.
The only problem is, my ISNUMBER function doesn't work when referencing from my MID formula to column 1. If I do the match and compare the smaller number in column 1 to the larger in column 2, it does work. Is there an easier way to delete or highlight entries in Column 2 whose middle 4 digits don't show up in column 1? Here is an example of what I'm working with.
I bought a new computer with Windows 7. A file that is fine on my old unit does not work on the new one.
Column "H" should provide a list of birthdays comming up in the next two months, but something is not working.
I have a problem with an array formula. The file is attached for better comprehension.
I've built a multi-criteria lookup based on INDEX and MATCH.
The idea is in Sheet 1, in column F to put 1 (one) if each record of columns A,B,C and D (all combined) match any record in Sheet DATA. If not then 0 (zero).
The array formula works nice for each of the records. However, I get a ZERO in Zinc! If you check DATA, you can see that Zinc is there, from line 221 to 224!
I can't understand why this happens since i've pasted the formula for each of the lines in column F.
The code below pastes an array formula on the given range. However, it results FALSE and it does not paste as an Array formula. How can I fix it?
Sub copy_Time_Spent_by_Ticket_Formula()
Dim SheetName As Variant
For Each SheetName In Array("Calculations")
With Worksheets(SheetName)
[code].....
this formula was working great since 12-08, but someone came to me yesterday saying there is a flaw in the formula between Company A and Company B
If you note that 10.125.239.0/24 is overlapped with 10.125.236.0/22, yet is marked FALSE ...