Populate data in cell by looking at another cells data

Hi Everyone,

Hope I find you well.

I'm not even sure how to go about this, so I hope that 
someone can shed some light.

I have a serial number in one cell eg '80199DD270238' 
where 'DD' is code for another value, in this case 'DD' 
= 'BLUE'.

How can I automatically populate a cell with the 
value 'BLUE' by looking at the serial number.

Many thanks for any help you can provide.

Best Regards

Gazza
0
anonymous (74722)
5/24/2004 11:36:24 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
613 Views

Similar Articles

[PageSpeed] 25

Hi
if the characters are alsways at the same place use
=IF(MID(A1,6,2)="DD","Blue","other color")

if they could be at different positions tryx
=IF(ISNUMBER(FIND("DD",A1)),"Blue","other color")

>-----Original Message-----
>Hi Everyone,
>
>Hope I find you well.
>
>I'm not even sure how to go about this, so I hope that 
>someone can shed some light.
>
>I have a serial number in one cell eg '80199DD270238' 
>where 'DD' is code for another value, in this case 'DD' 
>= 'BLUE'.
>
>How can I automatically populate a cell with the 
>value 'BLUE' by looking at the serial number.
>
>Many thanks for any help you can provide.
>
>Best Regards
>
>Gazza
>.
>
0
frank.kabel (11126)
5/24/2004 11:46:31 AM
Hi Frank,

Thanks for that, it works great.

Expanding on that a bit more, is it possible to have 
multiple IF statements so that I can check for several 
different codes within the serial number and then have 
several different values for each of the codes?

Cheers

Gazza
0
anonymous (74722)
5/24/2004 3:31:24 PM
Hi Frank,

It's OK, I've sorted it. Thanks for your help.

Best Regards

Gazza


>-----Original Message-----
>Hi Frank,
>
>Thanks for that, it works great.
>
>Expanding on that a bit more, is it possible to have 
>multiple IF statements so that I can check for several 
>different codes within the serial number and then have 
>several different values for each of the codes?
>
>Cheers
>
>Gazza
>.
>
0
anonymous (74722)
5/24/2004 3:42:41 PM
You can nest If functions within one another in the Spot he has "othe
color" just enter a new if function.
You could also use a lookup table.  Even have the information o
another tab that has the lookup table.

Frank?
How does this the ISNUMBER Function work since "DD" is not a number???

Michae

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

0
5/24/2004 3:50:01 PM
I'm not Frank, but here's his formula:

=IF(ISNUMBER(FIND("DD",A1)),"Blue","other color")

So =isnumber() isn't testing the DD value at all.  It's testing whether the
=FIND() function returned a number.  (=Find() returns a number if it finds the
string.  It returns #VALUE! (an error) if the substring isn't found.

And there's a function called =search() that is not case sensitive.  DD, dD, Dd,
dd would all be found with Search.  Only DD with Find.



"daniels012 <" wrote:
> 
> You can nest If functions within one another in the Spot he has "other
> color" just enter a new if function.
> You could also use a lookup table.  Even have the information on
> another tab that has the lookup table.
> 
> Frank?
> How does this the ISNUMBER Function work since "DD" is not a number???
> 
> Michael
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
5/24/2004 11:03:43 PM
Reply:

Similar Artilces:

Plotting data in Excel #2
Is it possible to generate a cell range mathmatically using formulas and "Concatenate" in one reference cell, and then use the contents of the cell to define the data range for an X-Y plot? In other words, instead of manually selecting the data range to be plotted using a pick box, I want the reference the contents of a cell that has computed the data range. Thanks -- James ---------------- 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" button in the message p...

how to use cursor to navigate active cells
With our Excel 2003, I could navigate to different cells by using the cursor keys. The cursor up key moved the active cell up. Left, right and down moved the active cell in the appropriate direction. I could select multiple cells by holding the shift key down and using the cursor keys to start selecting large groups of cells. Now, with Excel 2007, the cursor keys move the sheet around in the window, instead of moving the active cell in the indicated direction. I have found no way to select multiple cells. How can I get my Excel 2007 cursor keys to move the active cell, instead of moving t...

How do I stop text from automatically filling another text box?
When I type in one box, the same text appears in another box. How do I unlink them? Someone answered my questions but the link isn't working so I can't see the response. Please answer again. Edit, Undo Synchronize. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Chicago" <Chicago@discussions.microsoft.com> wrote in message news:650076B3-99E6-4043-993C-7171FA4D0396@microsoft.com... > When I type in one box, the same text appears in another box. How do I > unlink them? > > Someone answered my...

Condition is true in one cell, give me same condition in another
Have a problem. See below. If a condition or value is true in one cell, I need the same conditio or value to appear in another cell. If cell A1 = "N", then give me a "N" in cell C1. Problem is, I need to write the formula in cell D1 due t specifications in workbook. Any Ideas? T -- Message posted from http://www.ExcelForum.com Hi not possible with formulas. Formulas can only return a value but can't change the values of other cells. -- Regards Frank Kabel Frankfurt, Germany > Have a problem. See below. > > If a condition or value is true in one cel...

Typed Data Set and relations
I've a fairly complex schema which I'm trying to create a typed data set for. I've had a number of issues to date but it is sort of working (e.g. elements are not considered nullable unless minOccurs="0" is present. If not the codegen:nullValue attribute is ignored). What I'm seeing at the moment is that if I do NOT add in key and key ref relations, the cs generated puts in sensibly named relations with appropriate accessors. But they don't work. If I put in explicit key refs I get pairs of relations defined for each 'real' relation. The ones added c...

Cells with Validation lists attached
How can I indictate that a cell has a valaidation list attached to it without having to select the cell...so that if someone views the xls they can tell which cells have a validation list associated without having to actually select the individual cells? edit/goto special, validation, then give it a color...? Bob Umlas excel MVP "Mike" <Mike@discussions.microsoft.com> wrote in message news:49AFC808-DE74-4E39-9978-893352BE288E@microsoft.com... > How can I indictate that a cell has a valaidation list attached to it without > having to select the cell...so that if som...

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

data range props
I have a problem with external data. In the Data Range Properties, under Data Layout. There are 3 options after 'If the number of rows in the data range changes upon refresh:' 1 - Insert cells for new data, delete unused cells 2 - Insert entire rows for new data, clear unused cells 3 - Overwrite existing cells with new data, clear unused cells. I want option 3 to be the default oprion but it always defaults back to option 2. Is there any way I can change this.?? Cheers Baz ...

Import data from Access to SQL
Hello everyone. I have an instance of SQL Server 2008 Express on my PC, I have need to export tables from Access 2007 to SQL. I did successfully migrate (table access with unique ID field primary key) with SQL Server Migration Assistant for Access 2008, I rechecking the tables in SQL and it's all ok. Linking tables in Access. I have saved import (from text files) that work without problems with Access tables, when I go to do one of these imports, at some point the message "Unable to register changes. The entered value violates the settings for the table or the list...

refer to data on multiple worksheets using hlookup/look up data on many worksheets?
Hi I have data on many worksheets in the same workbook. Is it possible to look up data on multiple worksheets using Hlookup, which means can the second argument in the formula refer to multiple sources? If it is not possible, is there any other way I can do so? I would like to select and display data according to a fixed order, whereby the data is located in one of many worksheets in the same workbook. For eg, I have stock returns of many firms in many worksheets. Say I would like to display in a single worksheet the returns of Firm D, Firm Z, Firm R; whereby the data of these 3 fir...

can I make a bell curve from this data?
A colleague at work asked if I could put some simple Excel data into a bell curve. I don't know what I'm doing, math-wise, and I don't know if my data even lends itself to a bell curve. Here it is: We have 19 cars in our division. There are usually 3 cars that are driven a lot and 3 that don't get driven much, so we periodically rotate the highs and lows so that mileage somewhat evens out. So what I have is the annual mileage for each car. My colleague is hoping this will look great on a graph. Can this be put into a bell curve format? Or would some faux work-around make it l...

transpose 3d cells to a column in single workbook
I would like to extract 3d cells from a set of worksheets into a single column on another worksheet within the same workbook. How can I do this? Insert a worksheet named Summary, then select the 3D range, and run the macro below. HTH, Bernie MS Excel MVP Sub TryNow() Dim mySht As Worksheet Dim dataSht As Worksheet Dim myRange As Range Dim myCell As Range Set dataSht = Worksheets("Summary") Set myRange = Selection For Each mySht In ActiveWindow.SelectedSheets For Each myCell In mySht.Range(myRange.Address) dataSht.Range("A65536").End(xlUp)(2).Value = _ myCell.Value N...

How do I validate data using different lists based on the data in.
How do I validate data using different lists based on the data in another cell? There are instructions here for dependent data validation lists: http://www.contextures.com/xlDataVal02.html Shannon wrote: > How do I validate data using different lists based on the data in another cell? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

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

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

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

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

Creating a chart using data from multiple worksheets
I am trying to create a chart using data from multiple worksheets within one workbook. Does anyone have experience in doing this? I would suggest consolidating / referencing the data from the different worksheets into a single worksheet. Once that data is in a single worksheet, you can create your chart. You can use different worksheets - just my experience that it's easier to put all of the data into one place. -- John Mansfield http://cellmatrix.net "Rose" wrote: > I am trying to create a chart using data from multiple worksheets within one > workbook. D...

Error for multiple users merging data from Access into Word 2003
Hello. I have multiple users who access a Microsoft Access database. The issue I have is that when a user is in the database, a different user is unable to merge data from that database, as Word states that the database file is already in use (which is it). When no one is in the database, all users can merge without issue. When I try to merge data from the database already in use, the error message is as follows: "Test connection failed becuase of an error in initializing provider. Could not use "; file already in use". Is there any simple way that I can make Word '...

Formula: values in one column based on corresponding data
My goal is simply this - I want to add a series of values (hours and minutes) worked on specific projects within one months time. Now, the month/day/year is listed in a separate column. Thus, one column lists all the hours and minutes used to complete a project and now I want to add up those values for a specific month's time. The reason is so I can explain how much time was spent doing a specific task for various cases. I believe the formula starts with "=sumif(" but after that I simply do not know. SUMIF won't suffice as you have multiple conditions to satisfy fo...

Moving Palm Desktop data to outlook
I have a new HP Ipaq which only runs Outlook for calendar and contact information. All of my calendar and contacts are in Palm Desktop software. I can move through export files, the contact information, but I cannot find a way to export from Palm and import to outlook, the calendar information. Does anyone have a solution, software utility or other method to move all of the calendar information from Palm desktop to Outlook Thanks JEV jverzella@comcast.com <anonymous@discussions.microsoft.com> wrote: > I have a new HP Ipaq which only runs Outlook for calendar > and contact ...

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

Help? My formula is displaying in my cell
Hi there - For some reason I have one cell (in a workbook where everything else i working) that when I type in the formula in the formula bar . . . th stupid formula is showing up in the cell. My formula is simply this: =Info!B13 Because I am taking text from a sheet labeled Info in cell B13. I'v done this for about a million cells in this workbook so far an everything else has worked. This one stupid cell is just showing m the formula. Can anyone give me some suggestions to fix that? Much appreciated! Thanks! Jennife -- Message posted from http://www.ExcelForum.com Hi Jennifer,...