# IF Function (few Words Shown Based On Figures In Specific Cells)

May 17, 2009

I want to have a few words shown based on figures in specific cells. I not quite sure how to explain, but i will try my best:

A1 = 5
A2 = 0
A3 = 7

The function i am using looks something like this:-

=IF(A1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")

However, by using this function, if the cell is empty it still shows the first wording when i need it to show lets say between 1 & 6 shows the first wording and more than 6 shows the second wording! If you know what i mean... I know what i want to do but cant really explain it very well.

Maybe it should look something like this but it does not work the way i am doing it.

=IF(E156>1<6,"Do I Have Sickness Self Cert?","Do I Have Doctor's Paper?")

ADVERTISEMENT

## First IF To Be Ignored If Specific Column Contains Any Of The Words Shown

Jul 21, 2009

=IF((P3<\$Q\$1),E3-S3,IF((G3="B"),"Not Changed",IF((G3="MT"),"Not Changed","Not Changed")))

Above is the formula I'm trying to get to work but it's not-a-happenin. It seems like the first IF overides all of the others. Basically I want the first IF to be ignored if column G contains any of the words shown. So basically what I think is happening is that P3 is less than Q1 so it's ignoring the rest of the IFs.
Originally my formula worked fine as:

=IF(P4<\$Q\$1,E4-S4,"Not Changed")

But I realized that i needed a certain category of product ignored. Products are labeled in column G.

## Locking Specific Cells That Are Hidden / Shown Based On VBA?

Jan 13, 2014

I am having trouble with my VBA code, it hides groups of rows (projects) based on the contents of corresponding cells above. these have either 'yes' or 'no' in them (indicating if the person is active in that project). The Yes/No is formula based from another sheet.

what I am trying to do, is have particular cells, withing the projects rows, locked (as they are also populated by formulas) but have other cells open for editing.

when I try and do this i get an error with my VBA, i am assuming that as cells are locked, the VBA is prohibited form hiding inactive projects/rows. i tried an addition at the bottom but this hasn't worked

Code:
Sub Worksheet_Calculate()
Dim i As Long, StartRow As Long, EndRow As Long
StartRow = 13

[Code]....

## HLookup For Words Not Figures

Jul 15, 2012

I am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?

## Macro That Locks Down Cells Based On A What Is Shown In Another Cell

Jan 4, 2008

I have a workbook with several sheets, but just on the sheet called “sales” I need a macro to do the following:

If F4 = CST
Then Lock down H4-S4 and AM4-AZ4

But if F4 is changed to something else I need H4-S4 and AM4-AZ4 to be unlocked

Is a password necessary for this? It is ou

I just want it to lock and unlock, not protect and unprotect.

## Conditional Formatting Based On Difference Value Between Figures In Two Cells?

Apr 10, 2014

I would like to use conditional formatting to mark cells with arrows depending on the difference between the values in those cells and figures from different cells. Namely, I have percentage values in column A and B. I need cells in column B to be marked with arrows (up, right or down) when the difference between value B and A is bigger than 2%, bigger than 0 but smaller than 2%, and smaller than 0 respectively (see the spreadsheet attached).

## Count Of Specific Word In Cells With Many Words

Apr 16, 2008

Is there a way of counting how many rows have cells containing a specific word. Some rows will have multiple cells with the word in and some will have none. I only want a count of the number of rows and not the number of times the word appears. How can i acheive this with VBA?

## Counting Cells With Numbers In Column That Contains Specific Words

May 9, 2014

I'm trying to build a function, but I haven't been able to successfully navigate some of the eccentricities of Excel formula-building.

Right now I am working on counting all of the cells that contain numbers (so likely just a =COUNT function), but will be eventually getting other descriptive stats as well. My problem is that I would like to create a formula that will perform the counting/mean calculation, etc., but only on a specific column that has the header cell that contains several specific words. Rather than having to type in the target words for each column, I would like to have the target words in the function be imported from multiple designated cells.

For example,

I want to find how many numbered cells are found in column E headed as "dog brown," but I also have columns headed as "cat brown" "dog black" etc. (which I will be using the formula on later) to paint a small picture, in my descriptive table, "dog" is in B31 and "brown" is in C30, then "cat" in B32 and "black" in D30.

## Highlight/select Data Between 2 Cells Containing Specific Words

Nov 6, 2008

Sample data (all in column a):

Heading 1
123
12345
1234
345
Heading 2
987
9876
Heading 3
785
Heading 1
2356
8525
Heading 2.........

## Function To Return Common Words To 2 Cells

Dec 29, 2008

I'm trying to write UDF which getting RegEx pattern and a certain cell as arguments and returns only matching string. For examples for string "The quick brown fox jumps over the lazy dog", and RegEx pattern "w{4}" the function will return two words "OVER" and "LAZY". What should I change in my code?

Function GetPattern(myPattern As String, myString As String)
Dim regEx As RegExp
Dim Matches As Object
Set regEx = CreateObject("VBScript.RegExp")

With regEx
.Pattern = myPattern
.IgnoreCase = True
End With
GetPattern = regEx.Replace(myString, "\$1")
End Function

## VBA Code To Colour Certain Cells Based On RGB Colour Shown In Cell A1

Apr 17, 2014

I have a lookup that gives and RGB colour code in Cell A1; for example 186, 206, 140.

I'd then like Cells R10:V15 to fill with the RGB colour based on the result in A1.

## How To Sync Two Cells And Sort As One Based On Common Words

Apr 30, 2013

I would like to sync cells together that contain common words for sorting purposes is this possible? For instance i have a [URL] ..... in column A row 1 and In column B row 2 i have the word bellmont i need to get the rows to sync so rows containing common words line up. I have 8,000 rows to sync?

## Instead Of Showing Figures For MAXIMUM Function, I Want To Show The Name Of The Row

Dec 16, 2008

Normally, when we use the =MAX() function, figures / numbers will appear. Is there any formula that can show the row the highest data is in? Let's just say that the figure is 3080 and it is under 'Transportation' row. Generally, when I apply =MAX() fx, 3080 will appear. Is it possible for it to show Transportation by using formula instead?

## Formula To Show A Date Range Based On The Amount Shown In Another Cell

Sep 11, 2009

Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc.

I added a new cell called Track Back on the employee search sheet.

What I want to do is only show variances for the amount of days back selected in the Track Back cell.

For example if I select the last 30 days, only the last 30 days would show up below in the sheet.

I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me

## Formula Not Adding Correctly, Calculate The Results Based Off Of What Is Shown In The Cell

Apr 9, 2009

Basically a cell might have a number with 3 decimal places

For Example 90.554, and i will format the cell two 2 decimal places. But when i use those cells which are formated to 2 decimal places in a formula it produces results based of all the decimal places in that cell.

Is there a way for the formula to only calculate the results based off of what is shown in the cell? . This is messing up our accounting area by alot of dollars since we sell many items.

I included an example of what i mean in this excel file which should clear up any confusion.

## Lookup Range Of Cells And Populate Specific Cells Based On Matching Data?

May 23, 2014

I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.

I have attached the worksheet so you can see what i am trying to achieve.

## Totals Based On Monthly Figures From Last Year

Jun 3, 2006

I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments \$75.

One month Last year the store made 31.82% on it’s money.
The bonus for anything over is \$75 per 10% increments.
Ie
0.00% \$400
0.10% \$475
0.20% \$550
0.30% \$625
0.40% \$700
So this month was over last months, 37.18% so the bonus was \$3,975.

What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with \$75. added to a base of \$400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.

## Showing Correct Percentage Based On Monthly Figures

Mar 21, 2013

I have the following three columns of Data and I'm using this formula to calculate the %age difference cells:

=IF(ISERROR((F14-F13)/F13), " ", IF(ISBLANK(F14)," ",IF(F14=0,"-100%",(F14-F13)/F13)))

A - No. of Hits B - %age difference C - Weekly Average

25
50 100%
25 -50%
0 -100%
20
0 -100%
0
50

I have left blank the "cells" which are giving me trouble, and my troubles are as follow:

-Where the value of the previous month was 0 and the value of the current month is higher my output is Blank, ideally I would like this to show as 100%
-Where the value of two months in a row are 0 I would like the value to show as 0% for the second month

I did add extra into the formula so it would show the 100%, unfortunately it did this for any consecutive months at 0 also which was incorrect.

## Sum Up Cells And Ignore Cells Shown As #I/T

Jun 2, 2009

How can I make excel sum up a colum, and ignore cells shown as #I/T?
This error sign might not be the same in the english version of excel.

If I try to sum up cells with the error value, the feedback will also be #I/T.

Faulty formula is now; =(SUM(B307:B316))

## Searching For Specific Words

Mar 15, 2007

I have 1000 diff names in a spread sheet and wish to search another spread sheet with these names on and alot more.

I want the search to look in the larger spread sheet for these 1000 names
and copy all the informtaion in the cells to a new sheet, if the name is not found i would like something like "no info found" to be seen.

Im a novice to doing this sort of thing in excel, but i have found this script which searches for the word "mail box" and copies all the info in the cell when it finds it.

## Lookup On Specific Words

Apr 29, 2014

I have two sources of information that I need to look up on, but one of the sources contains other words within the same cell?

For Example I have a source of data which contains all our client names, and I need to search for the client name in a second source of data to bring through the value from another cell, however the table array that I would do the look up on contains both the client name and project name together in one cell - therefore I am unable to do a normal Vlookup - also the client name is not always the same number of letters.

See an example:

Source data 1:
Client name:
XYZ Ltd

Source data 2:
in cell A1 XYZ Ltd/project ABC
in cell A2 shows the value e.g.: Â£5000

Would there be anyway to do a look up on XYZ Ltd to bring through the value in A2 on source data 1?

## Determine If Cell Has Specific Words?

Jun 18, 2008

Basically, I want a formula that searches for more than one word anywhere within a cell, and if the cell contains any of those words somewhere in it then I want to display a value, but if the words aren't there, then stay blank.

For example, my cell could say: "Magenta, Rose, Lime, Lavendar". I want to search this cell for any of the these words: "Lime", "Forest" or "Teal". If any of these appear, I then want the cell with this formula to say "Green".

I know the formula to search for only one word /phrase in a cell, I use: =(IF(ISNUMBER(SEARCH("yellow",A1)),"colour","-")), (i.e. if the word "yellow" appears anywhere in cell A1, then this cell will show the word "colour", if not then a "-".) I tried adding an 'OR' function in this to search multiple words but it didn't seem to work.

## How To Return One And Two Words Before And After Specific Word

Apr 14, 2012

I am in need of a forumula. I am trying to extract one word before a keyword, one word after a keyword, 2 words before a keyword, and 2 words after a keyword.

For example:

The keyword is blue:

birds are blue today - 1 word before=are; 1 word after=today; 2 words before=birds are; 2 words after=today

blue is my favorite color - 1 word before=(null); 1 word after=is; 2 words before=(null); 2 words after=is my

see blue colors in the sky - 1 word before=see; 1 word after=colors; 2 words before=see; 2 words after=colors in

## Extract Numbers After Specific Words?

Mar 13, 2014

I am trying to extract numbers after a specific text in a text, for eg :

abc SN 12345 xyz
edf SN No. 456 mno

As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)

Any formula to get result as "12345" and "456".

## How To Delete All Words Containing Specific Characters

May 14, 2014

I need a formula which can clean up a huge data set. Essentially i need to delete the entire word which contains the characters "aceae". note that "aceae" is a suffix, but i need to delete the entire word not just the suffix, plus keep the rest of the string. i have tried the "find and replace" function of excel with wildcard, but that deletes everything before/after without deleting the entire word. i have tried a combination of formulas to isolate the unwanted words, but that method is inefficient and inaccurate. below is a schematic of what im looking to do:

Column A ------------------------------ Column B
l. planeri asteraceae africa laselva-----> l. planeri africa laselva
l. planeri moraceae europe singer------> l. planeri europe singer
origin l. fluviatilis bignoniaceae asia----> origin l. fluviatilis asia
alternate l. fluviatilis piperaceae asia---> alternate l. fluviatilis asia

## Delete Rows That Contain Specific Words

Nov 10, 2006

how to create one macro: I have a spreadsheet with 8 columns with various text headings. Each column has different data (some show dates, some show only figures, some show text). Specifically, column E (entitled "Sub Item Reference") has various text entries (e.g. "Newsletter" or "Booklet"). I am hoping to find a simple macro that will search column E and delete any rows where that column contains the text "Cover" or "Label". I'm sure it's the simplest thing going but I can't seem to work it out.

## VBA To Populate To Specific Cells Based On Criteria

Jul 6, 2014

I am looking for a VBA that could populate from column (A2:A5) to cells (B2:F5) as in the attached spreadsheet, based on the frequency, in a five year time-frame.

screen_print.JPG

[Code] .....

## Extract Words Function

Jan 10, 2008

I am using the extract words function outline here [url]

I copied and pasted the function into a new sub and deleted the sub module1 and the end sub to make sure it is it own function I can call. I have called it in another macro I wrote and when I run the macro it bugs out at the first if statement. The code it has a problem with the Get_Word code below. It throws a "Run-time error: '1004': unable to get the Find property of the WorksheetFunction class."...

## Putting Specific Value In Blank Cells In Same Row Based On Value In 1st Cell?

Jul 18, 2013

i am trying to Put a specific value i,e "NO Deliveries" in blank Cells in the same row based on value in 1st cell of the Row which has a fixed value . note:the columns and rows are dynamic for example: I want to put the value "no Deliveries" in cell C because its blank.

A B C D

% of Availability
0.00%

80.00%

## Find And Replace Macro Based On Specific Cells

Sep 12, 2009

After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.

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