# Text In Cell Then Formula To Put Particular Number In Adjacent Cell

Feb 16, 2014

I have text in column A of either "xxx" or "yyy"

I'm looking for a formula so that If

A1: "xxx" then B1: \$100
Or if
A1: "yyy" then B1: \$150

So when I write "xxx" or "yyy" in a cell of Col A

then \$100 or \$150 shows up in adjacent cell in Col B automatically.

I will need to sum the numbers later so Col B will need to be in number/accounting format.

## Excel 2013 :: Check Cell For Text And Return Corresponding Text In Adjacent Cell?

Jan 28, 2014

I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).

What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.

## Copy Any Bold Text Within Cell Text String To Adjacent Cell?

Jul 31, 2014

Here's an interesting one:

A1 contains a text string which is both bold and unbold (
B1 is blank

I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.

Example:

A1
The sky is blue

Macro is run

B1
sky blue

## Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

## Excel 2010 :: Locating One Cell Data In Text Of Adjacent Cell?

Jul 1, 2013

I am using Excel 2010.

In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.

I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.

## Find Cell Value In Column & Return Text In Adjacent Cell

Jun 10, 2008

I have a drop down list in a merged cell B12-F12 and B13-F13 and B14-F14 . . . B30-F30.

I need the adjacent merged cell to populate a reason (text) based on the text answer in the drop down list or the entered text in the first merged cell. For example in the cell B12-F12 the user picks from the list or types in "Amiodarone." I want the adjacent merged cell G12-J12to automatically fill with "Heart Rhythm." I also want to be able to set up multiple if - them statements like if Amiodarone is entered then fill adjacent cell with Heart Rhythm and if Toprol XL then fill adjacent cell with Heart / Blood pressure and if simvastatin then fill adjacent cell with Cholesterol, etc. I have about 30 different options for cell 1 that I want to have auto fill in cell 2 based on the contents of cell 1. I've attached my file.

I want the user to be able to choose from the list or type the drug name in.

## Incorporate Adjacent Cell In Formula W/o Naming Cell Address?

Oct 2, 2009

I searched and I keep finding something that's not quite what I'm looking to do so I think I'm missing something really simple.

Let's say that I want each cell in col b to be the sum of 5 plus whatever value is in the cell adjacent to it on the left (col).

I know that you can just write a formula in each cell like...

b1 will be =sum(a1+5)
b2 will be =sum(a2+5)

... but is there a shortcut so that you don't have to write out the actual cell address for each one?

Meaning - is there a predefined name or something that represents the cell to the left or right so you can just use that instead, allowing you to just copy paste the same formula all the way down the column? Something like...

b1 will be =sum(left+5)
b2 will be =sum(left+5)

... where "left" represents whatever value is in the cell to the left of that particular cell?

## Count Cells By Number & Add Adjacent Cell If Number Is X

Jan 19, 2008

Create some sort of formula combination or macro that will: Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total. Ive tried lots of methods but i cant figure this one out!

## Use Value From A Cell Immediately Adjacent To Referenced Cell In A Formula

Feb 25, 2014

I need to create a cell reference which will allow me to specify a cell in a table by value (using indirect) and pull the value from a cell immediately one row over for use in a formula. Say I have a table Containing column A and B; I want to reference the value of a cell in column A (e.g. "Large Widget") and receive (in the formula) the value in column B (e.g. "41 cm^2").

## Make Text Flow Into Adjacent Cell Without Merging Cells Or Wrapping Text?

Jul 19, 2014

I have some text in a cell, which is longer than the width that I am going to make that cell. When I don't wrap the text, it cuts it off at the end of the cell.

Since each column represents a day in a calendar, it would be incorrect to merge the two cells, but I don't want the text hidden, since I need to print the final product.

It is non-numeric text, and i've pasted --> values to remove formula issues.

## Compare Text Strings In Two Columns And Return Text From Adjacent Cell

Feb 28, 2013

how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?

## Sum Cells Whenever Adjacent Cell Contains Text

Feb 22, 2014

Here's what I'm working with:

??
5
3
4
1
Total 13

5
6
7
2
Total 20

Is there any way I can sum only the cells in the right column where the left column contains "Total"?

## Show Text If Adjacent Cell Is Not Blank

Aug 8, 2008

I am looking for a piece of code that I can use.

Basically if cell A2 is non blank then I want cell C2 to display My Text. I want to do this for every cell down to about A250.

## Comments In Dropdown Box Dependent On Number Selected In Adjacent Cell

Jul 8, 2013

I have been asked by my supervisor to make a performance review template a little bit more user friendly.

Basically what I have is a drop own list in cells in one column (various cells with the same drop down menu) that you select from either 0-10 in a drop down box.

I have another cell which I want to have a drop down box with text comments that you select from when. Only problem is the comments different dependent on the ranking score you give a person (the number selection in the previously mentioned cell from 0-10). So if you have a score between 9-10 in the cell then the comments box will give you certain comments to choose, 7-8 different comments and so on and so forth.

## Number Rows In Numeric Order IF Adjacent Cell Not Empty

Sep 22, 2007

I am looking for a formula to number rows in A1:A500
I would like to skip a row when B1:B500 cells are empty
then skip that row and start renumbering from 1
This is what I tried so far
=If(b9>"", countif(\$B\$9:\$B9,">"""),"")

Apr 22, 2014

I am wanting to add the value of an adjacent cell and keep the SUM in another cell... depending on that cell value.

For example: H10 value is "JW" .. so that number adjacent (G10) would be added to the cell G4 (next to JW), and same with "NK". If it says "NK/JW" then I need it to be divided equally.

N528MW Revised.xlsx

## Gathering Sum Of Cells Based On Text In Adjacent Cell?

Jan 15, 2010

I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

## Conditional Formatting Based On Text In Adjacent Cell?

Feb 16, 2014

A2:A5 contains different dollar amounts, a6 is the sum of those amounts

In column B (B2:B5), i place a "p" beside the amount in column A once it has been paid.

I want to:

a. conditionally format the amounts in column a to turn green once I put the 'p' in the adjacent column

b. conditionally format the sum in A6 to exclude amounts that have been paid, and only display the total amount of the unpaid lines.

## Gathering The Sum Of Cells Based On The Text In The Adjacent Cell

Jan 15, 2010

I have a column of dollar amounts (variances) and next to each is the shift they belong to.

I am looking to get a break down of the variances for each shift.

Attached is an example of what it looks like.

## Search A Range To Enter Text In Adjacent Cell

Jul 19, 2006

When i press say a command button " Add Repair Information" i want the User to Enter the serial number which i have given it a range called SerialN from the excel sheet and enter a repair action. Is there a function in Excel which will allow me to search the Serial Number and allow me to put the Repair action across in the cell on the same line?

## Find Text String And Adjacent Cell Contents

Feb 20, 2008

I would like to find a text string in a document, move the information in the cell to the right of this to another cell (say A1).

## Display Formula In Adjacent Cell

Mar 19, 2009

I want to display the formula in the adjacent box to where it is actually used. Would anyone happen to know how to get that done? Example:

If cell A1 contains the following formula: A1+ A2
I want cell A2 to show : '= A1+A2'

## Excel 2007 :: Insert Random Number Preceded By X If Adjacent Cell Has Data

Nov 24, 2011

i have a problem in that i need to generate a random 6 digit number preceeded by an X if the data in column B is either blank or 0. This needs to be filled in if the adjacent cell in column C has data in. i am a moderate macro writer and am finding this part quite challenging.

i am ussing excel 2007.

## Formula To Search For One Word In Adjacent Cell?

Jul 8, 2014

I've got a long list of company names, i'm trying to create a formula to see if they match but the problem is the names won't match exactly (e.g it would be Joe Bloggs Limited & Joe blogg's ltd). I've thought about it and think the best way to see if they match would be to take the first word from the first cell (in this case Joe) and then search for it in the next cell and return TRUE if it exists anywhere in the cell and FALSE if not.

## Formula To Check Adjacent Cell Value, And Report.

Jan 5, 2009

Ok. I am creating item lists in excel for every product that my company sells. I am in need of a formula that does the following:

I want the cell to check to see if there is a value in another cell, and if there is anything at all, i want to display the word Yes as the value, or leave the cell empty.

So I want the cells in Column A to Check if the adjacent cells in Column B have a value. If the column b cells have a value I want the cells in Column A to display the word "Inventory Part". If there is no value I want the cell left blank.

## Find And Replace By Adjacent Cell Formula

Feb 4, 2014

Is there a formula that can find a keyword within a cell then replace it with a formula that will add the adjacent cell value up.

A1=103 B1=104
A2=104 B2=105
A3=105 B3=Out-1p-(1)
A4=110 B4=111
A5=111 B5=112
A6=112 B6=113
A7=113 B7=114
A8=114 B8=Out-3p-(23)

I am looking for a formula that can automatically calc (B3) to 106 (based upon cell A3) if the cell has a value of (*out*).

## Auto-Fill Formula When Adjacent Cell Changes

Jan 6, 2007

what is prompting this formula to auto fill down when a value in an adjacent cell is entered.

In column A I enter an unformatted fax number.
In column B I use the following Text Formula:
"="("&LEFT(A20,3)&")"&MID(A20,4,3)&"-"&RIGHT(A20,4)

Someone in my office copied the workbook and now any time a value is
entered into the next available Column A cell. The cell in column B will automatically apply the above formula and format the fax number. The person who copied the workbook does not know how this is working nor does anyone else in the office.

There are no macros embedded in this workbook so no event is firing to cause this.

I have attached a small copy of the workbook called "Auto format1.xls"

How to use:
-Select cell B20 and not nothing is in it.
-Enter any 9 digits in A20 and B20 somehow
copies down the formula from B19.

how to duplicate this.

## Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell

Jan 19, 2008

One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

## Create Multiple Text Files From Cells & Save As Name In Adjacent Cell

Jun 26, 2008

I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.

## Formula To Keep Adjacent Cell Blank When No Date Is Added

Jan 23, 2014

I have a formula in my spreadsheet that shows dates for servicing of products. Column F show's Date Last Serviced and the adjacent column (G) then shows Date Next Service. When there is nothing on the F column's cell then the date that appears on my spreadsheet is 31/12/00. It looks messy and I would rather have a formula to make it so that if the F column is blank, then the adjacent cell in G column should stay blank also.

To automatically calculate the next service due date, I have variants of this formula that creates the dates in G column:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))

I tried adding as an extra part, for example:

=DATE(YEAR(F4)+1,MONTH(F4),DAY(F4))&If(F4 = 0, "", F4)

It always brings back the answer of 366, and does not make the cell blank...