Exact Criteria In 2-variable Table
Jun 26, 2009
This is probably so simple for an Excel wiz, but I've drawn a blank.
I have a 2-axis table, with the formula =DCOUNTA(Data,"Function",B15:C16) in the top left intersect cell. Data range is the table of info, which has location and function column. Location has values UK and Ukraine.............yes, you've guessed it, the UK row of the table returns both UK and Ukraine numbers.
The criteria range B15:c16 has Function and Location as headings and blanks underneath. The data table references B16 and c16 as the row and column variables.
How can you modify the criteria or table formula not to use starting-with criteria but exact matches ? (probably have to use another formula besides an array version of dcounta, right ?)
View 9 Replies
ADVERTISEMENT
Jan 8, 2010
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
View 3 Replies
View Related
Dec 12, 2013
Using the dcount function it appears that the criteria is not exact. I have typed in ="IP" but instead of getting only IP. I'm getting cells beginning with IP, eg IPp and IPe. When I use countif I don't get this problem.
View 3 Replies
View Related
Jan 14, 2014
Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?
Please see an example:
Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer
[Code] .....
I guess it has something to do with AVG and IF formulas.
View 2 Replies
View Related
Feb 12, 2008
Small Spreadsheet attached, using lookup gives the correct result for most but noat all initials entered? I do not get the correct result when I enter "JB" in the grey cell. Result (red cell) should be "John Burch".
View 3 Replies
View Related
Dec 19, 2011
I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.
Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).
The relevant section of code attached below:
Dim role_count as range
Dim role as string
If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value
View 4 Replies
View Related
Nov 2, 2009
I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:
1. The rows containing the matched search criteria in the first sheet are highlighted.
2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.
I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.
View 3 Replies
View Related
Jan 15, 2014
I have a data set in Excel that starts with row 13 and has 26 columns. The headers are in row 12.
A pop-up user form requires the user to select from a drop down menu i.e Top50, Top100, Top200...
Once this is selected, the script below applies a filter to the header row & filters Column O based on the Topxxx chosen in the user form.
The script then selects the visible cells in selected columns from the data set and copies these to another sheet.
The only thing that changes is the filter criteria i.e the user-defined choice of Topxxx from the user form.
Obviously I would like to try and avoid having to copy copious lines of coding for each filter criteria i.e Top50, Top100 etc etc.
Code:
If UserForm1.ComboBox2 = "Top50" Then
Sheets("MD14").Activate
A9 = ActiveSheet.Range("A9").Value 'Counta
ActiveSheet.Range("A12:Z12").Select
Selection.AutoFilter
[Code] ........
View 1 Replies
View Related
May 16, 2006
I'm actually trying to write a Macro that applies autofilters and the information just doesn't show! The autofilter is properly applied and everything, but the only visible row is the header. The code is the following.
mes = Month([M2])
filfecha = Range("M2").Text
Columns("E").AutoFit
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=filfecha
View 2 Replies
View Related
Nov 29, 2007
I'm trying to do a DSUM based on a unique reference number. As I have 500+ such numbers, I need to automate the criteria array based on the reference number.
This is as close as I've come:
DSUM('Income'!$A$1:$Q$37735,"US FREE AMT", LOOKUP(A2,Criteria!$A$1:$BX$2,OFFSET(Criteria!A1:BX2,-1,1,2,2)))
LOOKUP, of course, wants to return a value instead of a range, so this only works as far as getting me to the right cell on my criteria sheet.
How can I get a range 2 rows deep by 2 columns high from the results of a lookup/search?
View 4 Replies
View Related
Oct 23, 2008
Currently I’m using several formulas to acquire my result; I know it can be abbreviated to a more compact formula.
I have four Sheets. One summary sheet and SheetA, SheetB and SheetC containing Data.
All sheets are documented in an equal format, e.g.: in column A: Date, column B: Code (A, B, C or D) and Column C: values.
Criteria are located in the summary sheet e.g.: A2: 1-1-2008 A3: 2-1-2008 and B2: A, B3:B
The data sheets are listed in H1:H3.
The results should cover a sum of col C over the sheets by using the critiria listed in summary sheet.
The criteria range is variable; sometimes I only want to use one criteria and sometimes more.
View 9 Replies
View Related
May 13, 2008
I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields
A=City
B=State
C=Values
What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.
I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:
=ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0)
But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.
View 9 Replies
View Related
Jun 12, 2014
I make budgets that have line items that have standard price based on 2 factors: a description, and a number. I want to keep these items in 2 different cells. I also have different clients, and the costs are different. I could simplify so that I can use a VLOOKUP with HLOOKUP and separate the clients but I would prefer not to.
Screen shot 2014-06-12 at 12.36.47 PM.png
View 1 Replies
View Related
Oct 2, 2013
I've made a macro and spend some time making it work across different sizes of data. The last issue I'm having is after the macro sorted out some data, that it will then delete, it has to select the first row and shift select down before deleting it. However, depending on how the data is sorted, the first row could be 9, could be 10 and so on. See below for my code.
Code:
Sub Macro1()
Windows("statistik.xls").Activate
Range("A8").Select
[Code]....
If the first row in my data set is not one of the 3 criteria ("Personligt ejede virksomheder", "Privat", "Reklamebeskyttet"), then the first row # will be 10 instead of 9. If the first two rows are not one of the 3 criteria, then it would be row 11 and so forth. How do I make my macro take this into consideration?
View 4 Replies
View Related
Nov 25, 2013
I am trying to create a 2 variable data table. I have the variables in a different sheet from the datatable sheet, and I read here XL: Error Message: "Input Cell Reference Is Not Valid" that if I link these variables to the active sheet where the datatable is located it should work, but the table is showing the same result in all the fields.
When I put the put the variables value in the same sheet as the datatable and link to these values from the other sheet the table is working fine, so I know that my design is correct but it has to do with referencing or something else that I need to set but I am not sure what it is
View 1 Replies
View Related
Jul 8, 2006
=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)
I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?
View 9 Replies
View Related
Jul 22, 2008
How to assign a variable of type Long to the below code.
1) Dim searchField as LOng
ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200=""" & searchField & """,$C$2:C$200))"
2) ActiveSheet.Range("N2").FormulaArray = "=MAX(IF($A$2:$A$200= searchField,$C$2:C$200))"
1) code failed because I want the variable searchField to have a data type of LONG.
2) code failed because it seems like excel thought the search criteria is of same searchField.
How to convert date to general format and store it in a variable of data type Long. I.E if I convert 3/15/2008(data type of Date) to data type of General, it should be 39522. What is the VBA code for doing this conversion?
View 9 Replies
View Related
Dec 3, 2013
I have a workbook with several worksheets and several pivot tables. Based on the selection from a drop down box on the first tab, I want to automatically filter pivot tables on subsequent tabs. Is that possible?
View 2 Replies
View Related
Nov 29, 2013
I'm interested in creating a two variable data table with the two variables in the top two rows.
I'm aware that two variable data tables are usually done in an array with variables in the top row and leftmost column. However, as there are multiple outputs to the variations that I want to make it makes sense to have the variations in the two top rows.
View 8 Replies
View Related
Mar 13, 2012
I need to return a value dependent on a few criteria. Type will be selected via a drop down and then it must lookup the same type in the top row and then the value which will be between two values and then return the grade on the far left.
GradeG4M5M6TypeValueGradeA
160016501800M5850CB600700650780900950C7008007809109501100D8009009101040
10001250E90010001040117010501400F100011001170130011001550G>1100>1300>1150
I am using Excel 2010 on Win 7
View 6 Replies
View Related
Dec 11, 2007
I'm working on a project in Excel (for a restauraunt) and I basically need to make a formula to work out what an item is, using it's ID number and referencing the certain menu that it is in.
as you can see the table array is defined in cell E3, but i still get a #N/A result, and when I replace the "E3" in the formula with "Deserts" it produces a result.
View 9 Replies
View Related
Jul 27, 2006
I have a spreadsheet where there are multiple records for each person. Each subsequent record contains information about one year prior to the one above it. So the first records contains info for 2005, the next 2004 and so on. I have the following formula in a cell in sheet 1: VLOOKUP($A2,'Sheet1 (2)'!$A$1:'Sheet1 (2)'!$DD$8796,11,FALSE)
I want to copy the VLOOKUP formula to the cell next to my original cell and have it begin searching on the first row after the first record and continue for each cell to the right. For example, if the lookup in my first cell (say A1) find the result on row 20, I want the lookup formula in B1 to start with row 21. So the change on the formula above would have to change the $A$1 above to $a$row below the first row found. I have a formula which tells me about the first instance of the vlookup finding the first row with the unique identifier.
View 4 Replies
View Related
Jul 20, 2007
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.
My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.
10 0 1 2 3
30 0 3 6 9
60 0 6 12 18
90 0 9 18 27
120 0 12 24 36
Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.
View 4 Replies
View Related
Dec 28, 2007
I am producing graphs that compare last years figures (static) with this years figures ( Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good. The formula currently looks like this.
=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0)
where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name. The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax
View 2 Replies
View Related
Sep 19, 2009
Here’s an example of my data:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
4330DDDDDDD
5330EEEEEEEEE
6440FFFFFFFFF
7440GGGGGGG
8550HHHHHHH
I need your help to figure out how to merge the above data to look like this in new blank worksheet using a VBA macro:
A B
1110AAAAAAA
2220BBBBBBBB
3330CCCCCCCC
DDDDDDD
EEEEEEEE
4440FFFFFFFFF
GGGGGGG
5550HHHHHHH
View 10 Replies
View Related
Oct 7, 2009
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?
The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).
I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
View 6 Replies
View Related
Jul 28, 2014
I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
View 2 Replies
View Related
Aug 2, 2014
I have been loaning my brother money over the past 14 months. The loans have been in the form or $1000 per month plus random payments for one-off expenses like doctors fees. He's not paid anything back yet but we want to know what the total owed is for interest of 10% per annum.
I can easily create a table with payments I've made and the dates with a running total of how much I've paid but how to I create a running balance of what he owes over time based on adding in interest. This might end with a one-off payment in a couple of months, I'd like to calculate what is owed there as a minimum.
View 7 Replies
View Related
Nov 15, 2013
I am looking to setup a formula to calculate a Fee that is based on the number of users of a service in a month, with the rate varying by the number of users. The first million users in a month will bring in $0.40 per user, for users 1-4M it will bring in $0.30 per user, for users 4M-7M it will bring in .20 per user, and for users beyond 7 million it will be .10 per user. So for example, if a user count was 9 million in a month, the calculation would be ($0.40 x 1 million) + ($0.30 x 3 million) + ($0.20 x 3 million) + ($0.10 x 2 million). I know the answer to the problem is obviously $2,100,000 but I can't build the formula that solves that and can handle instances where the user count is capped in one of the individual brackets. (IE if there are 3.5M users)
I've attached an example spreadsheet : Variable Fee Schedule.xlsx‎
View 7 Replies
View Related
Aug 17, 2013
I have an engineering spec table. Down 12 columns I have height in metres of a post and across the rows I have square metres with 5, 10, 15, 20 and 25 being the sizes.
The engineering spec of the post type is in the table. There are some duplication of post types so post heights of 1m, 2.1m, 2.4m, 2.7m, 3m, 3.3m and 3.6m are the same, then they change. Its a similar thing in the other column headers for 10sq.m 15sq.m etc.
I need a formula so that if the post height of 3m was chosen and the sq.metres was 15 then it would tell me the post type from the table. Or if the post height was 4.8m and sq.metres was 25 it would tell me.
The post type is an engineering code of numbers and letters with no spaces.
View 5 Replies
View Related
