Asigning a numeric value to text.

Hi All,

I need to assign a numeric value to a text string, so when the end use
selects a particular text from a dropdown list it returns a numeri
value to be used in a formula. Tried everything I know, need help
please

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

0
8/24/2004 4:00:00 PM
excel 39879 articles. 2 followers. Follow

2 Replies
585 Views

Similar Articles

[PageSpeed] 38

Select some out of the way cells and assign RangeNames to them that are the
same at the TEXT selections in your dropdown list.  Then type the numbers in
each box you wish each of them to represent.

Then a formula like   =A1+RangeName   would use the RangeName in the
formula, but return the number in the result.

hth
Vaya con Dios,
Chuck, CABGx3




"skyriakidis >" <<skyriakidis.1bi2ty@excelforum-nospam.com> wrote in message
news:skyriakidis.1bi2ty@excelforum-nospam.com...
> Hi All,
>
> I need to assign a numeric value to a text string, so when the end user
> selects a particular text from a dropdown list it returns a numeric
> value to be used in a formula. Tried everything I know, need help,
> please.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
croberts1 (105)
8/24/2004 4:37:29 PM
Hi

if you set up a table on another sheet with your text string and the numeric
equivalent, you can use the vlookup function to return the associated
value - either to a cell or directly into the formula

say your drop down list is in sheet 1 A1 and the table is in sheet 2 A1:B100
and you want the numeric value returned to sheet 1 B1, in sheet 1 B1 type
=IF(A1="","",VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0))

or if you want the put the numeric in a formula in B1 you can do something
similar to this
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:B3,2,0)*1.1)

or whatever the formula is

hope this helps
Cheers
JulieD

"skyriakidis >" <<skyriakidis.1bi2ty@excelforum-nospam.com> wrote in message
news:skyriakidis.1bi2ty@excelforum-nospam.com...
> Hi All,
>
> I need to assign a numeric value to a text string, so when the end user
> selects a particular text from a dropdown list it returns a numeric
> value to be used in a formula. Tried everything I know, need help,
> please.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
JulieD1 (2295)
8/24/2004 4:38:55 PM
Reply:

Similar Artilces:

my column headers has changed to numeric from alphabetcal order #2
I have come across strange problem ..my column names has chnaged to numeric from alphabet and now none of my formulaes work as all the cell names show up as R1C1 , R1C2 and so on... Anyone knows what have i messed up ??? Hi Annie, Try: Tools | Options | General Tab | Uncheck R1C1 reference style. --- Regards, Norman "Annie" <Annie@discussions.microsoft.com> wrote in message news:1FE6319D-59BD-4BEE-9C77-B8B97AC78CEF@microsoft.com... >I have come across strange problem ..my column names has chnaged to numeric > from alphabet and now none of my formulaes work ...

interpolated values
Column A Column B Column D 4.99 3650 6.500 5.06 3940 6.450 5.12 4230 6.400 5.19 4520 6.400 5.26 4810 6.450 5.33 5100 6.500 5.39 5430 6.500 5.46 5760 6.400 5.53 6090 6.350 5.60 6420 6.300 5.68 6750 6.200 5.73 7100 6.150 5.80 7450 6.150 5.87 7800 6.100 5.93 8150 6.050 6.00 8500 6.000 6.07 8850 6.150 6.14 9200 6.350 6.21 9550 6.450 6.27 9900 6.470 6.34 10250 6.900 6.41 10650 6.950 For the input values of column "A" I am having respective out put values in column "B". now for any input number between "4.99" to "...

Converting Text to Number in 2000
I have a spreadsheet that has a sum formula at the end of a column. Th problem is that the last 7 or 8 values being summed seem to be text. However, if you go to format cells they show up as numbers. In Offic XP you can simply click on the Green arrow in the upper left han corner and select convert to number. This fixes the problem and all i well. In 2000 this function is not available. Is there a way t convert these values without having to retype all the values in? Thanks Bdun -- Message posted from http://www.ExcelForum.com Select the cells. Give them a number format. Data/text to ...

Pivot Table Filter values
Is there a way to "read" what values you have selected for a Pivot Table filter in Excel-07? I have Report Filter(s) where I may select multiple values for any given filter. Being that this is a Report Filter, the values are not displayed in the data. For instance, say I have a Report Filter which contains 12 months (Jan - Dec). If I select Jan, Feb & Mar in this filter, the pivot table then reflects this data. However, short of clicking the drop down to see which items I've selected, I have no way of knowing what months are being displayed. I just want to be able to ...

How does text overflow work on added pages?
I reduced the size of the text boxes on 30 existing pages and added 10 additional pages to hold the remainder of the text. How do I get the text to overflow to these added pages? Link them Click on the box with the text, then on the icon that looks like a chain, it will turn into a puring pitcher. Click on the next text box you want to use. Keep going. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "lericks5" <lericks5@discussions.microsoft.com> wrote in message news:23349D52-F7B5-4A9D-A0D...

Grouping and Stringing Values Together
I have a table that contains information from people where people were allowed to make multiple responses. Know I would like to group this information and string it together. The table looks something like this (two fields). RespID Comments 1 Dogs 1 Cats 1 Rats 2 Cats 2 Horses 3 Monkeys 3 Elephants I would now like to create a table that would look like this. RespID Comments 1 Dogs, Cats, Rats 2 Cats, Horses 3 Monkeys, El...

When Printing: Text comes out as jiberish
Hi All, When I print from visio 2007, all of the text prints as jiberish. I tried two different printers with PCL drivers; same results. Any help would be great. Thank you! Can you give a few more details? What are you trying to print and how is it coming out? Is this the first time printing from Visio? Do you have any language packs installed? John... Visio MVP Need stencils or ideas? http://visio.mvps.org/3rdparty.htm Need VBA examples? http://visio.mvps.org/VBA.htm Visio Wishlist http://visio.mvps.org/wish_list.htm "mikeg" <mikeg299@yahoo.com> wrote in ...

how do i find values on a line graph in Excel?
i made a plot graph connected by a line. now i need to find values on that line. how? thanks. Two possible ways: 1. if you have the formula for the line, use it go calculate the reading. 2. If you have sample points on the line, but no formula, then you can use the FORECAST() function. If you have two points [x1,y1] and [x2,y2] and you also have a third x3, then FORECAST() will calculate y3 for you. The neat thing about FORECAST() is that it works equally well if x3 is between x1 and x2 or is outside of the range. -- Gary''s Student "sorrelponie" wrote: > i...

Copy Specific Values from Table to Report
I don't know if this is the proper group to post this message into, but I'll start here and see what happens. To try and "simply" explain my request...... I know this can be done, as I've seen the results, but I can't figure out how to do it. I want to use the Report function of Access to create printed forms. I would have different reports, named A, B, C, D, and E for this example. The information I want to have printed on the report(s) comes from one specific table. On report "A" I want to have the values printed that are in rows 2,3, and 5 of "col...

Text want wrap in text box
I can't get the text to wrap in a text box, word wrap is set to true, I put some text boxes on a sheet today and the text will not wrap, I did this yesterday and it worked fine, I can copy the ones from yesterday and paste them on a new sheet and they work, any ideas? I have rebooted but this did not help. Using Excel 2000 (9.0.6926 SP3) Thanks Paul B If you're referring to a Control Toolbat text box make sure that the MultiLine property is True. -- Jim Rech Excel MVP "Paul B" <pbridgesnews@uga.edu> wrote in message news:%23ovLUkuLEHA.2456@TK2MSFTNGP12.phx.gbl......

Add remark to listed values of fields on report 05-24-07
I made a report with 6 subreports on it to show the total count of the variable values of 6 fields in a table. It's briefly illustrated as follows: Total No. of participant: 50 (Records in the table) Occupation Category: ( a Field in the table) Education 15 Medical 23 Business 12 Registration: ( a Field in the table) Email 18 Phone 12 Other 20 My boss wanted me to include a remark next to the count for the listed value of ea...

copying text frames
I realize that when I try to copy a connected text frame from multiple pages, the actual text doesn't get transferred, only the frame, as I have to copy one page at a time. Is there a way to do this? I need to copy my multiple- paged Publisher document into Microsoft Word. Select the Text Frame and go Ctrl+A so all the text is selected, then Ctrl+C to copy the text. That should copy all the text in the connected Text Frames. -- The US should free all those illegally held prisoners they are torturing, abusing and denying human rights being held at Guantanamo Bay. ...

Vlookup based on a unique "Text" occur
My vlookup range starts based on a unique "Text" occur once in a cell in B:B and end when the same occur once in a cell in A:A Within this target range of rows there are unique texts like text-1,text-2, text-3… and so on belongs to the "Text" and they are in A:A but unevenly spaced. I need vlookup values located in row of col C:C. Below is example. "Text" Text1 50 Text2 60 Text3 45 Text4 50 Text5 20 Text6 80 "Text" Thanks for any help to resolve this ...

Excel Blank Value Import
Hi , I am trying to import Excel File to MS Access using Visual Basic. The problem I encountered is as follows : When I import an excel file, if the first row of an excel file contains an empty cell then thereafter the data in that particular column doesn't get imported! I am using OLEDBJET 4.0 to connect and retrieve the data from Excel Sheet. It would be great, if anybody can post their thoughts on it! Thanks, Vijay -- vgurusa ------------------------------------------------------------------------ vgurusa's Profile: http://www.excelforum.com/member.php?action=getinfo&...

text format? Not Really!
Why, when I make an entry into a form field bound to a table source that is Tex format, does it automatically calculate what I entered? I need to enter driver's licence numbers, and here, they have dashes in them, but instead of "1100-100", it will store "1000", calculating the first number *minus* the second number. I don't think I can use an input mask, because the numbers vary in size. Hi, I have made a new table with a text field, I type in 1100-100 and it stays as 1100-100. I can close the form and open it again and it is still showing 1100-100 Jeanet...

DateTime Null Or Minimum Value
Hello, I am working on a authentication system and I have the following: create table dbo.Users ( Id int identity not null, LastLock datetime not null, LastLogin datetime not null, LastPasswordChange datetime not null, LastReset datetime not null, Username nvarchar(40) not null constraint Users_Username_U unique, constraint Users_PK primary key clustered(Id) ) -- Users When a user opens the account there was never a login, it was never locked or password changed. So should I insert in this fields a Null value or a "SQL Minimum Date". What is yo...

Excel keeps wrapping cells with text incl. alt/enter
I have a cell with multiple lines (alt/enter line breaks) in a standard row where word wrapping is disabled. So I see only the first line and that's how I want it. Now, when I edit this cell the word wrapping gets automatically enabled (checked box) and row height gets adjusted to fit the text. It seems alt/enter breaks auto enable the wrapping, but what I need visible is a clean one line row no matter what text I put in. Any solution/workaround? I'm on excel2000. thanks -- Peter -- higgs ------------------------------------------------------------------------ higgs's Profil...

Excel chart
I am trying to create a column or line chart where a value for 9 variables are given for each month of the year. My problem is that the values are mostly between 0 and 40. I have one value though that is 280 and this makes the chart not really legible. Is there any way to have the scale on the y-axis showing 5 as major unit up until 50 and after that 50 or 100? On Sat, 5 Jun 2010 03:58:02 -0700, Ann <Ann@discussions.microsoft.com> wrote: >I am trying to create a column or line chart where a value for 9 variables >are given for each month of the year. My problem i...

Table fields:- Setting default values to be based on other field values
Greetings, Is it possible to define at a table design level that a default vaule of a field is based on other fields? For example Field "Name" = Field "FirstName" + "SecondName" Thanks Rob It's not possible and, in fact, wanting to do something like that is usually an indication that the table hasn't been properly normalized. In your example, Name shouldn't exist in the table: it's redundant (since it's based solely on values in other fields of the same row). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails...

Charting cells with no values
I have am using a chart which takes source data from a group of cells. these cells have a formula along the lines of: =IF(A1"","",=A1*20) When they return a value of "" (i.e. an empty cell) the chart still treats the value as 0 and plots it on the graph. I have made sure that the 'plot empty cells as' option is set to 'Not plotted (leave gaps)'. Completely empty cells are not plotted on the chart but cells which contain the formula above and still remain empty are still plotted on the chart as 0. I want these cells to be ignored on the ...

Substitute each instance of "B"in a range with value in Col A
need to substitute each instance of "B" in a range B4..g24 with the value in the Corresponding row, Col A. -- Al You could use a formula like =SUBSTITUTE(UPPER(B4),"B",$A4) if you want "B" and "b" replaced if you only want "B" replaced =SUBSTITUTE(B4,"B",$A4) copy across five columns, then down, once done you can copy and paste special as values and replace the old table -- Regards, Peo Sjoblom (No private emails please) "AMK" <AMK@discussions.microsoft.com> wrote in message news:35BC9A2F-1BFF-4568-AF73-...

Text box background color
I would like to change the background of a text box based on whether or not a corresponding record exists in another table. Is that possible? Tara wrote: >I would like to change the background of a text box based on whether or not a >corresponding record exists in another table. Is that possible? Probably. Without any more info than that, all I can say is to try modifying the form's record source query an outer join to the other table. Then you can pick up a field from the other table and test that for Null in Conditional formatting to display a different colo...

auto resize text without resizing cell in excel
format|cells|alignment tab|check "shrink to fit" Ferd wrote: -- Dave Peterson ...

copy range of unique value
Hi, i use Excel 2007 and i have a strange behavior with a particular thing that i do. quite often i have list of values with several times the same values. e.g.: 2,3,8,5,4,3,9 from cell A1 to A7 when i use the function: Data => Sort & Filter => Advanced => Copy to another location, list range $A$1:$A$7, copy to $D$1 and checked Unique records only. i correctly get 2,3,8,5,4,9 it means second times that 3 is found, it is removed from the new list. i have also a good result with only alpha value and only numerical values. my main problem is when cells have ...

Lookup unique values & calculation
This is the first time I am using this forum. Any help on this o pointing me on the what kind of function to use will be greatl appreciated. Here is the problem in detail. Column A contains compan names. Column B will have date. Column c will have a code f1, f2, f till f15. column D & E will have numbers or blanks. Each company nam will have its own f1, f2 f3 in column c. All codes for differen companies will have values in 2 columns (D & E)on which I will be doin calculations. My problem is to find certain code or codes for on company, based on that, do calculations. The calculati...