Lookup Nth Occurence In Range & Limit Rows To Search Based On Cell Value

Jan 14, 2009

I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.

I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.

I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.

If value I5 says Period_1 it will only look in rows 12:46.

If value I5 says Period_2 it will only look in rows 48:82 etc.

I believe I need to modify the xl look part of the code with if statements.

Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.

Function Lookup_Occurence(To_find, Table_array As Range, _
Look_in_col As Long, Offset_col, Occurrence As Long, _
Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)

Dim lLoop As Long
Dim rFound As Range
Dim xlLook As XlLookAt
Dim lOcCheck As Long

View 5 Replies


ADVERTISEMENT

Limit The Search Range In FIND() Function

Dec 2, 2009

I am currently using the simple code block below to jump to a cell on another sheet based on a user-inputted value to a cell on the current sheet. Although jump is too strong a word at the moment...walk slowly might be more appropriate.

The problem is that the sheet that contains the target cell hosts a very sizable used range and the FIND function can sometimes take almost a minute before finding and focussing on the required cell.

While I do not believe that a search range below that of sheet level for the FIND() function is possible, the cell that I am looking to jump to resides in a one-column, sorted, dynamic named range (=Bookings!JobID) on the target sheet, so I am sure that it must be possible to find and go the target cell in a much quicker timeframe than that being delivered at the moment. Just can't find something suitable at the moment.

View 4 Replies View Related

Occurence /count Of A Cell Value In A Column/range

Nov 14, 2008

I need to find to the occurrence/count of a cell value in a column/range.

Like say column A has following values

A12
A12
A12
A12
A13
A13
A14
A14 and so on ..

I need to find the no. of times or count A12 is there in the column A.

I have tried using .count function in the range A but this instead gives the number of cells in complete range A

View 3 Replies View Related

Finding 2nd Occurence And Lookup Value

Jul 27, 2006

How can I find the 2nd occurence of a value in a list and lookup a value in the same row?

My data looks similar to this:
Name.............Order No
Alan...............1234
Bob................4567
Steve.............7890
Alan...............6543
Steve.............0985

etc.
How can I lookup Alan's 2nd Order No.?

View 4 Replies View Related

Lookup Info In Two Columns - Pick Out Last Occurence In A Third

Jan 6, 2009

I have a list of data which is not in any particular order....

View 9 Replies View Related

Hide Rows Based On CheckBox Event - Reaching Maximum Limit

Apr 19, 2012

I have a sheet with a hierarchy of operating units in a column, with DirectX checkboxes next to each (used a VBA script to auto-create the checkboxes ).

The checkboxes go from D5:D147, so 142 checkboxes. They are named checkbox_D5, checkbox_D6 all the way to 147, so checkbox_[Column]&[Row].

Based on a True/False value in another column to the right (BA or column#53), I am hiding specific rows. Just to give a sense, if a checkbox for a Level 2 hierarchy is clicked, I have some formulas to determine which Level 3 rows will be shown by putting a TRUE in column BA. For sake a speed, I created these to only run for 30 rows per click (that's the most that would ever change on a checkbox click).

I did not know how to do it any cleaner, so I created 142 of these:

Code:
Private Sub CheckBox_D5_Change()
Dim i As Long
For i = 5 To i + 30

[Code]....

It works OK up to checkbox_D23 or 24, but excel seems to reduce the number of rows it goes through for each checkbox below that, with checkbox_D31 down doing nothing. I read somewhere about a 32 form item limit, wonder if I'm hitting that.. Cleaner way to do this vs. 142 instances of checkbox_XX_change() events?

View 9 Replies View Related

Lookup Array/Range Based On Cell Value

Jan 4, 2009

I need the array portion of a lookup formula to change based on a cell value.

On a worksheet(named "groupings")that groups students according to ability level, I have data validation lists where teachers select the criteria for the group. In cell I5 they choose the period, and in cell L5 they select which assessment to look at.

Cell B8 will either say high low or medium. Then in cell C8 I have this formula: Lookup_Occurence($B$8, ??????, 1,1,2)

Again the array will depend on what is entered in cell I5 and cell L5.

I have attached one of the worksheets the lookup_occurence will be referencing. In this exit card worksheet if cell B10 (the name of the assessment) matches what the teacher chose in cell L5 on the groupings worksheet, then the array formula will be 'Exit Card'!Card1. If cell G10 matches what the teacher chose in cell L5, then the array would be 'Exit Card'!Card2. Where A9:C339 is named Card1 and cell F9:H339 is card2 etc.....

However, it gets even more complicated then that because within Card1 there are 8 periods, and if a teacher chose period 1 in cell I8 on the groupings sheet, then I only need the array to look at the period one data in the card1 array.

Essentially what I am trying to accomplish is create a page where teachers can group students by ability level according to any assessment they choose. On the exit card page the formulas currently equaling # Div/0 will say either high, med, or low depending on how a student performs on that assessment.

View 9 Replies View Related

LOOKUP Range Based On Contents Of A Cell

Aug 24, 2006

I want to be able to send out one ďmasterĒ workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range.

The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I donít have to create one for each district office. Maybe Iím thinking in a wrong direction altogether.

View 3 Replies View Related

Transpose Rows At Every Occurence Of A Value In Column

Apr 8, 2008

Transpose rows at every occurence of a value in column ...

View 9 Replies View Related

Percentage Occurence Of Specific Word In Range

Apr 29, 2008

I want to count if cells in a specified range =yes then divide the range to get a total percentage of yes cells. I have a data validation list set up with the values: yes, no, n/a. I don't want the total percentage to change when using n/a.

View 9 Replies View Related

Lookup Part Of Text String In Userform ComboBox And Search For Value In Sheet Range

Feb 12, 2014

I have a worksheet with following values:

A
B
C
1
Shorts
75

[Code]...

Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".

What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.

Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.

I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.

View 1 Replies View Related

Setting Values In A Variable Range Occurence Query

Jul 18, 2006

I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;

1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for

Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................

View 2 Replies View Related

Nested IF Limit For ISERROR And SEARCH

Jun 13, 2006

I am trying to write a formula requiring more than 7 nested conditions for ISERROR and SEARCH. Excel throws up and error if I exceed this limit ...

View 7 Replies View Related

Hide Range Of Rows Based On Cell Values

Aug 13, 2014

I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.

[Code] ....

View 6 Replies View Related

Calculate X Number Of Rows In Range Based On Cell Value

Oct 2, 2006

I can't seem to make SumIF or Vlookup do what I want here.

I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.

If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)

Period Target Actual %Target
1 74 68 91.9%
2 81 71 87.7%
3 76 87 114.5%
4 76 68 89.5%
5 71 89 125.4%
6 69 81 117.4%

View 3 Replies View Related

Copy Rows To Sheet Based On Cell Value In Range

Aug 16, 2007

a macro button on my excel sheet that should do the following.

i have a range (Ex: E3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E".

after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)

View 9 Replies View Related

Search Formula - Lookup Person Name In Given Worksheet In Specified Cell?

Jan 15, 2014

How to create a search formula that will look up a person's name in a given worksheet in a specified cell?

View 8 Replies View Related

VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

View 4 Replies View Related

Hide Rows Based On Text Search In Three Columns?

Mar 22, 2013

I am working on a time management sheet for my company. I need to be able to click a button and have the file search 3 columns for a persons initials and then hide all the rows where the initials are not in at least one of the three columns. I seem to have no problem getting it to work for one column at a time, but as soon as I try to search more than one it all falls apart.

I will also need to create an unhide all button to reset the sheet after the macro has been run.

I have attached a sample of the sheet below. Each project needs to have 3 rows to show the schedule and budget broken down by each team member.

Project Number
Address
Service
PM
SS

[Code]....

View 5 Replies View Related

Search Range And Copy Result Rows To New Sheet

Jun 3, 2008

I have a table in excel with a group of headings. (Serial Number, Model, Description, Repsonsible and a few others) What I would like to do is be able to search every cell within the range of that table. When the user clicks search an inputbox is displayed and you can enter any search term you like. if there is a match within the cell range, i want the entire row (and the heading row always at the top) printed into a new sheet to display results. There may be a match in multiple rows, and id like evrery row displayed.

View 5 Replies View Related

Search Column Bottom Up Then Delete Rows Based On Value Found

Jul 7, 2009

I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.

I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.

For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.

View 5 Replies View Related

Sum A Range Based On A Starting Lookup Value

Mar 2, 2009

I'm trying to sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.

So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).

View 4 Replies View Related

Lookup A Value Based On An Array Spanning Multiple Rows And Columns

Nov 24, 2008

I'm having difficulty finding a way to ask excel to lookup a value based on an array spanning multiple rows and columns. There's a mock example in the attached.

View 2 Replies View Related

Return A Lookup Value Based On A Range Of Options?

Jul 13, 2013

I have used the below formula which works in a standard cell, however I would like to have this is a VBA code using the Target.Offset option, however I can not get this to work. There may also be a better way of doing this.

The code is looking to see if the 1st cell (A118) is empty or not, if it is empty do nothing otherwise it then looks up the value in the 2nd cell (B118) and assigs the appropriate name from the range values.

VB:
=If(A118="","",LOOKUP(B118,{0,0;0,"";1,"Main Bank";71,"PFS";80,"Main Bank";106,"Dry Clean / Photo";112,"SCO";141,"Cafe";168,""}))

View 5 Replies View Related

Lookup Based On Numeric Range Scope

Nov 19, 2009

I have a spreadsheet and Iím trying to create several different staffing scenarios
Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. Iím trying to have excel give me the staffing value if the current number in cell B5 is a certain value Ė so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?

View 3 Replies View Related

Formula To Search Text From Another Sheet And Add Sum Based On Date Range

Feb 9, 2014

I'm trying to create a formula that will search for a specific text in "sheet1" within a date range and then SUM the total on "sheet2". I've manage to use the SUMPRODUCT (below) formula but want to expand on this so the SUM can be shown on another sheet(summary page). =SUMPRODUCT((ISNUMBER(FIND({"nmlclex06"},A1:A10)))*(B1:B10)) In this example I search for the text "nmlclex06" and SUM the size, but don't have a date range and don't know how to SUM the data on another sheet.

Columns: Name, Size, Date
nmlclex06114/12/2013
nmlclap121012/12/2013
nmlclex0651/10/2013
nmlclex0619/10/2013
nmlclex0611/01/2014
nmlclap12124/10/2013
nmlclex06113/11/2013
nmlclap12118/12/2013
nmlclap1219/12/2013
nmlclap12130/12/2013

View 11 Replies View Related

Search Range In One Sheet - If Any Cell In Range Is Greater Then 0 Copy That Row

Sep 24, 2013

Basically I have three sheets. MAIN, Sheet 1 and Sheet 2

Sheet 1 and 2 are in the same format

A3 down is a list of country names and then B3:I71 contains the data im interested in.

I've been trying to create a function that looks at B3:i71 to see if any cell in that range contains a value greater then $0.00. If it does then the row that contains the cell with a value greater then $0.00 (between col A to K) should be copied to sheet MAIN from cells B3 down. This should ultimatley produce a list of data for any row containing a value greater then $0.00. This process should then be repeated on Sheet 2 and should join the list below sheet 1.

View 3 Replies View Related

Selecting Entire Date Range To Be Lookup Based On Start And End Dates

Mar 6, 2013

I have an excel sheet where I would like a user to enter a start date and end date(say Feb 1, 2013 to Feb 28,2013). And these dates will be able to change to whatever month the user wishes to look up in a specific date range(column A has 365+ dates). This post is closely related to [URL]... but I cannot figure out how to get it to work from my case.

So working off the same worksheet, I have the beginning date in AC35 and the end date in AC36 in which the user enters. I would like the dates that fall in this entire month, including the beginning and ending dates to be referenced when looking for the specific day that contains a value I have in S35.

In other words, I have data in columns A through N. The dates are in column A and S35 is a value obtained from using Max(L185:L526) where L185:L185 is manually selected each time I want a different month. I don't want this to be a manual process of scrolling down the spreadsheet to get the next month.

View 12 Replies View Related

Limit Data To 100 Rows

May 14, 2009

I have a user form that writes data from textboxes 1, 2 and 3 into the next empty row in a speadsheet but what i want to do is write the data into rows 1 to 100 then start again at row 1 overwriting the data. The reason is that i have the rows linked to a graph and want to continually show the last 100 results and never have to manually delete old data.

View 9 Replies View Related

Fill Down Limit For Number Of Rows

Sep 9, 2008

I have a macro that selects a range of about 20 columns and fills down. It works fine on my smaller set of data, but when I use one that has about 40,000 rows I get the error:

FillDown Method of Range Class Failed

It fills down about 7,000 rows, then fillsdown the first cell of the next row, but leaves the others blank. The entire range (including the nonfilled cells) is still selected.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved