#### Populating a field conditional of the input of two cells

```Hello All,
I'm trying to write a simple formula that puts a specific answe
conditional of the answers supplied in two other cells. I have followe
the formula structure as I understand it, however microsoft Excel X
doesn't like the (and statements. Could anyone enlighten me on th
correct way of doing this?
Many thanks. (The formula is below)

> =IF(AND(F4=1,F6="XLPE"),"1",IF(AND(F4<>1,F6="XLPE"),"2",IF(AND(F4=1,F6="PE"),"3",IF(AND(F4<>1,F6="PE"),"4",IF(AND(F4=1,F6="PVC"),"5",IF(AND(F4<>1,F6="PVC"),"6",IF(AND(F4=1,F6="N"),"7",IF(AND(F4<>1,F6="N"),"8","Error")))))))

--
Message posted from http://www.ExcelForum.com

```
 0
12/30/2003 7:24:08 PM
excel 39879 articles. 2 followers.

1 Replies
653 Views

Similar Articles

[PageSpeed] 21

```Would this work?
=LOOKUP(F6,{"N","PE","PVC","XLPE"},{2,4,6,8})+IF(F4=1,1,0)

--
Don Guillett
SalesAid Software
donaldb@281.com
"R3df1sh" <R3df1sh.z9lnn@excelforum-nospam.com> wrote in message
news:R3df1sh.z9lnn@excelforum-nospam.com...
> Hello All,
> I'm trying to write a simple formula that puts a specific answer
> conditional of the answers supplied in two other cells. I have followed
> the formula structure as I understand it, however microsoft Excel XP
> doesn't like the (and statements. Could anyone enlighten me on the
> correct way of doing this?
> Many thanks. (The formula is below)
>
> >
=IF(AND(F4=1,F6="XLPE"),"1",IF(AND(F4<>1,F6="XLPE"),"2",IF(AND(F4=1,F6="PE")
,"3",IF(AND(F4<>1,F6="PE"),"4",IF(AND(F4=1,F6="PVC"),"5",IF(AND(F4<>1,F6="PV
C"),"6",IF(AND(F4=1,F6="N"),"7",IF(AND(F4<>1,F6="N"),"8","Error"))))))))
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
Don
12/30/2003 8:17:32 PM
 Reply:

Similar Artilces:

Passing Field Values
Hi I have added a new button on to "Sales transaction Entry" screen. How to pass the screen control values to the corresponding button's function (in VBA) Regards Guru You will need to bring up the window and add the "control fields" to VBA. Then you can reference them in your VBA script and pass them to whatever other code you have written. David Musgrave Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@nospam-microsoft.com Any views contained within are my p...

Need critical input on this
I have created the below code that actually works and compiles with zero errors and zero warnings. I would like criticism and input on it from all angles, First off is there a better way to do this. Does my code have problems etc. (granted I have left some error checking out to keep it brief) This all started when I wanted to change the font type and size in my FormView Listbox. I always try to keep everything within MFC since I want to concentrate on my stuff and not windows stuff. But everywhere I looked on the net for changing a font in a resource control had stuff setting the O...

Populating Excel from CSV
Hello all, I need to know how to populate an Excel template with data from a external CSV file. Let's say I have this nice pretty colourful templat that is far more pleasing to the eye than the usual chunk of importe CSV data, what are some methods to populate it with data from a CS file?? Any ideas or suggestions or redirections would be much appreciated. Thanks in advance : -- AJMorgan59 ----------------------------------------------------------------------- AJMorgan591's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2777 View this thread: http://www.ex...

Sum cells in different colors
Can you sum all the numbers in a column that are green only? I do not want to sum numbers in other colors. You need a VBA macro to do this. See http://www.cpearson.com/excel/colors.htm , -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "heater" <heater@discussions.microsoft.com> wrote in message news:51EADCED-9548-4260-B16C-174CD8751A31@microsoft.com... > Can you sum all the numbers in a column that are green only? I > do not want > to sum numbers in other colors. ...

Copying Conditional Formatting
I am trying to copy conditional formatting using formula instead of cell value. I cannot get it to progress down the column. I attached a file to illustrate. I have oversimplified the illustration but in it I would like to apply conditional formatting to column A to highlight the cell in Red if the number is higher that the cell to it's right in column B. I create a simple conditional format in A1 that says if A1 is greater than B1, highlight the cell in Red. That works. My question is how do I copy that format down the column? I tried copy and past special and every other techni...

Addding notes and rating fields to a report
I just started experimenting with creating some reports using vb.net 2003 and the SRS. So far getting most of the data on the report has been pretty easy but I cant seem to get certain data on there. I would like to have columns available for the notes and also rating as of yet I have not been able to find them. Any ideas? ------=_NextPart_0001_666CDF65 Content-Type: text/plain Content-Transfer-Encoding: 7bit <xxdcmast@discussions.microsoft.com> wrote: > I just started experimenting with creating some reports using vb.net 2003 and > the SRS. > > So far getting mos...

Sequence Not populated for the MO Numbers during Data Collection
When data collection is done for the MO Numbers by using the Data Collection Window under Manufaturing, the Sequnce look up does not show the whole list, it only shows NOTES, for the user to select and enter the necessary data. why does this happen in the case of some MO Numbers and in the others it shows all the sequnces as NOTES,MAKE READY, PRESS RUN, WASH UP, MAINTENANCE, REPAIRS, REWIND/INSPECTION, etc. Any information would be much appreciated -- Thanks AshaM It should show all of the router steps. Are some of your steps possibly marked to backflush labor? These steps would not...

lotus uses {?} for input, what does excel use (macros)
i can't figure out how to get the macro to stop for the input. what is the designation in the macro to wait for user input? Here are 2 methods Reply = MsgBox(Prompt:="Tell me what to do", Buttons:=vbYesNoCancel, Title:="Give me info") Namestring = InputBox(Prompt:="You name please.", Title:="Enter Name", Default:="Name Please") Mike "jmoore" wrote: > i can't figure out how to get the macro to stop for the input. what is the > designation in the macro to wait for user input? Check out VBA help on InputBox ex...

Can I protect data in a cell, but allow comments to be entered.
Hi All, I have an excel sheet in which I have some protected cells and unprotected cells. The protected cells are protected so that the forumla within them cannot be edited/removed, however, I would like all users to be able to insert comments in these fields. The edit objects selection when protecting the sheet allows for comment entry, but it also allows the user to alter the data in the cell. Any solutions/ advise on this would be greatly appreciated. Many Thanks Chris Sheet is not protected. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Co...

how do populate empty cells with the contents of populated cells .
I have this worksheet problem. The work sheet is thousands of rows long. One of my columns has cells not populated. I would like to get any empty cell in this column to populate itself with the contents of the next populated cell below it? Kind of a "find and replace" action where by an empty cell says to it self;"I'm going fill myself in with the next thing I find below me" I would greatly appreciate if someone can relate how to do this. Thanks Jim, Select the column, use Edit | Go To... Special , Blanks, OK. Then type an equal sign, press the down arrow...

Field inheritance from account to contact
Hi, I see that CRM 3.0 only inherits the main phone no. (telephone1 attribute) from Account to a new Contact to that Account. I cannot find the code doing that under field events, form events or view events. My intention is to duplicate that behavior for other fields (such as fax no.), so does anyone know how this is done by CRM for telephone1 attribute? Thanks Dan Dan, This is configured under Mapping - on the relationship between account and contact. Path is Customizations > Customize Entities > Account (in grid) > Relationships (in navigation pane) > Find the relation...

TASK: required field value when task is beeing completed
Hello, I'd like to prevent saving task record as completed if a specific field (eg. resultcode) is empty. I've created new field "Result code" which should be filled out by the user when he push "Save as completed" button. In customize forms -> Task -> Form properties ->events -> OnSave I can check if <crmForm.all.new_resultcode.SelectedText> is empty or not but this field should not be obligatory when the task is Open. I'd like to display alert if resultcode is empty only when user click button "Save as completed". Even when I che...

Increase the size of some fields in Inventory Mgmt.
How do I increase the size of some fields in the Inventory Mgmt screens (e.g fields like Item Description are not long enough). Do I just make the changes in Modifier? Or do I have to tinker with table fields too to handle larger Item Descriptions. Is there any other place I need to make changes to accomodate a long Item Description? Any help would be greatly appreciated. Thanks Surendra Do you mean the amount of characters you can enter in a field? Modifier is only for changing the window's appearance. The length of the fields is set in the database tables. Those fields shoul...

Outlook Express accessing mail from two computers and needing to transfer messag
I have a laptop and desktop and have hundreds of OE messages I want to get from one machine to the other. I have tried backing up and copying but OE wont recognise the files. is there a simple way other than re emailing all the files from one machine and then reading them on the other? Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in...

Autoplay for Pictures Not Populated
I inserted my XD card in to my card reader today and the autoplay pop up window asking what I wanted Windows to do was blank. For Video, Music and Mixed content the options are there but not for Pictures - it is just blank. I have tried running autofix.exe but this has not fixed it. Any ideas? Is this a Microsoft problem or has something else caused this? It worked fine until today. Thanks. Peachypumpkin wrote: > I inserted my XD card in to my card reader today and the autoplay > pop up window asking what I wanted Windows to do was blank. For > Video, Music and Mi...

City and State Populated by Zip Code
Does anyone know of a way to load a program that when you type in the Zip Code the City and State are automatically populated? I have seen a MSCRM add-in from a company called QAS (http://www.qas.com/). This is more than just Zip Code/City&State. It's a full address validator along with streamlined data entry. They have integrated with CRM via an ISV pop-up and it seems to work fairly smoothly. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On 15 Apr 2005 11:13:56 -0700, "Morgan" <tzeoli@att.net> wr...

Pass cell data to Query
Anyone know how to pass the contents of a cell to an sql query? I have a worksheet that I have connected to an SQL database that I want to query to get data. The worksheet is like an invoice. What I want is the user to input a product code and currency then the query to return the price. It sounds simple enough to do with a nested function but the price list is made complex by prices in different currencies so the level of nesting allowed is insufficient. I thought a database query would be a more appropriate solution but I can't find any information on how to do it in this way. -...

How can I match two spreadsheets by patients ID?
Hi there, This question is torchering me many days: I have two spreadsheets for the same patients. However these tables are different but complementary to each other. Both spreadsheets have unique Clinical Number. Is it possible to match the information from these two tables by Clinical number? Ex: Spreadsheet_1 ID Sample name Diagnosis Notes 123 RA234 ST RA Not for patient 456 RA456 TA GCA TA biopsy positive Spreadsheet_2 ID Date of Birth Sampling Date Investigator Treatment 123 10/01/1960 10/10/2005 Jo...

Blank field in form
I have a query that gets some criteria (City and State) from a form. I have the State field set up as a ComboBox connected to a local table I created. If a user was to type in a City (i.e. "Springfield") and then leave the State field empty, then the query would have no results. How do I make the query so that if one of the fields on the form is left empty it will run for all possibilities (i.e. look for all Springfields in any state)? It would help tremendously if you had provided the SQL view of your query. Without that I can suggest you might use a where clause like...

e-mail
Hi, I would like to make a mailing to 80.000 people. I don't want to use WORD for this because the performance. At the moment i can select userfields which i define in the template. The value of this userfield is for example 7, but the value 7 is related to a gif file. How can i make the relation in the template between the value 7 and the *.gif file ? Thanks a lot Regards Erwin Not quite clear on what you are trying to do. Are you wanting to put an image in the email template? -- Matt Wittemann http://icu-mscrm.blogspot.com "ESP" wrote: > Hi, > > I would...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

how to calculate the substraction between two dates
hi, i have two cells ,in which there are two dates. (for instance ,one cel l 08/02 ,the other 07/30) if the gap of the two dates is 2 days , something will be set to do. but it is difficult to judge the the substraction between two dates any good ideas. thanks Perhaps this might be of help .. Assume the *dates* "2-Aug-2004" and "30-Jul-2004" are input in A1 and B1, and A1:B1 is custom formatted as: mm/dd viz. it'll show as: In A1: 08/02 In B1: 07/30 Try in C1: =DAY(ABS(A1-B1))-1 Format C1 as: General or Number C1 will return: 2 i.e. the number of days...

Set Field Value Based on Entry in another field
Access 2003 and total noob here. Sorry - I can't seem to find anything that answers my question through searches here...maybe I'm too new so I don't recognize the solution when I see it. I have a form where users select their organization name from a menu. The menu is populated from a query against the organization table. The organization table has three columns (ID (pk), ORGNAME, ORGID). The ORGID is a two character acronym for the organization. I need to populate a hidden field on the form with the ORGID based on the selection made in the Org Name menu. How do...

need two email accounts for two phones on one computer
My wife and I have just bought an iphone each after a recent burglary. We are trying to maintain separate contacts. Apple advice was to set up a new user for my wife to stop us synching with each others contacts etc. I've et my wife up as a new user but don't know how to set up outlook with separate outlook account so that we can get our own emails and maintain our own contacts. I did manage to set up a replica outlook email account but it generally means we get the same emails in each account. Is it possible for us both to have a separate outlook installation under ...

from field upload file problem
www.icingpictures.co.uk win xp FP 2003 online order form url http://www.icingpictures.co.uk/online_order_form.htm Problems with online order form - upload files form field Everything on form works fine 100% except one aspect. Maximum file size it can upload. I can successfully upload up to 8 files simultaneously using the from providing the file sizes are small (under 2MB). If I try and upload either one file or a number of files and the combined size is around 2MB plus – I get a user name and password prompt – obviously customers using the form would not know this and th...