I am using this formula. How can I get it to pull 'Sales Materials'!$F17:$F69' and display along with 'Sales Materials'!$B$17:$B$69 when my logical statement is true.
=INDEX('Sales Materials'!$B$17:$F$69,SMALL(IF('Sales Materials'!$E$17:$E$69="YES",ROW('Sales Materials'!$B$17:$B$69)-16),ROW(A1)),1)
I've tried using * but I think something is going on with "-16" which I was told to you use to account for headers.
I have a large table which I am filtering 3 times. There should be results but the final VBA filter does not return them until I manually apply the filter. The custom filter is correctly populated with the criteria. The code that I am using is as follows:
VB:
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="<>", Operator:=xlAnd
Selection.AutoFilter Field:=10, Criteria1:="=INV", Operator:=xlOr, Criteria2:="=CRD"
Selection.AutoFilter Field:=21, Criteria1:=">=" & DTPicker1.Value, Operator:=xlAnd
The first two filters work fine. The last does not. Oddly it works on some date picker values and not others, even if there are actual results and as mentioned earlier if I stop the program running and apply the filter (without typing anything else) it works fine.
I'm trying to have an IF function analyze a sheet and then return a list of names that return a True
=IF(AND('LTV Watcher'!L2="Yes",'LTV Watcher'!M2=""),'LTV Watcher'!A2)
and it returns:
John Doe, LLC
however I'd like it to function to analyze a range of cells:
say L2:99, M2:99, and A2:99
In the attached I have an index/match formula in B15 that I cant get to return any results not even a N/A#
Quick Quote Tool.xlsx‎
I have been searching all day about this topic and while there are many "solutions" none of them fits my criteria and I can't figure out how to tailor it.
I am using a very basic data sheet to populate a purchase order. I want to be able to search a style and have all the data (color, units and price) automatically fill in upon entering the style number, I have been using vlookup but this only works if there is one color per style.
I have found different lookup functions that give the value +1 when the data is sorted but it returns the value even if it doens't fit the search criteria, I only want to return the second value if it correlates to the initial lookup
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.
I pass in the query name and the sheet name into qryName and sheetName name.
Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
[Code].....
I am trying to create an advanced filter that filters my results into a row.
e.g.
Shane
Shane
Tom
Tom
Paul
Mike
would return
Shane Tom Paul Mike
I have tried changing this piece of code
Range("M1:M300").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns("M:M"), CopyToRange:=Rows("25:25"), Unique:=True
The problem is with
CopyToRange:=Rows("25:25")
I have tried using Range instead of Rows but I am not having any joy.
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
VB:
ActiveSheet.Range("$A$1:$I$566").AutoFilter Field:=1, Criteria1:= _
">=11/11/2012 22:13", Operator:=xlAnd, Criteria2:="<=11/12/2012 6:47"
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
VB:
[FONT=Consolas][SIZE=2][COLOR=#0000ff]xlsRange1.AutoFilter(Field:=1, Criteria1:=">=11/11/2012 22:13", Operator:=Excel.XlAutoFilterOperator.xlAnd, Criteria1:="<=11/12/2012 6:47")
This returns 1 instead of 4 expected.
Found code to find files and return results.. edited to my liking(barely.. i have no VBA knowledge).. it works on the PC I use. This is intended to be on a shared folder across a network for others to use, and it is not returning any results on the other PCs. This computer has Office 2003.. the rest have 2000. I can NOT figure out the problem. I have read in searches that Application.Filesearch is not reliable, but I do not know how to change it to any other type of coding
Attachment 26214
Option Explicit
Sub SrchForMSDS()
Dim i As Long, z As Long, Rw As Long
Dim ws As Worksheet
Dim y As Variant
Dim Fil As String, FPath As String
y = Application.InputBox("Search for file(s) named:", "MSDS Search")
If y = False And Not TypeName(y) = "String" Then Exit Sub
Application. ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "\My-SbscompanyMSDS Database"
' \#.#.#.#companyMSDS Databse or My-Sbs
.SearchSubFolders = True
.Filename = y................
want to match column A to column C and if they match take whats in column D and put it in column B.
Column C has multiple instances of Column A and I would like each instance's, which is the following column, value put into column b
This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.
View 2 Replies View RelatedI have a range that has formulas that are based on other fcells outside the range.
What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.
There could be a marco to run when this process is needed.
example attached
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies View RelatedI am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx‎
It works I1 is blank and it works when F7:K7 has data and I1 is initialed. However, when I1 is initaled and there is no data in F7:K7 the cell reads "N/A". It should read "Approved with insufficient data".
=IF(ISBLANK($I$1),"",IF(ISBLANK(F7&G7&H7&I7&J7&K7),"Approved with insufficient data",LOOKUP(99999999999,F7:K7)))
I am trying to sum a column with values using a report downloaded from Celltracker, however the sum total is returning as zero.
When added using =a1+a2 the value returns correctly.
To get cell to display results of formula I must edit (F2) and hit enter. Is there an easy way to do this in bulk rather than editing and hitting enter on hundreds of cells?
Example of formula:
=IF(COUNTBLANK(K2)=1,"",CONCATENATE("*** ",K1,": ",K2," "))
I have two cells. The first cell has the formula:
=CONCATENATE("D",TEXT(MATCH($B$6,'Zip Ranges'!$D$1:$D$157,0)+1,"0"))
which results in a col and row number (such as D65).
The second cell has the following formula:
=INDEX('Zip Ranges'!$A:$B,MATCH($B$6,'Zip Ranges'!D1:$D$157,0),2)
^^
I wish to replace the 'D1" in the Match function with the results of the first cell's formula.
I assume Indirect would work, but I don't know how to code the formula to use it.
I need a formula for cell P5 that returns the date for the following Wednesday from a date in cell I3?
For example, if in cell I3 01/05/2014 (through to 06/05/2014) is entered, cell P5 should show 07/05/2014
If the Wednesday date is in cell I3, cell P5 should should that same date (e.g. 07/05/2014 in cell I3 and P5).
=AVERAGEIFS($D$106:$D$2825,$D$106:$D$2825,">"&$I117,$D$106:$D$2825,"<"&$J117)
Returns = 0
But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?
the formulas in sheet 2 of the attached file, the formula from rows 27 is returning nothing, and I think I’m not getting the formula right. It may need a little correction.
View 5 Replies View RelatedI am trying to average a data set where some rows at the moment are blank but will update at a later date, I am getting a div/0 error, the formula I am trying to use is
=IF(C23:C30="","",AVERAGE(C23:C30))
I thought this would look at the data and see if its blank leave it blank but if there are numbers there it would give me an average.
I have a Countifs formula that is returning a zero "0",
=COUNTIFS($B$4:$U$4,$A29,$B$10:$U$10,B$18), and it should return a 1.
The manual calculation is turned on and I have tried the F9 and no change in the value returned.
I have the following Vlookup formula:
=VLOOKUP(G3,'Calculation for September 07'!C7:E1975,2,FALSE)
The formula is returning #N/A, but I see the lookup value in the table array.
What do I need to add to the formula to have the lookup value recognized?
1) Not using a macro and combining relevant w/s functions, is there an intelligent way to return the value of:
SUM(n=n1 to n=n2) of (-1)^(n-1)*(n)^(n-2)/(n-1)!*X^n
to a cell containing the formula ??
2)Example:
n1 in cell A5:: 1 (integer variable)
n2 in cell A6:: 32 (integer variable)
X value in cell A7::1.00
Returned sum in cell A8:: 0.567143 ...
I have the following SUMPRODUCT FORMULA, Ive tested all the data in the columns. It's returning a #VALUE. Can someone explain?
=SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")*(C1:C10))
I'm using Excel 2003, if that is a factor.
I am using the OFFSET formula (=OFFSET($A$1,0,0,$B$1,1) and instructions (http://www.ozgrid.com/Excel/DynamicRanges.htm) I found on this site (which is great, by the way).
In the instructions on the site, it requires you to define a range for which the formula to apply. What I tried to do was skip naming the range, and simply using the OFFSET in various cells throughout the file. However, whenever I enter in a row [height] of greater than 1, the formula returns a #Value error. I want to use this offset on multiple data ranges, so naming all of them would be rather time consuming. Is there any way to skip this step?
why is this formula
=IF(ISERROR(AVERAGE(H14:H16),"",(AVERAGE(H14:H16)
returning DIV#!0 if there is no data in the target cells, I thought the ISERROR was meant to stop that?
I am trying to determine if I can write a formula that will read text in one cell, such as insurance and if true enter the amount from another cell and if false then 0. I have tried a IF statement by add the text to name manager. However, the answer is 0 rather than the amount in the selected cell. I might need to use a different formula or function.
View 10 Replies View Related