If Statement In Cell Doubling Quantity That Is Being Returned?
Sep 10, 2013
I have a spreadsheet that I am working on for an estimating tool for engineers in my office. I created an in cell pulldown that contains all of our pipe specs. The theory is that on another worksheet I have all the information for the labor costs, and by changing the spec in the pulldown will automatically change the numbers in the estimation table. Below is the if statement that I created. the only cell that I have the completed "if" statement in is C9, because it is adding almost double to the actual quantity that is supposed to be returned.
Code:
=IF(Sheet2!M2="SS1",VLOOKUP(A9,Sheet2!A3:J19,3),IF(Sheet2!M2="SS2",VLOOKUP(A9,Sheet2!A23:J39,3),
IF(Sheet2!M2="SS3",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS1",VLOOKUP(A9,Sheet2!A43:J59,3),
IF(Sheet2!M2="CS2",VLOOKUP(A9,Sheet2!A43:J59,3),IF(Sheet2!M2="CS3",VLOOKUP(A9,Sheet2!A43:J59,3),
[Code] ......
View 2 Replies
ADVERTISEMENT
Aug 27, 2009
I am trying to have a value returned in cell C3 if two conditions are met. In Cell C3 (Sheet 1), if the value in Cell A3 (Sheet 1) is listed in column A on sheet 2, and the 12031 is listed in column B on sheet 2, I need the value of Open to be returned.
View 3 Replies
View Related
Feb 6, 2009
I am looking to return a value of 2 cells in to one
ie
1+1 (2) = 4
1+0 (1) = 2
View 2 Replies
View Related
Nov 23, 2009
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
View 5 Replies
View Related
Sep 5, 2013
I have a countif that is doubling when counting. I am using:
=COUNTIFS('Stage 3 Data'!G2:G3000,">="&Breakdown!C19,'Stage 3 Data'!G2:G3000,"
View 3 Replies
View Related
Aug 31, 2006
i have is a worksheet, columns A thru J. Column B has names in it, Column F has dollar amounts. i need to create a macro to find the duplicate entries in column B, add them in column F (which would overwrite the existing amount) and then delete all the duplicates. all of the columns between A and J contain data. i was able to do this on an totally different sheet using columns A and D. but i did not have other data in the sheet. it's been a long time since i completed this other sheet and i need a refresher course in vba.
Sub finddups()
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Set Sh = Worksheets(1)
Sh.Columns(5).Insert
LastRow = Sh.Range("A65536").End(xlUp).Row
With Sh.Range("A1:A" & LastRow).Offset(0, 4)
.FormulaR1C1 = "=IF( COUNTIF(R1C[-4]:RC[-4],RC[-4])>1,"""",SUMIF(R1C[-4]:R[" & LastRow & "]C[-4],RC[-4],R1C[-1]:R[" & LastRow & "]C[-1]))"
.Value = .Value
End With
Sh.Columns(4).Delete........................
i need to convert this old macro to work with my new sheet. since the underlying ideas are the same i think it should work. however i am unable to get it right. i always end up deleting all my data.
View 3 Replies
View Related
Jun 14, 2009
I'm trying to add a hyperlink to the final outcome of: =SUBSTITUTE(Info!$G$28,"village=99999","village=" & Z8). I need to add the hyperlink to the value returned in the cell. The value ends up being something like:
http://en28.tribalwars.net/game.php?...5&screen=place but it isn't a hyperlink.
The hyperlink can either be like this:
http://en28.tribalwars.net/game.php?...5&screen=place or like this
View 3 Replies
View Related
Oct 10, 2013
This formula works great but in my example if A1 cell is blank how can i get it to return a blank instead of a 0?
=SUMPRODUCT((RANKING SALES[Master.xlsx]Tyre!$A$2:$A$156
=A1)*(RANKING SALES[Master.xlsx]Tyre!$B$1:$N$1
=$I$4)*'RANKING SALES[Master.xlsx]Tyre!$B$2:$N$156)
View 4 Replies
View Related
Dec 2, 2009
I have a formula (AL15) to determine the address of the last column with a value greater than 1. =COUNTIF(C16:AC16,">=1")+2. I then use this column value to aquire the Absolute row "15" and the determined column=(ADDRESS(15,AL15,4)). This returns to me a Column value and row value that I need to use as a refference. How can I convert this returned value to a reference.
View 2 Replies
View Related
Feb 12, 2010
How do I put in iserror in to this to make the cell go blank with N/A is returned. Only ever done this on vlookups. =IF(I15<E8,"TOOLING REQUIRES CALIBRATION","")
View 2 Replies
View Related
Mar 21, 2014
I'd like to generate a total sum based on the quantity from each item. So columns H3 - N3 should generate a total in P3 (skipped O) by the quantity entered in each cell. In addition I'd like the value of D3 to change the pricing in the formula. D3 has a drop down with 3 products, so selecting ANY of those products should cancel out the previous formula and replace it with another.
This is the sum without a value in D3
=SUM(H3*10.00)+(I3*8.00)+(J3*6.00)+(K3*18.00)+(L3*10.00)+(M3*8.00)+(N3*8.00)
IF D3 has a package selected from the drop down, I'd like this sum to generate instead.
=SUM(H3*8.00)+(I3*6.00)+(J3*4.00)+(K3*15.00)+(L3*8.00)+(M3*6.00)+(N3*6.00)
[code]....
View 3 Replies
View Related
May 8, 2009
I have a spreadsheet with details of letters that should be responded to within 20 days, and i want to have a cell showing the amount that that need a response within 10 - 5 days. Basically I want to have a value of one returned to a cell in a row if one cell in that row contains a value between 10 and 5 (less than 11, more than 4) and another cell is blank. For example, AD27 contains 6, k27 is blank. I want AE27 to show 1. I also need the same if the value is less than 5, but i'm sure i could figure out how to alter it.
View 2 Replies
View Related
Apr 22, 2006
I've attached part of the file I'm working with, and can't figure out how to align the text to the top in the merged cells titled description. How do I format the cell so that if there isn't two lines of type in the description it remains aligned with the quantity and price
View 2 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Nov 22, 2011
If you have a data in two dimensional table. and you need to fill each cell with the value returned from another table.
Example : this the my table
Company A Compay B Company C Product 1
Product 2
Product 3
Product 4
and this my data :
Products Companies Price
Product 1 Company 8 Product 1 Company A 7 Product 1 Company B 5 Product 1 Company C 6 Product 2 Company B 6 Product 2 Company C 9 Product 3 Company A 10 Product 3 Company B 8.8
View 5 Replies
View Related
Dec 9, 2009
Is it possible to have a formula that will return the answer as the value.
For example if I have 15.75 in A1 and I use the formula =ROUND(A1,0), the solution is 16, but when you click on the cell it shows the formula and not the value.
I know about Paste Special, but was wondering if anything could be added to the formula to provide the solution.
Would this require a macro?
View 5 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Jan 29, 2009
I've got a workbook where sheet "Raw Data" is used to enter audit findings. Subsequent sheet "Analysis" contains formulas to extract quantities and nature of audit findings so that they can be shown on quarterly reports.
When I set up the workbook the formulas on the "Analysis" sheet worked fine. The department has filled the columns with data and now all the formulas are returning #Value! There's something fishy going on here.....
=SUMPRODUCT(--('Raw Data'!$H$7:$H$1000=A4),--('Raw Data'!$K$7:$K$1000=C$1))
View 4 Replies
View Related
Nov 20, 2008
I have a user form where a user chooses a file. The form works fine, but if the user clicks the (x) in the top right of the box I get an error. how to either remove the (x) or what value is returned when the (x) is clicked so that I can put some handeling for it in my script.
View 5 Replies
View Related
Nov 9, 2011
Here is my file after two rows of headers:
A3: any date
B3: any date
C3: =IF(A3>DATE(YEAR(TODAY()),1,1),A3,"") ... I need excel to return the date here only if it is within this year
D3: =IF(B3>DATE(YEAR(TODAY()),1,1),B3,"") ... same as above
E3: =IF(COUNT(C3:D3)
View 9 Replies
View Related
Apr 2, 2007
I am using a vlookup formula. In searching my sheet that uses this formula and it does not find a value that is in formula cell, why? Is there a setting that can be changed?
View 9 Replies
View Related
Jan 13, 2010
I have a sheet where I put a code from a product in Column B, and the name of the product will appear in Column C, with a VLOOKUP Formula. What I would like to do is, get a VBA code to display a msg box, if somebody enter a the wrong code. If that happen a #N/D will appear in Column C.
In this Sheet I already have the bellow VBA Code, to avoid repeated product codes:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range
If Target.Count 1 Then Exit Sub
Set Rng = Range(Range("B1"), Range("A" & Rows.Count).End(xlUp))
If Application.CountIf(Rng, Target) > 1 Then
MsgBox "O valor introduzido " & "(" & Target & ")" & " é duplicado. O Menino está a Dormir???"
End If
End Sub
View 9 Replies
View Related
Aug 23, 2006
I am trying to write a macro that will search in column ONE, then autofilter in column TWO.
So for example....
aaa | abab
bbb | abab
ccc | sdsd
aaa | abab
bbb | sdsd
I then search for "aaa"
then...(it autofilters by what is associated with column TWO, regardless of what was in column ONE)
aaa | abab
bbb | abab
aaa | abab
I was thinking...using Vlookup in column ONE, then Autofilter with column TWO.
My code right now is...but clearly it doesnt work.
Sub CommandButton1_Click()
Sheets("Sheet1").Activate
Dim Var As String
Var = Application.VLookup(TextBox1, Range("A2:AI772"), 34, False)
Selection.AutoFilter Field:=34, Criteria1:="*" & Var & "*"
End Sub
View 5 Replies
View Related
Nov 1, 2006
I have the following IF formula. It is working fine except the underlined part; it does not returned the value 15 ...
View 4 Replies
View Related
Nov 17, 2007
I have a 2 column array of numbers. Column A has duplicate values, column B has unique values.
I have a lookup which is counting the number of occurences of any given value in column A, but now need to populate a cell with a concatenated string of the values from column B that correspond with all instances of each unique value in column A.
View 9 Replies
View Related
Mar 30, 2014
I have a table like this:
QTY PRODUCT
---------------------
1 AB111
3 CC332
2 DA221
Is there a way to display that information on another sheet so that each individual product is on its own line with a QTY of one? I can do it with VBA but was wondering if there is an alternative?
QTY PRODUCT
---------------------
1 AB111
1 CC332
1 CC332
1 CC332
1 DA221
1 DA221
View 8 Replies
View Related
Oct 6, 2009
I have 3 columns:
in first cell (A1) i have a 553482300040
in second cell (B1) quantity: 1000
and in third cell (C1) i have first 2 numbers from first cell (A1), in that case 55.
In second row, first cell (A2) i have another code, but begins with 55 too, in second cell (B2) i have quantity for this: 500.
View 9 Replies
View Related
Apr 3, 2014
I have IDs in the first column of an excel chart. After that I have three more columns, being date of test, type of test (start, 3 months, 6 months, 9 months, finish), and lastly the result for the test.
Right now, the same IDs are listed multiple times for different results, so for example:
ID | Date | Type | Result
27 | 3/27 | Start |8.3
27 | 6/27 |3 Mon |7.9
27 | 9/27 |6 Mon |7.4
27 | 12/3 |9 Mon |7.2
27 | 3/27 | FINISH |6.5
What I need is the following layout:
ID | Start | Date | 3 Months | Date | 6 Months | Date | 9 Months | Date | Finish | Date
ID is only shown at left, and the values for the test result and corresponding dates are shown in their respective columns.
I tried to do an IF function with a LOOKUP inside, and it worked originally, but when I add more values for the same ID to the original column, it only shows the latest date, and only gives that result.
View 1 Replies
View Related
Dec 23, 2013
I have a table where the rows are conditions and the columns are experiment numbers, as an example below:
Experiment 1
Experiment 2
Experiment 3
Hardness
X
X
Solubility
X
X
Density
X
X
The table is fairly large. What I would like to be able to do is use a lookup/formula that will return all the experiment numbers a given condition is tested in. In other words, for a given condition (i.e., Hardness), which columns have an "X". Not sure if that can be done with a lookup or not or if there is another function necessary. I would like to be able to do it without macros though and also have it automatically update if I move the "X" around to different experiments.
View 3 Replies
View Related
Jan 7, 2014
Every week I have a set of open tickets with various dates. I need to break these Dates down to certain ranges.
Current DateAged Date Range Value to assign
10/20/2013 10/18/13< = 3 days A1
10/20/2013 10/7/13> 3 days & < 2 weeksA2
10/20/2013 9/23/132 weeks - 4 weeksA3
10/20/2013 9/22/13> 4weeks A4
I need in cell H:2 a value returned of with A1 thru A4 based on the results of the information in Columns A:D.
So basically it shoudl have in column H a bunch that fall in the reange of 3 day or less with "A1" then the dates that fall in the range of greater then 3 days but less then 2 weeks a value of "A2", etc. I hope i made this clear enuf to understand.
View 6 Replies
View Related
