How To Make IF Work On Formulas And Not Values
Nov 18, 2013
I've developed a spreadsheet that shows what errors people made in their spreadsheets. I compare cell A1 in spreadsheet 1 (original) against cell A1 in spreadsheet 2 (final), in spreadsheet 3. Spreadsheet 3 shows a "-" if the cell they wrote was OK, and shows the new, correct value if they made an error, as follows:
=IF('Final'!A5='Original'!A5, "-",'Final'!A5)
The problem is, this only works if I take the original spreadsheets, which contain formulae in each cell, and paste them into a new spreadsheet using paste values.
Is it possible for the IF function to search the formula in a cell, and not the value?
View 1 Replies
ADVERTISEMENT
Feb 13, 2010
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies
View Related
Jul 1, 2014
I've got a PDF file that I was hoping to convert into an Excel sheet, but it doesn't look like that's possible, xince I'm working with only Adobe Reader, not Acrobat. Anyway, I need to compare 1 column of this report to another report that IS in Excel. Is there a way to have a macro compare an Excel file with a PDF??
View 2 Replies
View Related
Jul 2, 2008
The following formula gives me the error message #REF!
=INDEX(INDIRECT("R7"),MATCH(V6,D8:D11,1),11)
The problem I believe is in the INDIRECT("R7") as the following formula works
=INDEX(Well_AA_09,MATCH(V6,D8:D11),1),11)
The content of cell R7 is the text Well_AA_09 which is the name of a dynamic range I have created and pasted from within VBA into cell R7.
View 9 Replies
View Related
Sep 13, 2013
I have a fairly simple macro (below). I want it to do one more thing.....not run if the filters have not been changed. I would like to prevent the error that occurs if someone tries to run it and there are no filters active.
Private Sub AutoFilter_Remove()
Sheets("Data").Select
ActiveSheet.Unprotect "password"
ActiveSheet.ShowAllData
[Code].....
View 9 Replies
View Related
Jan 10, 2010
if i enter a value in 1 cell and want it to increase by a percentage how can this be done without having a sum column etc
View 9 Replies
View Related
Jan 31, 2007
I'm having some difficulty with this one. I have found several posts on how to make a macro work through several rows, but cannot seem to figure out how to apply it to my macro.
Right now I'm having to write the macro for every line and it's a bear! Here's what I'm working with right now and it's ridiculous (I've actually got through line 51, but I'll only post a few here, for your sanity!)- there has to be a better way:
If Range("C9") = "" Then
Range("T9").Formula = "=IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))"
Range("U9").Formula = "=IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))"
Else
Range("T9").Formula = "=IF((T8+U8)>=180,0,(IF(K9>I9,IF((I9-H9)>=180,180,(I9-H9)),IF((I9-H9)+(K9-J9)>=180,IF((K9-J9)>=180,0,180-(K9-J9)),(I9-H9)))))"
Range("U9").Formula = "=IF((T8+U8)>=180,0,(IF(K9<I9,IF((K9-J9)>=180,180,(K9-J9)),IF((I9-H9)+(K9-J9)>=180,IF((I9-H9)>=180,0,180-(I9-H9)),(K9-J9)))))"
End If............................
View 4 Replies
View Related
Feb 7, 2014
I'm trying to convert some data from MLS to make it easier to work with statistically. The raw data regarding Bath count downloads from MLS in 2 columns; 1 column for Full Baths & 1 column for Half Baths (see Raw Data.jpg). I want to concatenate the baths to one column and, if there is a 1/2 bath, tack ".5" to the end of the full bath count.
The problem with my formula is that if it concatenates the ".5" to the full bath count number, it converts the format to text (see Current Formula.jpg). I know I can go through later and convert it back to number format but is there a formula I could use that would keep it in number format?
View 3 Replies
View Related
May 7, 2008
I have generated macro, all I want is when this macro runs on different work sheets it should automatically select the active range based upon column A and copy the formula in the inserted new column B, please see the macro which will make it clear. Basically whenever it is copying a value or a formula it should be for the active range.
View 11 Replies
View Related
Aug 21, 2009
OR can I? I get a "Run Time Error '1004' Select Method of Worksheet Class Failed"
Is there anyway around this?
Basically all the sheets are hidden from the user except for the Entry sheet. They input there data on there and then they press a send command button which then needs to copy that information to a hidden sheet. This is where the problem occurs.. Thought maybe their was a way around that or do I need to unhide the sheets during the macro and then rehide them again?
View 13 Replies
View Related
Mar 9, 2014
Is it possible to make excel template work only on 1 computer by giving any vba password (on lines of license number).
Aim is to ensure it works on only client computer, even if template is shared outside it becomes useless.(We often see clients audit team getting access to templates using it for thier purpose & distributing).
In same spirit what are best practices one can take like protecting formulas with password etc
View 3 Replies
View Related
Aug 14, 2008
Our system can create an SLK file showing customer open orders. It is ALWAYS in the exact same format. I created a macro to open this file, manipulate the data (convert text to numbers, convert text date to actual date, and a few other things) and then make a pivot table.
When I try to run it it gets hung up on the pivot table and the debug highlights the BOLD below:
Columns("F:L").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"SOLIST!C6:C12").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
The RowField, ColumnField, and Page Fields are correct.
The QTY is the data that goes in the field.
View 9 Replies
View Related
Oct 10, 2006
i have the following formulas that work perfectly seperatly
=MAX(0,(E28-D28-"0:45")*24-F28)
=IF(C28=$C$70,C28=$C$72),2,0)
but it is imperative that i get them to show there results in the same cell.
neither are used at the same time, only one result is required depending on what is needed.
i did try putting them together as follows but did not work
=MAX(0,(E28-D28-"0:45")*24-F28),IF(C28=$C$70,C28=$C$72),2,0)
View 11 Replies
View Related
Jan 19, 2010
I'm trying to get some formatting/formulas to work, but apparently I'm not doing it right (otherwise, I wouldn't be here ):
1. Column A has dates. I want Column A_ to be red if the date is a week old or more from today's date, and white if it is not.
2. Column A has dates, and column B has dollar values. I want three four summations:
-Total dollar value from dates a week old from today's date
-Total dollar value from dates 2 weeks old from today's dates
-Total dollar value a month old from today's date
-Total dollar value year to date
View 9 Replies
View Related
Dec 8, 2013
I have a niggling problem with a worksheet when using Hlookup to return a value from a range of cells it is coming up with blanks when there is data in the range.
There is only ever one row cell containing text in the specified range and i need this to be returned in another column.
The problem with the hlookup formula i am using is that it will not work if cells contain formulas and in the range i am working on all cells contain formulas.
I know this formula works if there is text without a formula in the cell range, as soon as it hits a cell with a formula though it won't work.
I have attached the sheet : Book4.xlsx
From j138 there is one cell with text i wish to be returned in column q for each row. E.g., in q1 the first cell text returned would be khawatim. q2 should contain cantor etc etc.
View 7 Replies
View Related
Jul 31, 2013
I have created two documents. One document (A) is my worksheet where I input data, the second document (B) is my spreadsheet where it comprises all my data into the fields I want. I have completed both documents on computer (1) and now need to transfer it to another computer (2). However my formulas no longer function after I move the document (A) from computer (1) to computer (2). Is there a way for me to work around this? Do I need to transfer the spreadsheet (B) to computer (2) in order for them to function correctly? How can I make it so I have the worksheet (A) on computer (1) enter data correctly into the spreadsheet (B) on computer (2)?
View 4 Replies
View Related
Dec 28, 2007
This formula is returning 0.2578 when it should return 2.473
{=AVERAGE(('Data-Design'!E2:E6000)*('Data-Design'!Q2:Q6000=1))}
What am I not understanding?
I want the average of column E when column Q=1
View 9 Replies
View Related
Apr 27, 2013
I'm working on a spreadsheet which will sum values from two source worksheets based on the month and year in certain columns. I put together a summary worksheet and was able to successfully pull the data I wanted. So my next step was to convert the Summary data into a table (because I prefer the aesthetic value of the "greenbar" effect in Excel tables). But as soon as I converted the cells to a table, the formulas returned nothing.
After playing around with it, I determined that the problem seems to be the fact that I use the year criteria as a cell reference (that way, I can later change the year in one cell and update all the dependent formula). I've got "2013" in Cell A1 in General format, and all the formulas use a YEAR=$A$1 criterion. I found out that if I changed the formulas to YEAR=2013 instead of using the cell reference, I got the desired result again. But I cannot quite understand why changing the data into a table would alter the way Excel interprets the formula (plus, I still want to be able to change the year by updating one single cell).
View 5 Replies
View Related
Aug 29, 2008
******** ******************** ************************************************************************>Microsoft Excel - Book3___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)boutA1=ABCD1ABC 2464104641046410 32081500002081520815 44062000040624062 51521259171521215212 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I am doing is comparing Zip Codes from 3 Data Bases against each. If all 3 DB's are equal then I need a responce of "Ok", if there is a difference in any of the 3 then I need a "Bad". I know this is simple, but Data Base A can have the Zip Code as all 9 numerals or just the first 4. It only depends on who entered the Zip whether or not the trailing 4 numerals were entered or the were entered as all zeros. To throw another wrench in it, if the leading numeral is a zero, all three DB's will drop the leading zero.
I want to do all of this in 1 Function, not 5 fromulas.
What I need is for Database A, test the cell for Length and if it is 8 or 9 numerals then remove the right 4 to only leave the left 4 or 5 numerals (4 if it had the leading dropped 0) and then do a simple If A=B and A=C Then "Ok" Else "Bad".
View 9 Replies
View Related
Jan 28, 2014
Problem we are having with sorting a table.
The table (A1:E10) was created by pulling data from another worksheet using INDEX and ARRAY formulas.
We inserted 6 columns to move the table over to G1:K10 (used column F to space the two tables).
We then duplicated the table: cell A1=G1, B1=H1, ...., E1=K1 and dragged the formulas down. We now have a duplicate table that we want to sort.
We choose CUSTOM SORT and choose 2 levels of sorting ... column A and column C.
PROBLEM: The ARRAY formulation evidently overrides the sort function because the table remains as it was when the sorting command is executed.
Is there a way to bring the data from table G1:K10 over to table A1:E10 in a manner that will allow sorting??
View 9 Replies
View Related
Mar 26, 2014
So i got a few macros on a worksheet and one of them is simply :
Sub Pr_Removefilter()
ActiveSheet.ShowAllData
End Sub
Ive worked on the excel a couple of times, everything fine. Then it started crashing upon saving (after changes made that had no possible connection to the macro, on another sheet). After a few painfull trials and errors i located the problem to be this macro button. I erased it, created a new one with the EXACT macro and it works. Why?..
View 1 Replies
View Related
Sep 17, 2007
I need to compare two tables in two different worksheets (A and B) and update the weekly changes (income and sales) from B to A. I have been using an Index(Match) formula which has been working perfect. I am however forced to update to a macro.
The worksheets are identical and each item is identified by a unique ID.
Any ideas in exchange for a 6-pack Budweiser?
View 4 Replies
View Related
Jul 21, 2014
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
View 1 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 Replies
View Related
Feb 20, 2014
I have a problem with 2 "IF formulas"
A B C
23:30 00:30 1:00 - =IF(B2>B2,A2-A2,B2+1-A2)
14:00 13:00 On Time -=IF(B3<A3,"On Time","Delayed")
My question is: - how to make the first line to show me Delayed. It is a problem with the time before and after midnight.
View 5 Replies
View Related
May 19, 2014
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
View 9 Replies
View Related
Jun 16, 2014
I am using a formula for weeknumbers and formatting cells based on their values but have just realised it will not work at year end.
The formula in D2 is as follows:
=IF($C2<$O2,"green","blank")
C2 is the weeknumber of an incident based on the actual date in B2 (e.g. 01/06/2014) and uses the formula: =WEEKNUM(B2). O2 is the weeknumber of the current day and uses the formula: =WEEKNUM(NOW()).
So cell D2 states "green" if the incident happened over a week ago, and states "blank" if the incident happened in the current week.
If I used this at year end when the incident weeknumber may be 52 and the current weeknumber may be 1 - the formula would not work as it would state "blank" rather than "green", even though the incident happened over a week ago.
How can I get the year encorporated within the weeknumber?
View 4 Replies
View Related
Feb 15, 2007
I found the thread about how to get the second best result (=LARGE(range,2)) but that can't handle when I have ywo top values and a lower third value. It still return the next value after the top value, which is the same value. I want to get the next lowest vaule.
Example:
In this row:
7
7
6
5
5
4
I want to get the value 6 and the formula =LARGE(range,2) gives the second number 7.
How do I get the second value that I want?
Maybe if I use the RANK() formula in some way, but how?
View 9 Replies
View Related
Jan 31, 2013
I have the following line of code:
Code:
Range("D" & LastRow).Value = Area.Text + Level.Text
Area and Level are both comboboxes on a userform, I want them to both go to the same cell but with a space between them. Right now it looks like "AreaLevel" but I want it to look like "Area Level".
I assume that I can do this by making each value in the combobox have a space after it but I would like to know the proper way of doing it...
View 3 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
