Display R, A Or G Depending On Text In 2 Other Cells

Apr 15, 2009

I am trying to nest a formula with IF & AND to work out a RAG status. I have 2 questions per row (in Columns I:J) which will be answered either Yes or No. For each combination I need to give a RAG status in Column K – e.g. two Yes replies give Green, 2 No replies give no and one No out of 2 will give Amber

Below is hoped for response in Column K:

ColI---Col--- JCol K
Q1----Q2----RAG
Yes---Yes---G
Yes---No----A
No----Yes---A
No----No----R

View 3 Replies


ADVERTISEMENT

Displaying Some Cells Data Depending On Selected Text From Other Sheet

Dec 31, 2013

I have an excel sheet which looks similar to below and with some more columns.

HTML Code: 

AreaBranchEmpolyeesProductsShelfs
Area1Area1 Branch110Product 110
Area1Area1 Branch25Product 16
Area1Area1 Branch38Product 28

[Code] ......

I want to have data in other sheet With respect to Product details. Where It gives me the count of Product 1 and so on.

I am able to get the count using countif function and it displays like below.

HTML Code: 

Product Count
Product 16
Product 28
Product 34
Product 49
Product 510
Product 68
Product 78
Product 89
Product 97
Product 108

What I want is when I select "Product 1" It should display some selected columns data from sheet where complete data is available.

HTML Code: 

Branch Empolyees Other
Area1 Branch1 10 abc
Area1 Branch2 5 xyz
Area1 Branch5 7 pqr
Area2 Branch5 5 wxy

Which function we can use. I tried Vlookup but was not successfull.

View 14 Replies View Related

Auto-populate Cells To Display Text From Another Cell Without Cutting Off Text?

Sep 2, 2013

I am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...

Excel Q.xlsx‎

View 7 Replies View Related

Text Trimming - Display Part Of Cells Text Value Inside Another Cell

Jun 15, 2014

I want to know how to display part of a cells text value, inside another cell.

Suppose in cell A1 i have "20-Jun-14"

How would I get cell B2 to display just "Jun"?

View 4 Replies View Related

VBA To Change Background Colour In Free Form Depending On Text In Cells?

Apr 3, 2014

I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.

Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").

If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).

This should happend automaticly, so I don't have to click the shape to make it happen.

View 5 Replies View Related

Insert Formula Depending On Cells Depending Of Variables?

Jan 27, 2014

In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:

[Code] .....

All my variables are declared:

Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed

The error message I receive is the following: "Run-time error 13, Type mismatch"

I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.

View 3 Replies View Related

List To Display Value Depending On Certain Columns

Apr 24, 2013

I am developing a workbook, one part of the book is to track discipline I have a page for this and a userform on the front page to enter data into it. I would like if you will a summary box (list box or whatever is best) displayed on the front page and I would like a line to appear in that box maybe saying john smith requires a warning. I need this to appear if john smith has been entered 3 times into the name column on the discipline sheet however only if the offence is the same 3 times for example 3 lates not 2 lates and 1 non attendance.

After it displays this line in the summary box I would like to be able to double click it where a small userform appears where a user can then select discipline received or something and for this lien to disappear in the summary and all 3 entries in the discipline sheet.

View 9 Replies View Related

Display Time Depending On Weekday

Aug 15, 2007

I have a date that is customised into dd-mmm-yy dddd eg 14-Aug-07 Tuesday in cell A1. I need help in formulating a formula to identify if the date in A1 is friday, to return "5:30 pm", else "6:00 pm" in cell B1.

View 5 Replies View Related

To Display Most Repeated Text / Value From A Range Of Cells

Feb 9, 2009

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

View 14 Replies View Related

Display Test String Depending On Date

Mar 31, 2009

I need a forumla that will dispay a set text message in a cell based on the date in another cell.

I need cell A1 to display the text message "Forwards Booking Date, No Action Required" Where cell A2 has a date entered in it that is the same as todays date or greater - i.e. in the future.

And if the date is in the past for no text message to appear.

View 9 Replies View Related

How To Make A Cell Display Text Difference Between Two Cells

Aug 30, 2013

Is there a way to make a cell display the text difference between two cells?

For example of cell E2 reads "Thank Goodness it is Friday" and cell F2 reads "Thank Goodness" can I put a formula in H2 that will show the text that is in E2 but not F2 ( "it is Friday")?

View 1 Replies View Related

Formula To Have One Cell Display Multiple Options Depending On Value In Another Box

Oct 1, 2013

I need to build a formula to have one cell display multiple options depending on a value in another box.

If Value is >x and <x display Y

Details:

Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:

If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.

View 2 Replies View Related

IF Function To Display A Message Depending On Percentage In Cell

Feb 21, 2007

Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.

I want the IF function to display a message depending on what the percentage in cell E35 shows;

IF, 0>> Display "Bad"

40>> Display "OK"

70>> "Good"

90>> Display "Very Good"

E35>=100 >>>> Display "Excellent"

I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.

View 9 Replies View Related

Conditional Formatting For Blank Cells To Display Custom Text

Feb 17, 2012

I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"

The blank fields get the light green fill, but no text; that is, the value displayed is still blank.

I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).

This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.

I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].

I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???

I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.

I also made sure the option [x] Show a zero in cells that have a zero value is checked.

With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.

And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.

View 2 Replies View Related

Get And Display Pictures (in Folder On Hard Drive) Depending On Cell Value In Excel Sheet?

Sep 9, 2012

I am working to make an automatic result card in which excel automatically gets data of every student which is present in sheet within the workbook by simply writing roll number of the student in a cell. and i have done it easily by Vlookup and index functions but the only problem i am facing is, that i want to show the picture of each student by roll no. pictures are in one folder on my hard drive, now i want that as the roll number of the student is written the result excel should display the picture (from the folder, each picture named by roll)of the student. for example i write 1234 in roll no cell and want to get and show the picture no 1234 in another cell. (from the folder)

View 2 Replies View Related

If Cell Text Equals Cell Text In A Range Of Cells Display Value From Range Of Cells

Oct 4, 2012

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??

=IF(C2='Google Doc'!$B1122:$B1266,"'Google Doc!K1122:K1266'","Not OK")

View 9 Replies View Related

Sum Range Of Cells Depending On Changeable Values In Other Cells

Aug 2, 2012

Assuming I have a data set like this :

column a column b
916 12,5
916 23,4
923 34,5
923 23,9
932 98,6
934 67,7

How can I get a sum of all cells in column b when values in column a are equal?

View 5 Replies View Related

Return Text Depending On Another Cell

Jan 8, 2007

I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet.

I tried to with Select Case Statement and if-then, but couldn't figure it out since I don't have much VBA experience.

View 6 Replies View Related

Enter Text Depending On Another Cell

Apr 8, 2007

I have a spreadsheet which is attached. I want text to be entered into column A only if there is text in column B. The text to be entered is taken from a cell in another workbook. I have removed the lin to up it to this fourm.

View 5 Replies View Related

Add Or Subtract Depending On Text Value In Another Column

May 3, 2007

I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?

View 6 Replies View Related

Return Value Depending On Text In Cell

Mar 1, 2008

I have a cell A5 in a worksheet that can be either E or W. I am looking for a formula in Cell A10 in the same worksheet to return numeric values of either +1 if cell A5 is W or -1 if cell A5 is E

View 3 Replies View Related

Highlighting Cell Color Depending On Text

Sep 15, 2014

See attached work book, it is an mot booking system that is all working fine except for one thing that I don't know how to change.

At the moment if you double click a cell in the test type column it enters the required data and turns the cell green highlighting the length of time and values this is all good but I am wanting the ones that start with n-side to turn yellow just so that we can see our own internal tests easily. It is control in the coding not formatting . Is there a way this can be done?

View 2 Replies View Related

Cell Shading Macro Depending On Text

May 30, 2008

I will have a spreadsheet similar to the image provided. Basically, I need the macro to go row by row down column I. If the cell in column I contains the word "Tech" I need the cells from column B to P to be shaded color1; if it contains "Update," color 2; if the cell is blank; leave the cell as is. Once the macro has done this, it will continue to the next row and to the same check.

The number of rows in the spreadsheet will change on a day-to-day basis depending on the data that's pulled, but the columns should remain the same.

So I think I have the shading part. This is what I have so far. I'm not sure if this is the most efficient way of coding it, but it kinda works. I guess another problem would be if in the future the column with this info (column I) changes and shifts.

View 10 Replies View Related

Set Font Size Depending On Length Of Text

Sep 3, 2007

When a number is entered in cell S3, it triggers formulas throughout the worksheet to populate the it with information from another sheet. If the length of the text in B6 is greater than 80, the font size for B6 only should be 8; if the length of the text in B6 is less than or equal to 80, the font size for B6 should be 10. Regardless of the length of the text in B6, the font size for the rest of the sheet should not be changed.

I tried the following
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$6" And Target.Count > 1 Then Exit Sub
Dim cnt As Long
cnt = Len(Cells(6, 2).Value)
If cnt > 80 Then Cells(6, 2).Font.Size = 8
ElseIf cnt < 80 Then
Cells(6, 2).Font.Size = 10
End If
End Sub

View 9 Replies View Related

Text Colour Change Depending On Outcome Of Formula

Jul 6, 2009

i am looking for a formula, if i put it as plainly as i can then i hope someone can work it out for me!

if ad4 is greater or less than 30 then ad4 text is red, if ad4 is equal too 30 then text is green

View 14 Replies View Related

Getting Value Depending On Changes Of 2 Cells

Jul 10, 2014

I have an issue with this for a couple of days actually..

I have to find in a certain date "column C" if User "column D" changes from the initial user's number to another one to gave me the first half sales of the day and then obtain the other sales of the other part, this must reestart again when the date changes.

I have to know what was the sales on the two parts of the day and then in column Q assign "Manana", S1 Cell or R1 Cell for second part of day

The User "column D", can be diferent each day.

I attach the excel data to get a better explanation on this.

Mov may-jul FARMAX.xlsx

View 7 Replies View Related

Conditional Formatting - Highlight Color Of Text In Particular Cell Depending On Value

Dec 22, 2013

I would like to know the formula to highlight the color of text in particular cell if the value of the particular cell ( value is text ) is so and so.

For Ex: If the J6 is "Vacation" then the J1 text should be in red color.

View 5 Replies View Related

Colouring Many Cells, Depending On The Value Of One

Mar 28, 2007

I'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be.
I then want to repeat this for about 200 rows.

View 7 Replies View Related

Display Text After A Common Starting Text?

Jan 23, 2013

I have a column of locations where all the locations start with 'SHIP TO - ' followed my the actual location which can be of varying lengths. How can I display everything after the 'SHIP TO - ' in an adjacent column. My instinct is to use a RIGHT Function, but I don't know how to get that to only display the variable length text after the 'SHIP TO - ', e.g. in another column I want this

SHIP TO - ABC COMPANY
SHIP TO - A VERY LONG LOCATION
SHIP TO - HERE

to show

ABC COMPANY
A VERY LONG LOCATION
HERE

View 6 Replies View Related

Conditional Formatting Depending On Two Other Cells?

Jan 27, 2014

On the attached spreadsheet, is there a way of highlighting a cell because it is a greater number than two other cells?

Can cell C3 be highlighted green because the number is greater than G3 and K3?

Can cell E3 be highlighted green because the number is greater than A3 and I3?

Can cell K4 be highlighted green because the number is greater than C4 and G4?

View 8 Replies View Related







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