inserting texts in cell based on conditions

Hi,

I would very much appreciate if someone could help me 
solving a problem, illustrated by the following example:

Column    A    B    C
1         1         "LB"
2              1    "DK"
3                        
4         1    1    "LB/DK"


If there's a 1 in column A, the corresponding cell in 
column C should get the text "LB" inserted into it.

If there's a 1 in column C, the corresponding cell in 
column C should get the text "DK" inserted into it.

If both column A and B have ones in them, the 
corresponding cell should get the text "LB/DK" inserted 
into it.

Haven't been able to figure this out and would appreciate 
any help and suggestions.

Cheers
Nic
0
nico1448 (1)
8/27/2003 6:23:09 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
925 Views

Similar Articles

[PageSpeed] 23

Nic,

Try   =IF(A2=1,IF(B2=1,"LB/DK","LB"),IF(B2=1,"DK",""))

regards,

JohnI


"Nic" <nico@alt.cz> wrote in message
news:08fe01c36c63$af8e3340$a301280a@phx.gbl...
> Hi,
>
> I would very much appreciate if someone could help me
> solving a problem, illustrated by the following example:
>
> Column    A    B    C
> 1         1         "LB"
> 2              1    "DK"
> 3
> 4         1    1    "LB/DK"
>
>
> If there's a 1 in column A, the corresponding cell in
> column C should get the text "LB" inserted into it.
>
> If there's a 1 in column C, the corresponding cell in
> column C should get the text "DK" inserted into it.
>
> If both column A and B have ones in them, the
> corresponding cell should get the text "LB/DK" inserted
> into it.
>
> Haven't been able to figure this out and would appreciate
> any help and suggestions.
>
> Cheers
> Nic


0
8/27/2003 6:54:51 AM
One way:

Put in
C1:=IF(AND(A1=1,B1=1),"LB/DK",IF(A1=1,"LB",IF(B1=1,"DK","")))
Copy down col C

Nic <nico@alt.cz> wrote in message
news:08fe01c36c63$af8e3340$a301280a@phx.gbl...
> Hi,
>
> I would very much appreciate if someone could help me
> solving a problem, illustrated by the following example:
>
> Column    A    B    C
> 1         1         "LB"
> 2              1    "DK"
> 3
> 4         1    1    "LB/DK"
>
>
> If there's a 1 in column A, the corresponding cell in
> column C should get the text "LB" inserted into it.
>
> If there's a 1 in column C, the corresponding cell in
> column C should get the text "DK" inserted into it.
>
> If both column A and B have ones in them, the
> corresponding cell should get the text "LB/DK" inserted
> into it.
>
> Haven't been able to figure this out and would appreciate
> any help and suggestions.
>
> Cheers
> Nic


0
demechanik (4694)
8/27/2003 7:08:15 AM
=IF(A1,"LB","")&IF(A1+B1=2,"/","")&IF(B1,"DK","")

--
Mike

Ref to "Nic" <nico@alt.cz> wrote in message news:08fe01c36c63$af8e3340$a301280a@phx.gbl...
0
mike22p (25)
8/27/2003 7:50:06 AM
Assuming it is acceptable to have zeros to replace blank cells, a
"binary" like set-up using VLOOKUP might be one possible way to
go.

This avoids having "indecipherable and hard-to-maintain" nested
IF()'s and also averts the limit faced for nested IF()s (albeit
there are ways to get around this limit).

Assuming the input cols are cols A to D

Set-up a vlookup table (sample below) in a 2 col range,
say in H1:I12, and name this range: List

(yes, a fair amount of one-time effort is required to set this
up. And you got to cover all the "binary" like permutations of
1's and 0's depending on the number of input cols involved. The
sample set below *doesn't* cover all permutations.)

0_0_0_0  LB
0_0_0_1  DK
0_0_1_0  LB/DK
0_0_1_1  AB
0_1_1_1  CD
1_1_1_1  EF
1_0_0_0  AB/CD
1_0_0_1  AB/EF
1_0_1_1  GH
1_1_0_1  IJ
1_1_1_0  GH/IJ
1_1_0_0  KL

Put in E1:=VLOOKUP(A1&"_"&B1&"_"&C1&"_"&D1,List,2,FALSE)

If A1:D1 contains 1,0,0,1 then E1 will return AB/EF

Copy down col E

Nic <nico@asco.cz> wrote in message
news:0f4f01c36d01$c5be6510$a001280a@phx.gbl...
> Big thanks to all of you, much appreciated. However, my
> example was simplified in that there are several columns I
> would have to check for value and writing IF for all
> combinations become tedious (is it n^2 -1??) Would there
> be another way of achieving this or will I have to bite
> the bullet and write a (rather) lengthy if statement?
>
> Cheers
> /Nic


0
demechanik (4694)
8/29/2003 9:20:31 AM
Reply:

Similar Artilces:

Entries in range that meet simple condition
I know this must be simple. But I simply cannot find information as to what I really want to do. We are talking about a VBA macro. I have single column range. (It is non-contiguous, 5 block of 10 rows. I'm not sure it matters.) I would like to search that range for entries where the offset(0,1) contains a value of 500 or greater. This for finding scouts that have sold $500+. I would like the results returned as a range or an array. Ultimately what will happen is that the results lookup will be transposed to a two-columnar form created by BSA. That range will be propagated by scrol...

Detecting cell fill color
Is there a function, or some VBA code that will detect that a user has applied a fill color to a cell? I want to reference a cell, and with a =IF function, apply a calculation to colored cells. Hi Tony There isn't anything that comes with Excel. However, have a look at this site for processing of coloured cells. www.xldynamic.com/source/xld.colourCounter.html HTH Michael "Tony Rice" wrote: > Is there a function, or some VBA code that will detect > that a user has applied a fill color to a cell? I want to > reference a cell, and with a =IF function, apply a &...

Counting Cells with Multiple Range Criteria (Excel 2003)
I'm trying to count the number of rows that matches multiple criteria in multiple ranges. As an example, I want to count the number of rows where both Column A and Column B have a date between 01-01-05 and 12-31-05. Where: Column A Column B 01-02-05 12-20-05 01-06-05 02-20-06 05-06-09 03-07-05 01-02-06 01-09-06 So, in this instance, I want it to count only row 1 because both columns match the criteria. I've tried a couple of different Countif statements, but I can never get it to count using the multiple criteria and ranges. Please a...

return different values based on 4 criteria?
Need Formula or macro to allocate the "Scheduled hours" based on Group Counts: If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Schedule...

How I can combine cells of different pages?
I mean how can I add, divide, mutliply, and so on figures of different cells for example, add a figure of the cell f4 of the page 1 with the cell g5 of the page 2. When you say page 1 & page 2 I assume you mean sheet 1 & sheet 2. You can apply mathematic functions for a cell using different sheets but you have to name the source i.e. =A1+Sheet2!A1. An easy way to do this is begin your formula on sheet 1 = A1+ and then go to the cell in the sheet where it resides, click on the cell and click on your active sheet. Excel will automatically place the name reference of the cell...

Data base Adventure Works
Hi all, I have installed the CRM 3.0 in Portuguese, but I don´t have data base. It exist this data base of "Adventure Works" in form of archive where I can import? Regards, Leandro Ruviere. Hi Leandro, There's information in the Implementation Guide regarding installation of the sample database "Adventure Works Cycle". See this url for the information: http://www.microsoft.com/technet/prodtechnol/mscrm/mscrm1/maintain/20_samdb.mspx Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://ronaldlemmen.blogspot.com/ "Leandro Ruviere&q...

If Conditioned Macro
Does anybody know if I can run a macro from inside an if condition, such as if((A1 + B1)< 50, run macro)? Any help or suggestions would be greatly appreciated. Thanks, Dave Hi no you can't. What you can do is use an event procedure. See: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany Dave wrote: > Does anybody know if I can run a macro from inside an if > condition, such as if((A1 + B1)< 50, run macro)? Any help > or suggestions would be greatly appreciated. > > Thanks, > Dave Ok. Thanks Frank! Dave >-----Original M...

How do I get more than 1 Text Watermark on the page?
Please help me!!!! I need lots of Text Watermarks to appear throughout the page. Noeleen Conlon <Noeleen Conlon@discussions.microsoft.com> was very recently heard to utter: > I need lots of Text Watermarks to appear throughout the page. Copy and paste? -- Ed Bennett - MVP Microsoft Publisher ...

Calculate cell reference
I have the following lookup which is working exactly the way I want: =VLOOKUP($C$4,Sheet2!$C$2:$M$19,MATCH("1M",Sheet2!$C$1:$M$1,)) The problem is, I need it to change it's lookup based on user input. If the user types 2 in a certain cell I need this lookup to change to: =VLOOKUP($C$4,Sheet2!$C$20:$M$37,MATCH("1M",Sheet2!$C$1:$M$1,)) If the user types 3, it needs to change to: =VLOOKUP($C$4,Sheet2!$C$38:$M$55,MATCH("1M",Sheet2!$C$1:$M$1,)) I can calculate the cell numbers easily enough: =2+B10*18-18 Which will give me the cell I need to reference, B10...

How do I scan and insert into Outlook 2007?
I want to insert a scanned directly into OL7. It does not seem to be an option in the insert menu to scan. A scanned what? State what you want to do in clear terms. -- Russ Valentine "John Hayden" <JohnHayden@discussions.microsoft.com> wrote in message news:0FC3B7E6-F179-42B2-A32D-99CBD7E9027F@microsoft.com... >I want to insert a scanned directly into OL7. It does not seem to be an > option in the insert menu to scan. John Hayden wrote: > I want to insert a scanned directly into OL7. It does not seem to be an > option in the insert menu to ...

unable to change data in cell(s)
This file has about 20 sheets. I have picked the first sheet, then did cntr and choose every other sheet. I was able to type a location address in the first sheet, and it did appear on all the other sheets in the corresponding cell. These cells were initially blank However When I tried to do the same with a cell that read "physician name" it would not work. I would type in "smith" and enter, but it would revert back to "physican name". I checked the formating of the cells. They are all set on general They are all unlocked (the file is password protected) Th...

FLIPPING TEXT
HOW DO YOU FLIP THE TEXT FOR AN IRON ON TRANSFER TO PRINT CORRECTLY Can your printer do a mirror image? Might try WordArt. Select the text box, copy, paste special as a .wmf, flip... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "MIA" <MIA@discussions.microsoft.com> wrote in message news:EA27CE71-2642-4A76-850C-6EBE2AC31BB1@microsoft.com... > HOW DO YOU FLIP THE TEXT FOR AN IRON ON TRANSFER TO PRINT CORRECTLY On Fri, 10 Feb 2006 18:06:28 +0000, MIA wrote (in article <EA27CE71-2642-4A76-850C-6EBE2AC31BB1@mi...

Auto Generate text field
I have a from that users can inout ingredients for recipes. I want the user to pick an ingredient from a drop down(which is already working). I also have a field that has an item number. I want the item number to auto generate base on the ingredient selected from the drop down. -- Lisa S. You have an item number field already and you want to generate another one? Also, if a new item number is generated every time a user selects an ingredient it's going to make that item number field useless. Or is your question how to populate a textbox with the already assigned item number for ...

http status code text message
CHttpFile::QueryInfoStatusCode() How do you get a text version message that you can display to a user? For example if it returns 404 "Not found." Etc. I'm not gonna code a giant switch for this, there's gotta be something in wininet or mfc that translates these into a message? ...

Conditional formatting #4
Is it possible to use a table of results to determine the formatting results in conditional formatting? I am wanting to use more than the allowed 3 conditions to format a series of cells. Say for instance I wanted the cell to format if the conditions were : H HN ..H ..NH 3.5/H ..3.5/H and so forth The list I have has 10 conditions in it and would like to use all 10 but as I am only allowed to use 3 I am struggling. Any help is appreciated. Goldie Here is one I sent in response to a similar post. Modify to suit right click on sheet tab>view code>insert this>SAVE Private Sub Works...

How do I to stop attachments turning to text?! Outlook2000 SR1
Hi I am using microsoft outlook 2000 SR1. It has suddenly started turning all attachments it sends into text! Anyone know how to stop it? Someone said something about "MIME" but I cant see any options that mention MIME... With thanks Ship Shiperton Henethe PS They tend to arrive in multiple parts too... "Shiperton Henethe" <shiphen@yahoo.com> wrote in message news:3fabd920$0$19533$afc38c87@news.easynet.co.uk... > > Hi > > I am using microsoft outlook 2000 SR1. > > It has suddenly started turning all attachments > it sends into text! >...

Merging Cells adding Carriage Returns
Hi everyone I've been sent a text file to import that contains (amongst othe things) addresses. These addresses are split over 5 cells and I want to merge them al into one cell per address so I used :- =B2&""&B3&""&B4&""&B5&""&B6 This worked great, but turned the address into a sinlge line. I reall want to add carriage returns at the end of each line to make it mor readable. Any ideas please? Thanks Coli -- Message posted from http://www.ExcelForum.com > This worked great, but turned the address into a sin...

How to set default cell formattiing to vertical alignment = top ??
How to set 'Alignment', 'Vertical:' cell formatting to be 'Top' as default format for all new worksheets ? -- akm Thanks again for your help. ---- Ah, the miracle of computers!... someone also said "To error is human, but to really screw up it takes a computer!" Take a look at "default workbook" in XL Help. In article <563D9BF8-D599-43E6-9383-AB68FC7CBF01@microsoft.com>, akm <akm@discussions.microsoft.com> wrote: > How to set 'Alignment', 'Vertical:' cell formatting to be 'Top' as default > format for...

Answer: How to create pop-up text on drawings saved as HTML
I know many people have asked this question, and it has been partially answered previously. Here is a more complete explanation. Summary: You can add the User.visEquivTitle cell to your shape and provide a string that will display as a tooltip in the web output. The shape must also have at least one custom property for the tooltip to be visible. Steps: 1) Select the shape in your drawing that you want to add a web tooltip for 2) Go to Window > Show Shapesheet 3) Go to Insert > Section, check User-defined cells and click OK 4) In the newly created section, click on the row name Use...

Inserting XML Data Into a New Table FROM VB.NET
Can anyone help me figure out how to insert data into SQL Server when a table to hold the data does not yet exist. I am able to insert data into a existing table in the following fashion. But what if the table does not exist? Appreciate any help I can get. Private Sub InsertData() Dim objSQLConn As New SqlConnection("SERVER=.;UID=sa;PWD=;DATABASE=MyTest;") Dim objAdapter As SqlDataAdapter Dim objDataRow As DataRow 'Dataset row Dim objDBRow As DataRow 'SQL Server table row Dim objDSXML As New DataSet Dim objDSDBTable As...

Autofit problem with merged cells
I'm using excel 97 and there apears to be a bug in which Autofit does not work on any cell that has been merged wih a cell that is adjacent to it, the only way to make all of the text visible is to manually resize the row. I have found a macro that solves this problem however i would prefer not having to run a macro each time i type in a merged cell. Does anyone know if microsoft has a patch or download that resolves this issue? Most people feel that merged cells create more problems than they solve. I think maybe you've just provided more evidence for that argument. On Mon, 1...

Is it possible to display text in charts?
I am creating graphs for results from a survey. Part of this is to graph each individual's response to every question. If a person did not answer a question, I would like "N/A" to appear in the data for the chart. I am not using formulas -- the numbers are typed directly into the spreadsheet. Is there any way to have the text "N/A" display in the chart data? Currently, if I enter text in the corresponding cell, it displays as "0" in the chart data. Thanks! Janine, You can try applying a custom format to those entries that are coming across as zero....

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

Changing text to date format
Hello I have imported a text file to an excel sheet and the date fields have been formated as text. I have not been able to convert the cells back to date format. When I do, the data turns into ########### Any ideas as to why this is happening and what I can do to change the data to date? Hi One possibility - depending on what is actually in your cells. Copy a blank unused cell. Select your range and Edit / Paste Special / Add -- Andy. "Ron B" <anonymous@discussions.microsoft.com> wrote in message news:3244BC70-04FA-493C-AD39-48B70F289935@microsoft.com... > Hello, &...

Changing the cell format doesn't change existing cell content
When I import data into Excel 2003, the numbers need to be reformatted so that they can be calculated. But when I highlight the range and reformat it to a number format, it doesn't register the change. I have to click inside the cell contents (hit F2) for it to 'accept' the new format. This complicates things when there is a lot of data. Any ideas? Thanks for any help. This could make things faster for you. After importing the data, choose another cell that is formatted as General or a number and enter 1 (the number one). Select the cell with the 1 in it and us...