I am looking to set up an excel doc with 2 sheets.
In sheet 1, I am would like to be able to enter a series of words of interest/criteria to be met (in column D).
In sheet 2, I would like to paste in the text from an individual's CV. The text would be entered en masse, meaning that some cells would be empty and others would have whole sentences/paragraphs of text.
I would like a formula to enter in column A of sheet 1 which would search all of the cells in sheet 2 for the word/words in column D of sheet 1 and return either a positive or negative value.
I had tried the following:
=(COUNTIF(Sheet2!1:1048576,Sheet1!D2)>0)
This formula, however, is not capable of extracting the desired text from a string/sentence, and so only returns a positive result if the desired text is in a cell by itself.
I have the code below which does work and looks up a piece of text in A1 (in this case the word exhaust) within the contents of column B. If the text is found (either part or whole) it returns a Y and if not an N in column C.
It works fine but I can't work out how to get it to repeat should I then want to look at a value in A2, A3 etc without removing any Y had Y already been displayed due to a prior word in the list. Below are the results I'd want.
Sub FindSomeText() 'Application.ScreenUpdating = False Range("C1", Range("C65536").End(xlUp)).Clear keyword = Range("a1").Value Range("b1").Select Do Until IsEmpty(ActiveCell) If InStr(1, ActiveCell.Value, keyword) = 0 Then
I have a large file where I want to name about 30 ranges. Initially I had done this using the range addresses and it worked well, but I continue to modify the spreadsheet and add and delete rows, so I need to go back into the code and change the ranges manually. I know there has to be a better way.
What I wanted to try doing was finding some specific text. That text (in all the ranges) is actually one column to the left and 33 rows below where I want to start the named range. Additionally the named range will be a 10 x 33 range. For example, if the data starts in B1, the data to be named will be in B1:K33 and the search title will actually be in A34. Hopefully that's clear...
I was attempting to us the Find function with this code, but I can't seem to figure out how to tell it to look for the text (that would be in A34) and then name the range that would be offset by (-33,1) and then name the entire range.
Above is one of the formulaes that I am using and I tried editing, to no avail.
Anyway here it is, I need to search E4 - E30 for a particular name, lets say Pandy. Now if pandy is found, then a cell on another tab has to equal a cell which is 2 cells on the left. from which the cell which contains the name is located.
here is a psuedo version of it...
Search E4-E30 Find a cell which contains 'Pandy' If pandy is found, 2 cells to the left then a cell on another tab is equal to that.
The easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
1) I am trying to find a matching value in a table. I have a 5*252 table that starts in B2. So the table goes from B2 to F253. I am trying to match a value in column D (column 3 in the table and column 4 in the spreadsheet) with a value in Column F (column 5 in the table and column 6 in the spreadsheet). The original value is in cell 222 in the table and cell 224 in the spreadsheet.
I tried the LOOKUP, VLOOKUP and HLOOKUP functions, but they return nothing but N/A. I use a reference cell or just the value for the lookup value but it does not work. I do not know if I am using the wrong formulas or what, but I have tried various permutations. (question 3 in excel workbook )
2) I am trying to calculate the standard deviation of a column D (contains both numbers and text) divided by column B (first in table). Excel does not appear to have a custom division formula. It has a remainder and a quotient formula which does not apply here. The problem is that the STDEV function does not allow for a division sign (/) in the formula. the STDEV function does ignore texts.
Is there a way for me to divide a cell ( containing both letters and numbers) by another cell just containing numbers and then taking the standard deviation of those values all in one formula?
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
So this is something that I'm not sure is possible in Excel. I would like to use an "if,then" statement to see if a number is in multiple cells. I know this is usually very simple except there's a catch here. In each cell there's a number range using a "-". So in a single cell a range would be 301-305. I am open to having the range done a different way like 301,302...etc. I just decided this would be an easy way to look at it and was hoping to find a way to solve this problem with leaving in the dash. Whatever is practical is fine with me.
Using Example A in the attached file I want to use this statement, =IF(308 is in any of the ranges in A3:A6, TRUE, FALSE).
So, for me, the alternative I want to avoid is Example B where I would have to list every single number and then check the whole range. I would like to avoid this because these examples, as you can imagine, are on a much smaller scale then what I will actually be dealing with.
My excel file was imported from a 3rd party and has many blank rows across the entire sheet. I want to delete these rows if every cell across is blank. How is this accomplished?
I tried the formula "=isblank(C2:C18)" however I get a "False response on those rows that are completly blank.
I have the following formula that is supposed to grab a value from C2 and check for that value in a range of cells and if it matches it is supposed to display the corresponding value in another range of cells. What am I missing??
I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
I have an Excel sheet set up with 6 columns: Col A contains a user-entered date. Cols B-E each contain a drop-down that the user can set to 1 of 4 options. Col F (I want) to show a date based on Col A & the results of the drop-downs.
The drop-downs contain (in order of importance): "Immediate","Urgent","Routine" and "None". If any of them are set to "Immediate", the date in Col F should be Col A date+2 days, if any set to "Urgent" it should be Col A+14 days, any set to "Routine" it should be Col A+28 days and for None the field can remain empty. I can enter a formula in F2 that checks for B2's drop-down as follows:
I need to build a calculator that sums cells. I want to be able to manually plug in the start cell and end cell into different cells then have the calculator return me a number.
Start Cell D1
1
End Cell D3
2
3
Calculated Value =sum(D1:D3)
4
5
Now if I change the text in B2 from "D3" to "D5", the calculated value should change as well. Is there even a way to do this in excel?
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.
How to send a range of cells in a sms text message by using Visual Basic for Applications in Excel?
BTW: I really need this to work for my job! So any feedback would be greatly welcomed. Please read below to understand what I'm trying to do. I need to be able to send my guys their daily goals via sms text message:
The following works great for sending a range of cells in a email message: ....
In my excel sheet i have few results displayed in range (same row different column with few banks cells in between). No i wish to display the most repeated value / result from that range into another cell on a different sheet of the same workbook. Can somebody help please.
Please note that the data (that is displayed in the cell rage from where i wish to get the most repeated one) is a the result of a nested IF statement. 16 minutes ago
I am just learning VBA. I need to move to a cell location that is named in a range. As follows:
The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like:
Application.GoTo Range("Sheet2!A1")
I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.
trying to add cells together that contain time (hours and minutes). The cell containing the total formula shows the #VALUE! result when and only when one or more cells being added do not have a value in them.
The times have been entered as actual times of the day, formatted for AM/PM.
A simple adding formula works fine, but all cells must have a value in them to obtain a valid result.
I'm trying to create a shortcut that will allow me to add quotation marks to the begining and end of text in cells. I've tried the following, which works fine when only one cell is highlighted:
VB: Dim rng As Range Dim txt As String Set rng = Selection txt = """" rng = txt & rng.Value & txt
However as soon as I highlight more than one cell, it errors. How to make it work for a range of cells?
