Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

If Cell Equals Text Then Return Another Cell

I need a formula that If One Cell Equals Certain Text, Then Another Cell Equals The Value Of Another Cell...

at the mo i've got

=IF(A2="PAID OUT", "OK", "Not OK")

but instead of the cell reading OK or Not OK, i need it to equal another cell. ie, if A2="PAID OUT", then =B2

how do i do that?


Post your answer or comment

comments powered by Disqus
I am trying to build a formula that would say If cell contains text then copy/paste text from a different cell.

For example:

I am writing my formula in cell I5. If there is any text in J5 then I want the text from J3 to appear in I5.

Background:

-What I am doing is making an excel spread sheet for meeting notes.
-Column A has the project names.
-Column I is the date the project was last updated.
-Columns from J on, represent weeks. (Column J is for 1/1, Column K would be for 1/8, Column L would be for 1/15, etc.) The column heading (the cells in row 3) would be the date.

Each week, a new column is added (for a new week) and if there is any new information it is typed for that project for that week. If there is any new text in that week's column, I want the heading for the column (in my case the date for that week) to appear in Column I.

I hope this makes sense; please let me know if I need to clarify anything.

Thank you,

Laura

TITLE: VB Scrip Help - IF CELL EQUALS VALUE THAN RETURN VALUE FROM COLUMN WITH SAME TITLE

I am looking for help to write a formula or script for the attached work document

The Goal is to reduce the amount of time I have to expend looking for bracket price for an item, based on the type of business:

Currently I have these business types:

GOV_EDU_PUBLIC_BID
BSC_COMMERCIAL
HEALTHCARE
LODGING_HOTEL_RESORT
ENTERTAIMENT_TRAVEL
FOOD_SERVICE
RETAIL_CASH_CARRY
EXPORT
NON_PROFIT
PORT_A_TOILET
ASCTD_DISTR_via_RD
RD_SE_Price

each type has a different price for the same item:

so I am trying to update cells Q24:Q47

this is the logic for what I need to achieve:

IF P24 = Empty "DO NOTHING"
IF P24 = same value as O24 ,
THAN GO to CELL N24
IF N24 = Empty "DO NOTHING"
IF N24 "VALUE" is equals to "EXPORT" (Example)
LOOKUP "N24 VALUE" in cells "R23:AC23" (Column Title)
THAN RETURN PRICE FOR ITEM NUMBER ON P24
FROM COLUMN WITH SAME TITLE "EXPORT"
Example: Y24 = should bring price of $40 to cell Q24

DO THE SAME FOR ALL ROWS Q24:Q47

FOR YOUR INFORMATION :
THERE ARE TWO WORKSHEETS IN THIS WORK DOCUMENT.
ONE HAS CALCULATION SHEET "Fill_IF_SEGMENT_ID" other are the Prices to return "SEGMENT_ID"

How can I detect a cell value in VBA if the cell is text? I want to set an if statement based on the whether or not cell B7 equals USA.

So if cell B7 = "USA" then copy paste B4, B5, B6 to A1, C1, D1,

If B7<> "USA" then copy paste B4, B5, B6, B7 to A1, B1, C1, D1.

I'm new to programming but have made macros using macro record as well as modifying some of the macros from examples on the internet. I can’t seem to find the appropriate functions in VBA help or figure ot enough from the examples on the site to accomplish what I want. Just a point in the right direction would be greatly appreciated.

:: Description of what I'm trying to do ::
I have numerous invoices imported from .csv files in excel documents. I would like to take the billing name shipping name etc and put them all on one row. Unfortunately since the invoice is saved from a webpage the Address 2 Field is dynamic I need to detect what kind of invoice it is to copy the fields. All of our invoices have USA after the city, ST, Zip cell. So by determining which cell USA is in I can copy paste the appropriate cells into my row.

Thanks for any help or guidance.

Scott

Hello,

Thanks for all contributors, your Excel Forum are great helps to all.

I looking to resolve a problem as below:

I have 6 columns of variable data. I want to search IF cells contain a TEXT strings exist THEN I copy the found cells plus the respective data in paralele at the last column to 2 different cells. See below samples, the text strings maybe in any columns

A B C D E F
1 123456 Dany 78901 234567
2 Tommy 890456 Jenny
3 457868 Johny (df#)l
|
|
|
|
79 Danny 234567
80 Tommy Jenny
81 Johny (df#)l

The last column may contains text string, numeric or symbols

The searche results are move to the last two columns at rows 79, 80, 81. The columns & rows is for example only.

Thanks,

I want to have an If/Then statement that looks for text in a cell and then
increases a count by 1.

=If(B33="text"),("b40+1"), (""))

This is about what I have, but I want something that would read something
like:
If cell b33 contains this text, then add 1 to cell b40, if not then do
nothing.

Thanks,

Louise

I have a column that has either One, Two, Three, Four, Five in each cell. I am adding a new column & wanting a formula where I put

If cell equals One or Five then put Upper
If cell equals Two, Three, or Four then put Lower

Thanks everyone, I need help with this formula? I've tried several things but I cannot get it to work

Formula needed if cell b1 = A , then b50 will equal 1 if not b50 = 0

Have a great day!

Is there a formula I can use in B2 whereby if the value in A2 is identical to the value in any other cell in column A, then return the value "duplicate" in B2?

Excel Experts

I am writing a autosum procedure. As part of the code I want to choose the
first cell in a column that contains a number. I use .End(xlUp) to get to
the top cell.

But how do I go down one cell if the top cell is text (a column heading).

I tried

If IsText(Selection) = True Then
Selection.Offset(1)
End If

But IsText only works as a worksheet function I think.

What code would I use to say: If this cell is text, go down one cell??

Thanks,

Alan

--
achidsey

Hey all,

I'm pretty new to VBA so this may seem like a super n00b question but I gotta ask it anyway...

Here is what I want to do (in VBA):

If A2 contains (any)text then concatenate text in A2 with text in C2 and display result in D2, else do nothing ...

Also I want to apply the above to the entire column

Thanks in advance for you help!

Cheers

Hi,

Having a braindead morning here.

I have 3 columns a=number, b=customer, c=quantity.

Column B has various customer names.
In column D, I need a formula which says if b2 contains text of any kind then show the text with (2) tagged on the end.
ie if collins is showing in b2 then d2 should show Collins(2)

Apologies for this, head all over

OK here is what I am trying to do.
At my sports company I run monthly inventory reports. My inventory system spits out the MASTER report in an excel spreadsheet. I then take that spreadsheet and bust it up into different catagories. These catagories group each individual sports inventory together so I can see what kind of inventory I own by sport.
Currently I manually seperate the formentioned MASTER report into the individual sport spreadsheets. This is somewhat teadious and time consuming. Is there not a way I can seperate the MASTER inventory into individual spreadsheets with a formula?
My inventory is coded by sport (So basketball products start with BB, football products with FB etc...) I was thinking of something along the lines of a vertical lookup with an IF argument or something like that.

In laymans terms the formula should read like this:

IF (cell) contains text that begins with BB move to (New Spreadsheet).

I guess the second part of my question is what would my flase argument be in this case? Something like If Not check cell below... ???

It sounds like such an easy thing... but my mind is boggled as to how to make it happen... PLEASE HELP!!!

Clear contents in BJ39:BV500,
then for each cell in range (BI40:BI500)
If cell.Value = TRUE then
copy the entire row
in the source range (AV40:BH500)
to the new location
in the target range (BJ40:BV500)
then copy contents in AV39:BH39 to BJ39:BV39

Can I include a picture in a formula i.e. if cell a26 >90% then show picture
x, if less then show picture y?...

I have a column of about 20 rows and in a particular cell I am trying to get an answer by subtracting 2 other cells. This works fine until I have to skip a cell. What can I do to tell it if cell is blank then goto the next cell that has numbers in it?

Thanks for any help.

Can I include a picture in a formula i.e. if cell a26 >90% then show picture
x, if less then show picture y?...

I am wonderying if it is possible to return another cell value into an If function with text?

an example: IF(C45>10,"sample text"(C27)"sample text","NO")

If not I guess it would be possible to split up things into different cells and line everything up the best I could but since C27 would be changing size it may get cut off from the column widths at times. And its actually a merged cell so autofit selection wouldnt work unless I come up with a way of changing things around. Thanks

How do i write an If function where if a cell has a certain text in it, it would return a value to onather cell.

For instance:
If cell B1 has "red" in it the cell B4 must be "6"
If cell B1 has 'blue" in it, then cell B4 must be "7"
and so on with certain text returning a certain value?

Please can you help me?

I have been given the following code and it works great. I now need to adapt it to the following scenario:

In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5.
If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B".
If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C".

Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.

WHEW! Clear as mud?


	VB:
	
 Range) 
     'I want to  exclude columns A and B as well as rows 1, 2, and 3.
    If Target.Column < 3 Or Target.Row < 4 Then Exit Sub 
    Dim MyCmmnt As String 
    If Sh.Name = "DATA" Then Exit Sub 
    On Error Goto errorout 'Resume Next
    If Target.Value  Cells(Target.Row, "B").Value Then 
        MyCmmnt = Application.InputBox("Briefly explain the discrepency", "Comment", Type:=2) 
        If MyCmmnt = "False" Or Len(Trim$(MyCmmnt)) = 0 Then Exit Sub 
        With Target 
            .AddComment 
            .Comment.Text Text:=Application.UserName & ":" & Chr(10) & MyCmmnt 
            .Comment.Visible = False 
        End With 
    End If 
errorout: 
    Err.Clear 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello, I'm trying to make a formula in excel 2007, where if for instance A1 equals "x" or is empty, then B2 would display "N/A", otherwise it would display "missing".
So if the cells in column A contain either x or are completely empty, the corresponding cells in column B would show N/A. if the cells in column A contain anything else, the corresponding cells in B would display "missing".
this is what I tried so far:
=IF(OR(A1="x", A1=""), "N/A", "missing")
I also thought maybe apostrophes are needed instead of quotes, I never know which to use and when:
=IF(OR(A1='x', A1=''), 'N/A', 'missing')

thanks a lot in advance!

Hello,

I want to scan for duplicates and then change the text in one column. The text will change depending on what the duplicate value is i.e. ADDED will change to RESCHEDULED TO and CANCELLED will change to RESCHEDULED. Then only problem I cant work out is that If the last duplicate on the list has ADDED in the cell, how do I change it to RESCHEDULED TO. I belive the 3rd If Statment would need changing (i+1), I tried but can't get consistnent results.

Many Thanks,


	VB:
	
 test() 
    Dim a, i As Long, temp As String 
    With ActiveSheet 
        a = .Range("a1", .UsedRange.SpecialCells(xlLastCell)).Value 
    End With 
    With CreateObject("Scripting.Dictionary") 
        .comparemode = vbTextCompare 
        For i = UBound(a, 1) To 1 Step -1 
            temp = Join$(Array(a(i, 5), a(i, 6), a(i, 8)), Chr(2)) 
            If Len(temp) > 2 Then 
                If Not .exists(temp) Then 
                    .Add temp, Empty 
                Else 
                    If a(i, 9) = "ADDED" Then 
                        a(i, 9) = "RESCHEDULED TO" 
                    Else 
                        If a(i, 9) = "CANCELLED" Then 
                            a(i, 9) = "RESCHEDULED" 
                        Else 
                            If a((i+1), 9) = "ADDED" Then 'Offset here?
                                a((i+1), 9) = "RESCHEDULED TO" 
                            Else 
                            End If 
                        End If 
                    End If 
                End If 
            End If 
        Next 
    End With 
    Range("a1").Resize(UBound(a, 1), UBound(a, 2)).Value = a 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to return the number 1 in any cell next to a cell that has letters or numbers

I've tried a couple "if then" but keep getting a #name error

any ideas?

What formula could I use to find the address of a cell containing a specific text string? Such as:

__A____B____C____D____
1| Hello Sir!

2|asdlkjfkld

3|sdfsdfjsdf

So, if the text that I'm searching for is "Sir", what formula could I use to search for the text and return the cell address where it resides?

N

Hi,

I am pretty new to using VBA and macros in excel. I currently have a large spreadsheet which is used for testing. I have multiple worksheets and on each sheet, there are many rows each containing test procedures and outcomes.

What I'm trying to do:
Some of the tests in each sheet only apply to certain products. In each row the test is detailed, the outcome can be recorded, and the products that the test applies to are listed e.g.

Column 1 - Test Procedure
Column 2 - Outcome
Column 3 - ProductA, ProductC etc

I need the macro to look at Column 3 and search for text relating to each product as I specify elsewhere in the sheet. I will create a button on the first sheet named as the product I want to select. This would then go through each row on each seet in the work book and hide any rows where the Product name is NOT listed. Here is what I have so far:

Sub HideRows()
Dim cell As range

Sheets("Sheet2").Select

For n = 2 To ActiveWorkbook.Sheets.Count
 
For Each cell In range("D4:D30")
If cell.Value = "ProductA" Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If

Next

I put the code in below to make the macro return to the seet I want once it reaches the last sheet in the workbook
If ActiveSheet.Name = "Sheet7" Then
Sheets("Sheet1").Select
Else
ActiveSheet.Next.Select
End If

Next n

End Sub
At the moment this code works if the ONLY text in the cells is "ProductA". This doesnt work if multiple products are listed in the cell e.g. "ProductA, ProductB".
I would also like the macro to ignore any blank cells.

I thought it would be easy but with my limited knowledge I am completely stuck.

Any help would be much appreciated!


No luck finding an answer? You could always try Google.