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
1007 Views

Similar Articles

[PageSpeed] 21

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:

Can you insert a picture into a specific cell?
Or do they only "float" on the page. Marc They float over the worksheet, but you can adjust the size so that it seems to fit a cell exactly. If you insert a picture, you can hold the alt-key down while you move/resize the picture. When it gets close to the edge of a cell, it'll snap-to that edge. Marc wrote: > > Or do they only "float" on the page. > > Marc -- Dave Peterson ...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Referencing cells across sheets
Hi, I've created a workbook with three sheets. The first three columns of sheet 2 and sheet 3 are referenced to the first three columns of sheet 1. What I'd like to do, is set the workbook up so that if I insert a row before, between or after referenced rows in sheet one, a row will also be inserted, and referenced, in the corresponding place on sheets 2 and 3. Does anyone know how to do this? Also, if no data is entered into a cell on sheet one, the value shown in the corresponding cells of sheets '2' and '3' is '0'. I need those cells to remain bla...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

How to unlock locked cells by MACRO?!
I have locked Sheet and I need to change particular cells. But when my macro wants to change it => ERROR :/ Is there any way to unprotect these particulary cells by macro and change them? - after that action, of course, I want to lock them again. Please help me, Pedro Just unprotect the sheet, do the work, and reprotect the sheet. Kind of like: activesheet.unprotect password:="yourpassword" 'your code to change stuff activesheet.protect password:="yourpassword" Petr N�me�ek wrote: > > I have locked Sheet and I need to change particular cells. But when m...

parameter for text
In Access 2003. In a form I have a parameter to search for a word at a a time Like [Froms].[Form].txtWords]&"*" which works well. .. In order to enter more than two oarameter separated by commas in txtwords I have been tryin the Str function like this "Instr([txtWords])" "Instr[txtWords]" The query displays no records at all. Could you suggest me how to do it Many thanks I would suggest separating by space and using this -- Like "*" & [Froms].[Form].txtWords] &"*" Remember that when you type in 'bell...

Shortcut for inserting names?
Hi, I am building many formulas, each with many named cells as part of it. Currently I have to go to Insert | Name | Paste with the mouse each time I want to insert a name. I looked in Walkenbach's Excel Bible but couldn't find any mention of a keyboard shortcut or other shortcut. Is there one I could take advantage of? I've got Excel 2002. Thanks, Jamie ...

How to add a button to restore all altered cells original values?
I want to add a reset button to an excel spreadsheet that will restore the values of all changed cells to the original saved ones. Any help would be appreciated. Thanks Dawn Hi this would require quite some VBA code as you somehow have to store the original values for example on a separate hidden sheet -- Regards Frank Kabel Frankfurt, Germany "Dawnybros" <Dawnybros@discussions.microsoft.com> schrieb im Newsbeitrag news:3340601E-16EE-4296-8F50-B0BAC18EA387@microsoft.com... > I want to add a reset button to an excel spreadsheet that will restore the > values of all ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

How do I bind a XAML text box control to a dataset?
Hello; I am new to using WinFx and I am having trouble figuring out how to bind a text box to a field in a dataset. I found an MSDN article: http://msdn2.microsoft.com/en-us/library/ms752057.aspx My question deals with the text box code: <TextBlock Text="{Binding Path=ISBN}" Grid.Column="1" /> How does this text box know to bind to the field "ISBN" in "myDataSet"? How does the control implement the dataset? Thank you Monty ...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

formula to have 0 to 6.5 only in ref to other cell val of 0<=6.5
...

If statment with two conditions
I have an IF statement with two condtions as noted below. I want it to work so that if column G has a y in it, it will be hidden, and also if column G has an N in it and column O has a 0 then it will be hidden. The second part doesnt work. Any thoughts? Sub Hide_new() Dim cell As Range Dim rngisect As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set rngisect = Application.Intersect(ActiveSheet.UsedRange, Range("G19: G4061")) For Each cell In rngisect If cell.Value = "Y" Then cell.EntireRow.Hidden ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Text box in Embedded chart is cut off
Hi. I am having a problem with an embedded chart that appears differently in word versus how it appeared originally in Excel. Specifically, the top of the chart (in excel) has a text box that divides the chart (it is a vertical bar graph) into subcategories (labels at the bottom are used to specify what each bar signifies; but these are taken from the associated cell range). Originally, the chart was labeled by a single text box, separated by spaces: Lifetime Use 30-Day Use Heavy Use Antisocial Behavior When this chart is linked in word, the text displays like this (tho&...

changing a cell of 60 files
Hi all I've 60 files and another one which summarizes all of them.. I've to put a day in the cell a1 and then I'd like to cut and paste that day in the cell a1 of the other 60 files without having to change all of them manually. I'd like to save and then exit every single file.The files are named 0001 0002 0003..and so on. I know that this is possible with a macro..but I've got a problem.. It's possible not to have the prompt which asks for updates of the file everytime I open one of them?? Thanks for the help Rossella Hi Rossella http://www.rondebruin.nl/copy4.htm ...