i currently have the following code set up to look at one cell (named 'TypeSelect'). If that cell = "a" it unhides tab "TAB DETAIL A". If it doesnt = "a" the tab remains hidden. I now want this to search across a range of cells (B2:B7 or using named range 'Category_Select') for "a".
Ultimately the code is to look for any from a list of categories within a range and unhide the relevant tabs if the category is present.
I'm trying to do is search a range of cells for a range of values AND then see if it matches one other value in another range of cells. In this case,
=SUMPRODUCT((T3:T49="P6")+(T3:T49="P5")+(T3:T49="P4")+(T3:T49="P3"))*(U3:U49="w")
T3:T49 can equal P6 or P5 or P4 or P3 but the cells can only be counted if U3:U49 is 'w' as well
1. can i limit vlookup on sheet2 to search only a certain range of cells on a sheet1.
example, I want vlookup to look in sheet1, colum A - Y but only look between rows 1 and 100
2. If 1 can be done, and i happen to add a row in worksheet1 (making it 101 rows vice 100) will the vlookup code on sheet2 include that 101st row or do i have to redo the range manualy.
I want to search within cells A4:F25 for "B71 - Brown" and show the number that is in the cell below this text. If there is multiple cells with "B71 - Brown" I would like the cell to show the total.
So for example,If the formula was looking for "B71 - Brown" in the cell it would show 8136. (A13+A21+D25+E17). If i changed it to "Lavender" it would show 2380. (C5)
Also this data is linked from several other sheets and the values i search for will change and will need to update.
I try to search for only one word/phrase in range of cells. I would like formula to return the cell value that contains work/phrase I asked to look for.
For example: A1 contains "Apple", C1:3 contain "Cherry juice", "Apple pie", "Orange bonbon". I want D1 to check which cell from range C1:3 contains word/phrase from A1 and show it to me - "Apple pie".
if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?
So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.
Columns A - D will contain a string of more than one character so it needs to search the string.
e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213
I would like this to return true because the A cells do contain the values in the E cells.
I guess this is like looking for duplicates but with a search string twist!
Following problem:
I have a list of changes on our bankaccount.
Sometimes, in the description a payment we receive the name of the person who transferred the amount is mentionned.
It can be anywhere in the string, it can be just his/her first name, it can be his/her full name...
I also have a list of all our clients with first name in column A, second name in column B
Something like this:
string from bancaccount:
EUROPESE OVERSCHRIJVING VAN BE16 6712 5615 7974 BANKIER OPDRACHTGEVER: EURBBE99 VANDERSMISSEN WILLY VAN PLAKSTRAAT 176 9000 GENT 68/10762827 PENSIOEN REFERENTIE: OV-0000236-00240
client list
Francois D'hondt
Germaine Canipel
Willy Vandersmissen
Karel D'hondt
.
.
.
I'd like VBA to search in the clientlist for names that occur in the bankaccount string.
Sice ther might be a lot of Willy's in the client lsit I think it will be best to serch for the combination of both first and second name.
Once found the name the procedure should return the rownumer on which the client can be found in the client list.
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria.
How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.
I tried to find out how to post my table but the link in that thread lead me to a notice saying I wasn't allowed access to the page.)
Cells A2:G2 contain various alphanumeric strings.
If any cell contains "SD#" (# = a number which might include decimal places) then I want to extract the number.
If more than one cell contains "SD#", I want to add the numbers together. The total goes in cell C6 and it needs to change if the contents of A2:G2 change. E.g. if A2 shows SD4.75 and B2 shows SD8, the total in C6 would be 12.75
So I'm looking for a formula for C6....
In my current sheet, I have a button which pastes the contents of the user's clipboard into cell A20. The data that is being pasted is a simple, single-column range of data that may or my not contain blank cells. When this button is clicked, the user's clipboard should already have data.
Some of these pasted ranges may go from A20:A40, A20:A60, or even A20:A73. The point is, the length of the pasted information is variable.
I am looking for VBA code which can look at the recently pasted range in A20:AX, find blank cells, and remove them. Here is my current code (very simple), which is only the paste function:
VB:
Sub admin_btnPASTE()
' paste_align Macro
' pastes data
On Error Goto Whoa
[Code]....
I created a user form that has two textboxes on it. One is to enter a person's name that gets put into column A, starting at row 4, and the other is to enter a number into column B, starting at row 4.
What I would like to do is for the form to search the names that have been entered in column A for duplicates in the same range. If the name is not there already, then I would like it to operate as normal. If it already exists, then I would like the number in the text box to go into column C in the same row as the name.
Here is the code I have already on the Submit button:
Private Sub SubmitButton_click()
Dim emptyRow As Long
Sheet1.Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 4
Cells(emptyRow, 1).Value = Guestname.Value
Cells(emptyRow, 2).Value = Roomnum.Value
Unload Name_usrfrm
End Sub
Is this possible? I have to make it more complicated, but once I get the basics, I might be able to figure it out myself.
I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
Sheet1
cup Y
bowl Y
spoon
fork
knife Y
Sheet2
cup
bowl
knife
..not..
cup
bowl
knife
I have a range of cells each containing different values. Each of these different cells has a related date contained in the row underneath.
I want to be able to search the range of cells and find it in the contents of a cell and return the corresponding date from the row below.
Excel 2010
A
B
C
D
E
F
G
[Code]...
Row A4:O4 is the range of cells to be tested. Row A5:O5 are the corresponding dates. Cell B1 is the value I want to search for and B2 is where the date I would like to appear.
In the example shown I'd like 01-Sep to appear in B2.
I'm using Excel2010 and Windows 7.
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 Replies View RelatedI' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
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.
I need to be able to query a large date range by a small beginning and end date range and return a count when the value is = each search criteria. i.e. - Search one year of dates from a table by Beg: 7/23/2012 to End: 10/21/2012 and return a count. The beginning and end dates are dynamic and I will need to reference the cells, i.e. B102 "Beg" B102 "End" and not a static date.
View 7 Replies View RelatedI have the following ...
View 7 Replies View RelatedThe intention of the below code is to write out the line is there is not a match in both ranges.
Instead of just D2 being searched, i need it to look into range E2 also if not found in D2.
At the moment if it is not found in D2 it is writing the line, though the match actually appears in E2 range in this instance, thus should not be writing the line.
FYI:
- Of course if there isnt a match in D2 range though there is in E3 then no line should be written as there is a match.
- if there are no matches in the 2 ranges then only 1 line should be written in the same fashion as currently coded.
What's the best way to search every third row in a range ("rDRange") which is one column wide to see if there is a match to "sDept".
View 9 Replies View RelatedI have a userform with text boxes. When I click on the GO button on the user form I want it to search from the TextBox to the range of rows on sheet2 from start to end and if duplicate entry is found it should display a message box.
For E.g. After pressing GO button Textbox entry should search Sheet2 (where all the data is entered) from the range starting from B6 to last entry of cell and if duplicate entry is found a message box appears. This entry keeps on getting updated and so it may increase or decrease, and therefore VBA should be such that it searches to the last entry.
I want to search for a value, in a specific range (in this case, in a row). If that value exists, l want the formula to return that value, otherwise l want it to return "FALSE" or whatever.
The formula must exist in one discrete cell only, i.e. the output will be contained in only one cell.I don't want to match the occurrence of that value with another row's cell. I don't want to search for multiple values (although if you have a way to do that, l'll be interested to know!), l don't want to count the number of times the value occurs.All l want is to return that value if it exists in the specified range of cells, and if it doesn't, l want to say "FALSE" or something.
I have a monthly time sheet. The times are entered in cells C8:F68.
When some one has a annual leave day or a bank holiday they enter A/L or B/H.
I have a summary sheet and have placed a cmd button on it to hopefully search the sheets that are months or the year.
I know how to search the sheets and increment through them and not the 'Summary' sheet.
What I need to establish is how to place in my code the actual search criteria and also limit the range to C8:F68
On top of this I need to total each occurance of A/L and B/H and display them in two cells.
I have tried to do a search of the forum but with no luck.
Like I say I have managed to understand how not to select the 'Summary' sheet when incrementing through the sheets but its just the search for the A/L and adding them up!
I'm currently designing a new spreadsheet and I can't change the formula I'm using below to search more than one cell or a range. Is either choices a option?
I'm using =IF(ISTEXT(E135),G135) Can I add cell E139 (and how many additional cells could be added) or can I set it up to search E135:E144.
I want to search for contents of A1 in range of A1:B3 and if it is in the range return Y or N if not there.
View 9 Replies View RelatedI need to look through a range for a specific value. When I find it if the nearby time column matches a specific time I want to retrieve the lookup value. Because I am not very macro savvy, and the spreadsheet has to run on both mac and pc I do not want to use a macro. Can I do this with an IF((AND combination or is there a better way? If there is a way to look through a range with IF, how would I structure the criteria to look through an entire column for the value? Lookup won't work because the value can occur several times through out the range, but only once with a specific time value.
View 5 Replies View RelatedIs 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
If a column contains it contains apple and ipod then display manufacturer as Apple Corp in c2. How can i do this?
View 9 Replies View RelatedExample:
0000-0999 Cell a1
1001-1999 Cell a2
2000-2999 Cell a3
3000-3999 Cell a4
These are a ranges of values in a spreadsheet. I would like to be able to do a search or lookup for a number that I designate that falls within one of these ranges and it will direct me to the cell that my value falls within.
For example if I do a search for 1550 it would take me to cell a2 with a range of 1001-1999.
Not sure if this is too complicated for a formula and might need to use VBA.