More Than 1 Cell As Search Range: Result Is Wrong

Apr 6, 2009

i've been using an IF function in order to display a certain value when one or more criteria are met in one cell in one specific worksheet. here's how it looks like:

=IF(OR(ISNUMBER(SEARCH("value1",Worksheet1!A1)),ISNUMBER(SEARCH("Value2",Worksheet1!A1))),"yes","no" )

with different values and names.. but it's just to give you the idea of what kind of function it is. this one works perfectly, but i also needed another one in which the logical test range is no longer only one cell, but more, thus a selection. i wrote the same formula, but instead of the "A1" i wrote for example "A1:A20" which i thought it would work.. but it doesn't. even more surprising, when i open the insert function (the helping wizard that pops up when i click the "fx" button on the left of the function string) and i put in the logical test, at the bottom of the window it says the correct result (ie: yes). but on the actual worksheet, the cell displays the wrong result (no). i know there are no errors in the formula, so i really don't know how to solve the problem.

View 10 Replies


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

Result In The Formula Editor Box Is Wrong

Nov 25, 2007

I can't seem to tell if my formula isn't correct in concept, or if this is a known problem with Excel 2000?

I have written a short formula that is pretty straightforward, but is giving a different result in Excel's "Formula Editor" box (when you hit the = button) than is showing in the cell itself. The Formula Editor's result is showing as "TRUE" which is what I believe is the correct value, but the cell itself is showing the "FALSE" result of a different incorrect value.

View 9 Replies View Related

Wrong Result Adding Values Of Textboxes In Userform?

Jun 27, 2014

I have 4 text boxes in a user form where user type numbers. At the end i have another text box(yellow one) that adds these numbers using this code.


In another text box(grey one) user types another number. Then using this code

[Code] .....

I try to add the values of the two previous text boxes. I get wrong result when i use decimals numbers and i can not understand what is wrong.

Attached File : add text boxes.xlsm

View 7 Replies View Related

Macro Giving Wrong Result For Counting Rows

Mar 10, 2014

The below code is giving wrong error.

[Code] ........

sheet1 has the following data
sheet1 has the following data

empid name loc
1 aaaa ddd
2 bbbb ggg

4 cccc dddd

the 3rd row is empty in sheet1 but still it is showing number of rows as 3.I need macro it will not count if entire row is empty in that sheet.

View 14 Replies View Related

Replacing Contents Of A Cell With Another Based On Search Result?

Feb 24, 2014

My current problem is that I need to search two tabs of data and use the results to modify the contents of one cell in the first tab.

I'll try to be as clear as possible:

tab1 has relevant cells "a" and "b", same row

tab2 has 3 relevant cells "c", "d", and "e", all on the same row

All cells are text values - some digits, but should be treated as a regular String.

The search needs to look at cells "a" and "b", find their match in "c" and "d", then replace the original value of "b" with the value in "e" after a match has been found.

View 1 Replies View Related

Changing Search Result Cell Highlight Color?

May 10, 2013

When I do a SEARCH operation to find the number 10 among hundreds of cells containing data, I'm often finding the way Excel highlights the cell that meets the search result is not easy enough to see.

Is there anyway I could setup Excel so that the resulting cell that meets the search result is highlighted in a RED border or RED Fill? I would like the cell that meets the search result to scream at me with a color highlight that is not easy to miss.

View 3 Replies View Related

Link Search Box With VLookup To Provide Search Result

Jul 15, 2012

What i have so far is:
-3 Sheets (Sheet1 - Search, Sheet2 - Data, Sheet3 - Result)
-Sheet1 has a userform ready to link up - this opens up when workbook and worksheet is activated.
-Sheet2 has some example data to search - the search is for column 1 (see attachment)
-Sheet3 has a template ready for the data to be pasted to.

I would like the search result is column 1 to copy the data from columns 1 to 7 to sheet3.

I want the template/layout to remain after the pasting of results. (everything protected from editing, if possible).

Once finished (ie go back to sheet1, i want the data from sheet3 to be cleared.)

View 9 Replies View Related

Named Range Returns Wrong Range

May 16, 2014

OK, I have a huge SS with lots of named ranges. Many of which are dynamically assigned lists.

All of the existing ones work fine, but when try to add a new named range, it returns the cells from a previously named range. (Always the same old one.)


A range (one of many) is named "Shift_List" and is defined using
=OFFSET(info!$C$3, 0, 0, COUNTA(info!$C$3:$C$2000),1)

New range is created named "PN_List" and is defines using
=OFFSET(info!AA$3, 0, 0, COUNTA(info!AA$3:AA$2000),1)

When I create a Data Validation List or otherwise use "Shift_List" as the source it works fine.

However if I do the same thing and refer to "PN_List", it returns the items from "Shift_List"

Any new named range returns the Shift_List cells, although older ones still work correctly.

View 8 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

Wrong Range Selected While Sending Email

Feb 25, 2010

I am wrote below code to send email using VBA:

Sub SendEmail()
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim Range1 As Range, Range2 As Range
alertsSheet = ActiveWorkbook.Name

Issue with this code is it is selecting wrong source range while generating email.

MsgBox Range1.Address is giving results as "$A$1:$B$1,$BX$1:$CA$1" and
MsgBox Range2.Address is giving results as "$A$32:$B$38,$BX$32:$CA$38" but
MsgBox rngeSend.Address is giving results as "$A$1:$B$38"

And the email generated as output is having only the data from range "$A$1:$B$38". Is there anything wrong with the way I am specifying the source range ??

I need that the code should replace strHTMLBody with the data in Range1 and Range2. I checked the tempsht.htm file and found that this file also have the same wrong data i.e. data from range "$A$1:$B$38"

Needless to specify that I am working with data having few hidden columns and need to consider only visible data.

View 9 Replies View Related

Control F Search On Value That Does Not Come Back With Result?

May 31, 2013

I am doing a control F search on a value that does not come back with a result. But the value exists in the sheet. I have multiple columns and rows (over 22,000 rows). Are there limits to how much you can search using control F?

View 2 Replies View Related

Copy Rows From Search Result

Apr 14, 2008

i have been using a code that "Joe Was" wrote on this forum the code search all cells for a word i type and then copy the rows where the word was found into a new sheet. my problem is that the code overwrites the rows, so only one line is shown even when the result box say it finds 10 results.

Public Sub FindText()
Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter text to find")
If myText = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets......

foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)

Found.EntireRow.Copy _
Destination:=Worksheets("Søkeside").Range("A2").End(xlUp).Offset(1, 0)

If Len(AddressStr) Then
MsgBox "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If
End Sub

View 9 Replies View Related

Search Duplicate And Result In Next Worksheet

Jan 10, 2009

I need to search duplicates in datas and the result will be in the next worksheet. Result will shows only one entry without anymore duplicate.

View 9 Replies View Related

Move Cursor To Search Result

Mar 4, 2009

I am trying to position the cursor in a spreadsheet column based on the data within the column but am struggling with the software to do it.

What I have is:
(a) A column (A:A) containing a mix of up to 500 numbers (1 to 3 digit) and a few text entries (1 to 6 char). These form the basis of a movement log where say 100 different drivers (each with unique identity) are logged in and out, so there will be duplicates.
(b) A number of other columns logging task/times etc but the one I am interested in (B:B), contains a tick (martlet font "a") to denote the driver has departed.

What I want to do when a driver departs, is have a quick way of finding (from the top down) the row containing the drivers number (A:A) where the driver logged in, but has not yet departed (so I can tick him out!).

So from the top: find the first row (in A:A) matching a specific number/string, where B:B is also blank and position the cursor at B"x").

I have imagined a "button" with data entry at the top of the sheet being the easiest physical way of doing it but am open to offers.

Can the Cursor (or cell?) be highlighted as well, because this is not always easy to see in bright light.

View 9 Replies View Related

Search For Multipl Values - Copy Result

Aug 7, 2009

I use this code to look for values like V1/V2/V3/V01 in my column. If the cell contains the value, nothing happens, if not, the whole row is deleted.

View 2 Replies View Related

Find Column Character Search Result

Jun 9, 2009

I'd like to search for text and get the column character where the cell with the found text. The combination the two 'demands'/formulae into one are giving me problems. And the fact that the column character can exist out of two characters I cannot solve.

I can get the column number with HLookup right? and it gives me the column number, not the character. I can solve this partially (and ugly even more) with =Char(((Hlookup etc)+64 (letter before A))).

View 5 Replies View Related

Search Result List (grep-like Functionality)

Aug 31, 2006

how to create a simple form that would search a sheet for a word and display the list of resulting rows - something similar to Adobe Reader 7 Search functionality ?

If I was having administrator privileges on this PC, I would simply install Cygnus and use its "grep" command on a CSV file but without that possibility, it's rather cumbersome to search an Excel sheet when you need to do it repetitively, and I need to locate a specific row each time without knowing the exact content of the cell (only searching for one or another word I know the cell contains).

View 9 Replies View Related

Enter Text To Already Populated Search Result

Jul 26, 2006

I have a userform that with a combobox called "complaintdis". This combobox displayes all the records in column A of worksheet "ComplaintData". When user selects any record from that dropdown list, it populates the other related fields on labels on the same userform. This part is working fine.

when user is looking at any displayed record on the userform (displayed via labels), I want user to be able to add comment that will get added to the column J of worksheet "ComplaintData".

I can workout a way to send this input data from textbox (called "txtcloser") to the column J, but I am struggling to figure out a way to link that comment to the specific record.

As there are number of entries in the worksheet "ComplaintData" , user should be able to select any record (via combobox) in no specific order and leave a comment.

View 9 Replies View Related

Search Column Add Result Rows To New Sheet

Jun 4, 2008

way I can search a particular column, and when results are found, copy the rows where the results are to a new worksheet? And perhaps reuse the same worksheet (so clear the sheet on every execution)

I have tried auto and advanced filter but its not really working to my liking....

View 6 Replies View Related

Excel 2003 :: Cell Result Based On Range Of Filled / Empty Cells

Apr 3, 2014

I'm stuck using Excel 2003 to auto-populate a cell.

I have a range of dates in five consecutive columns called:

Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5

I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.

Each phase is consecutive to the next so will always be filled in from 1 to 5.

I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.

I've tried nesting some ISBLANK functions without any luck.

View 4 Replies View Related

VLOOKUP Formula Dragdown Copies Previous Cell Result Instead Of Unique Result

Jun 10, 2014

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.

View 3 Replies View Related

Search For Multiple Items And Display Result Of Word

Jun 16, 2014

I Want to fetch a data as below.

M&C_ES_F_55+_Phase1 <55>
M&C_BR_F_18-34_Phase1 <18-34>
M&C_ES_F_35-54_Phase1 <35-54>

Likewise there are many line items, wherein i want the Age should be searched in ITEM column and should be displayed in the RESULT column.

* I don't want to Use text to column method, is it possible to use IF condition along with Search formula.

View 1 Replies View Related

Filter Using Userform And Display Whole Row Of Search Result On Textboxes

Apr 17, 2014

Currently I am working on a data entry form for CRM database using Excel. Now i am stuck at filtering and displaying the data from the spread sheet to the user form.

There are three text boxes to key in the filter criteria and a button which will filter the data from the spreadsheet based on the criteria in the three text boxes. Then the whole row where the filtered data resides will be displayed on text boxes on the user form.

View 5 Replies View Related

Search In Mulitple Columns And Return Corresponding Column/Row Result

Apr 30, 2008

Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.

View 10 Replies View Related

Search For Cell Value Through A Range

Mar 2, 2007

Is there a formula that will look through a certain range of cells for a specific value which is held in another cell.
So if P21 holds the number 21, search through P17:S19.
if the number (in this example 21) is found in that range place W in P23 if it isn't then L in P23

View 9 Replies View Related

For Each Cell In Range Result In Another Other Cell

Aug 15, 2014

I wanna make a worksheet Event, so everytime my Active Cell inside of this Range("A1:D4") then In Range("E1:E4") will show the number of Row and Column of Active Cell.

View 5 Replies View Related

Search Multiple Sheets Within Report And Then Find Average Of Result?

Jun 6, 2014


I have changed the attachment to use CSV's

I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.

What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.

Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.

Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1

I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)

View 5 Replies View Related

Convert Text Into Multiple Rows And Search Result In Next Sheet?

Apr 11, 2014

vba script to convert the text in a cell(split considering "Space" as delimiter) into multiple Rows and Single column & the resultant row's(A1:A7000) values need to be searched in Column(B) of another sheet,if the search result is false then the value in the row need to be highlighted in red.

I am able to split the values in one column into mutiple rows,but need to have all the resultant values in a single column.

I am able to split it as below:

Column A
Column B
Column C


for second situation - search I could search based on the values by using Vlookup() function,but unable to highlight when search result is false.


if the search string "ACB" in Sheet1 is not available in the sheet2, then value "ACB" should be changed into red.

View 1 Replies View Related

Excel 2007 :: Search And Return Result With 2 Criteria In Different Sheet?

Nov 7, 2013

I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?

=IF(SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B))=0;"not found";INDEX('sheet 2'!D:D;SUMPRODUCT(--(C5='sheet 2'!B:B);--(C13='sheet 2'!C:C);ROW(B:B));1))

(I'm working with the dutch excel version so it might be that ";" must be ",")

View 6 Replies View Related

Copyrights 2005-15, All rights reserved