Is there a way to copy a formula result as the actual value and not the formula,
I can do copy and paste using "value" option but would like an automated formula or code option if possible.
In the example, D4 copies C4, would like D4 to show "AB" and not the formula "=C4"
Is it possible to use the IF formula and if the result is true, to run another formula and if the result is false put a "0" in the cell???
Here is an example...
In cell A1, I have an amount of money.
In cell B1, I have a quantity.
In cell C1, I want to use the following IF formula:
=IF(B1>0;((A1)*(B1)*0.15);0)
At the present time, when I do this, when the result is true, I see ((A1)*(B1)*0.15) in the cell instead of what that formula should give.
I created a simple auto numbering function whereby Cell A7 contained =Row()-6, and Cell A8 contained =(A7+1). I then shift, and pasted the contents of cell A8 until cell A600. My aim is to simply copy the increments of 1 - 600 into another column. However when i copy and paste i'm also copying the initial underlying formula ie: =( A?+1), Is there a way to copy the results, not the formula?
View 2 Replies View RelatedIf the result of cell B1 is true then YES if not NO?
I know I can use the formula: =IF(B1="Yes","YES","NO") but I'm looking for the TRUE or FALSE result.
Sheet1
AB110Yes2 3 NO
Spreadsheet FormulasCellFormulaB1=IF(A1>5,"Yes","No")B3=IF(B1=TRUE,"YES","NO")
Excel tables to the web >> Excel Jeanie HTML 4
I have a formula that I'm trying to utilize VLookup to determine a specific bonus. The issue I'm having is when looking for the closes match, it is returning the value below where it should because it's rounding down. For example, the string I have reads:
=VLOOKUP(S5,'Rep Daily'!A1:B61,2,TRUE)
The value it's defining is 17.7485, so it's returning the bonus for 17.5 instead of 17.75. Even when I use the =Roundup function, it's showing 17.75 in the cell but the actual value of the cell is 17.7485 so VLookup is returning the lower of the two values.
I want to run a macro if the result of an IF function is true.
E.g. cell J55 contains =IF(H55>I55,"Goodbye","")
H55 contains =NOW()
I55 contains =DATE(2012,12,31)
Now by changing the dates etc. it prints Goodbye.... simple enough, but what I want is for it to run a macro that selects a range from the previous year's figures and simply changes the fill colour on the range. The macro for this also works fine.
I've tried using the worksheetfunction.sumif route and couldn't seem to get the syntax correct. I'd prefer the worksheetfunction route, as I don't need the formula stored, but it would be great to have both of these pieces of code for reference online - I scoured Google, and the examples I found were pretty weak.
I've recorded the macro (hence the R1C1 references in the second example) and had it work perfectly, but when I replaced row references with my variables, it went back to displaying "TRUE." I'm including all the code here.
Variables:
expr = 3 to 38 (For loop)
startrow = the starting row for the reference data
endrow = the ending row for the reference data
thresh = minimum threshold (a number)
Syntax error here:
I have a worksheet called "Raw Data" where in columns J, I and G contain values. I would like to write a formula whereby if all 3 conditions are met, it will count the number of values found in column C.
I've tried to write an IF statement but it just returns FALSE, despite the result being true. Need the right combination of IF/AND statements that would do this?
=IF(AND('Raw Data'!$J:$J="Maintenance",'Raw Data'!$I:$I="Open",'Raw Data'!$G:$G="1-2008"),COUNT('Raw Data'!$C:$C)). I've tried to attach the workbook, but there's a problem with uploading attachments I think. Sorry if my explanation is unclear.
I am new in Excel VBA and im trying to find a way to get back a result "True" or "False" when I have password, wich must consist of 6 letter, one upper case letter and a number must be in it. When this word consist of 6 letters and has a uppercase letter and number the result is True and this will be written into the cell next to the cell where the password is.
View 2 Replies View RelatedI am trying to create a formula to count the number of time the word "NEW" appears in one column, provided the result of another column gives a certain answer. Sample of my problem and question in detail on attached.
View 2 Replies View RelatedI am trying to implement a simple macro. Here is my
Function Age(DOB)
Age = Int((Now - DOB) / 365)
End Function
Sub Macro1()
Age (Selection)
ActiveCell.Offset(0, 1).Select
******
End Sub
The function basically calculates the persons age using their date of birth. What I need to know is how to copy the result of the function into the cell next to the selected one. (The code above only selects the adjacent cell).
I've got a formula that has numerous nested IF statement and am trying to get the true result to be the first of a given month of the current year. Here's my formula:
Code:
=IF(Setup!$B$3="X","1/1",IF(Setup!$C$3="X","2/1",IF(Setup!$D$3="X","3/1",IF(Setup!$E$3="X","4/1",
IF(Setup!$F$3="X","5/1",IF(Setup!$G$3="X","6/1",IF(Setup!$H$3="X","7/1",IF(Setup!$I$3="X","8/1",
IF(Setup!$J$3="X","9/1",IF(Setup!$K$3="X","10/1",IF(Setup!$L$3="X","11/1",IF(Setup!$M$3="X","12/1",""))))))))))))
Everything works, save that the cell becomes 1/1 with no year at all. I've messed around a bit with TEXT and various DATE/MONTH/YEAR functions on this one, but can't seem to get it correct.
When I drag my VLOOKUP formula down a column in Excel 2010, the return value copies the formula result from the original VLOOKUP formula result. For example, if the first VLOOKUP returns a value of 0.5, I expect to see 0.5 or 1 in the cell below that one. However, I get 0.5 which is not the expected result for the cell below.
When, I click the fx on the cells below, the expected return values appear in the formula result. After I click OK, the expected formula results updates and now appears in the cell.
I'm not sure what is causing this issue. My computer was updated recently from an old machine to a new one. I have never experienced this issue before.
Is there a simple solution, by changing something in the button properties? My current code below allows data from a userform to goto specific sheets and cells. Currently my option button named winbutton if true will display "true" in cell. i need it to be numerical value "1".
View 3 Replies View RelatedI have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
I am trying to write a formula using If statement to output the result "True".
I have 4 columns (P3, T3, AD3, AK3) that store a value of either "Yes" or "No", I want to check the column if they are equal to "Yes". I have written a formula but it only works for 1 column at the minute, i not sure how to add the other columns into the formula:
=IF(P3="Yes","True")
If you look at the attached spreadsheet you will see 6 tabs. Main, New Cust, Quote, etc.
"Main" will be the sheet that everything is inputted. If there is a "Y" under new customer, I want the entire row to copy to the tab "New Cust". If there is an "X" under quote, struct design, or graphic design I want it under its approp. tab. The Lisa tab should take certain Sales-Person and copy entire row on the Lisa tab. The certain Sales-Persons are BS, PK, PB, PD.
Buttons are fine. The "main" page will be getting updated daily w/ new info so after entering new data, i can press the button and it should copy the above to its locations.
I want to use the If formula but want the value if true/false to be another formula, not just a set value. Here's what I came up with (obviously wrong, please excuse my lack of experience) =IF(D2>C2,"=(D2-C2)*5","=((60-C2)+D2)*5 ")
View 2 Replies View RelatedI have a list of items that I am preparing to upload to an access database. The list is quite lengthy and has required a lot of manual corrections to get it prepared for upload. It also has several columns that are not needed in the access database, but that I used to make sure that all of my data was ready. What I'd like to do is create a new table that will have the same structure as the table in my access database and upload the portion of data that I have ready now so that I can do some further development using actual data. In order to do this I need to extract the ID Numbers (column B values) of the records that are marked "True" in the "Ready" column (column G Values) and deposit them in the new table. I only want to copy over the ID Numbers as the additional fields will be populated with match / index lookups from a third table that I have on another worksheet.
View 2 Replies View RelatedI need a macro that would sort out the true value in column A, and paste it to another sheet (range A77)
View 10 Replies View RelatedI need a macro to copy and paste cells from one worksheet to another, if there is the word true in the column L of that row.
View 10 Replies View RelatedIt brought up my own thread, I didn't realise it untill today, but the answer I been using uses conditional formating to highlight the lowest cells in each row. And as Sicarii pointed out earlier today you can't select a cell my color with conditional formating turned on. can I highlight some other way or can I select cells to copy some diffrent way. This is the VBA I use to select the lowest price.
Sub FIND_LOW_PRICE()
Range("a1:H6").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=and(a1=min($a1:$H1),not(isblank(a1)))"
Selection.FormatConditions(1).Interior.ColorIndex = 6
End Sub
And this is the code that Sicarii supplied and correctly warned me will not work with conditional formating.
Sub COPY_BASED_ON_COLOR()
Dim RngCol As Range
Dim lLoop As Long
With Sheets("Sheet1")
Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
For lLoop = RngCol.Rows.Count To 1 Step -1
If Range("B" & lLoop).Interior.ColorIndex = 6 Then
Sheets("Sheet2").Range("B" & lLoop) = Sheets("Sheet1").Range("B" & lLoop)........................
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
I need to copy the row data from sheet1, if the DEPT is HR and paste the same into sheet 2.
Qus:
data table ( Sheet 1)
SLEMP IDDOJ DEPT
1123419/17/2010HR
2123429/17/2010HR[code]......
I need to copy the data form Cell "C" to Cell "D" only if Cell "D" is empty
i have been using the following code but it will overwrite cell "D" regardless if data is present or not.
I have a series of checkboxes, we'll say checkbox 1-5. These are all listed on a sheet called products.
If for example you select check boxes 1,3 and 5 I need those to then be listed in a column in that order on another sheet called purchased.
If I went back and then uncheck 3 and check 4 then purchased would be updated.
I understand that if you take a cell and name it P1 for instance..and then on another sheet enter =p1 in a cell that they will update and share data, but I dont know how to pull from a check box..nor do I know how to assign a row of cells to accept multiple and changing information from those checkboxes.
if there's a macro that can be run to check one worksheet. In this worksheet there is a column where the value should be "true". If this value is true, I'd like to copy the entire row of data into another worksheet.
There can be multiple rows of data where this condition is met.
My rows consist of the following cells, an author(s), an article name, a pubilcation name, a volume number, and a page number. There are thousands.
My first column has multiple names and need to separate the names into separate cells. Some names have a comma or semi-column separating them, some have the word "and" separating them. Most of the names are listed as last name, first name. (Some are not and I will have to deal wtih that later.)
Row 1:
Eisenhofer, Jay W., Jordan, Kirk S. Tucker, Marc B."Business Faces Harsh Sentencing Penalties",Delaware Business Review, October 7-13, 1991951
Row 2:
Embley, Craig M., Turezyn, Andrew J. and Welch, Edward P."Recent Developments in Delaware Corporate Law",S.A.S.M.& F., May, 19901443
I'm sure is a multiple part process. Is there a formula for placing anything separated by a comma into a new cell, or placing a tab between the names?
Ultimately this data will be placed in database from which to search for a name and article, etc.
trying to put a formula in to find the true value of cells, i.e.
need the positive value of these cells (into one cell), note below, assume colume A1-A6.
FALSE
FALSE
FALSE
35
FALSE
FALSE
I am using a spreadsheet to determine if students are meeting Growth Targets between a pre test and post test.
Here is my formula
=If(D8>=C8,"yes","no")
D8 is the Growth Target and C8 is the Post test Score.
After doing this, I need to calculate the percentage of Yes results. This year it will be easy, as it only applies to 25 students. Count the yes's and multiply by 4. Next year, it will apply to number in the neighborhood of 150 students.
Here is a sample
PreTarget Post
Student 15778 82Yes
Student 25369 91Yes
Student 35778 75No
Student 45778 68No
Student 55369 50No
Student 63761 50No
Student 74061 68Yes
percent of yes's