data sort of like

Hi experts

I like to have a tabel like this



      This is one 1
      This is two 2
      This is someother 5
      Text 4



that say a text that is assosiated with a number. Then (some where else in
the spreedsheet) I like to have a drop down wther I can choose between
("This is one"; "This is two", "This is..."), and depending on the choosen
text I will use the assosiated number in some calculations. Is this possible
with Excel?

e.g. if in the drop down is in cell A5, and there is  choosen "This is Two",
and in cell A4 says 55 like to write some thing like this, in cell A6:
"=A5*A4" and the the result should be 110

 Is this possible with Excel?

best regards
    Torben


0
1/19/2004 2:23:19 PM
excel 39879 articles. 2 followers. Follow

2 Replies
878 Views

Similar Articles

[PageSpeed] 1

Hi Torben
Try the following

1. your table with the possible options is in columns D1:E4 (for more
options enlarge this area), e.g.
       D                    E
1    This is one        1
2    This is two        2
3    ...
4    ...
Note: I have separated your text and the corresponding values in two
columns

2. Highlight A5, goto 'Data - Validation', choose list and select the
area D1:D4

3. In cell A6 enter the following
=IF(AND(A4<>"",A5<>""),A4*VLOOKUP(A5,$D:$1:$E:$4,2,FALSE),"")
The first part checks if A4 and A5 are not blank


HTH
Frank

0
frank.kabel (11126)
1/19/2004 2:42:11 PM
One way using Data Validation and VLOOKUP

Assume your sample table below is in A1:B3
(text in A1:A3, numbers in B1:B3)

>       This is one 1
>       This is two 2
>       This is someother 5

Name the range A1:A3 as say: MyList
Name the range A1:B3 as say: MyTable

You have in A4: 55

Select A5
Click Data > Validation > Settings tab
Under "Allow:" select "List" from dropdown
Under "Source:" Put: =MyList
Click OK

(This sets up the Data Validation in A5, you'll get a dropdown list to
select)

Put in A6: =VLOOKUP(A5,MyTable,2,0)*A4

If A5 = "This is two", A6 will return 110 (i.e. 2 x 55)

The above should give you what you're after

--
Rgds
Max
xl 97
----------------------------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------------------
"Mig" <kommer_ikke_dig@ved.nu> wrote in message
news:bugp8v$2ia8$1@news.cybercity.dk...
> Hi experts
>
> I like to have a tabel like this
>
>
>
>       This is one 1
>       This is two 2
>       This is someother 5
>       Text 4
>
>
>
> that say a text that is assosiated with a number. Then (some where else in
> the spreedsheet) I like to have a drop down wther I can choose between
> ("This is one"; "This is two", "This is..."), and depending on the choosen
> text I will use the assosiated number in some calculations. Is this
possible
> with Excel?
>
> e.g. if in the drop down is in cell A5, and there is  choosen "This is
Two",
> and in cell A4 says 55 like to write some thing like this, in cell A6:
> "=A5*A4" and the the result should be 110
>
>  Is this possible with Excel?
>
> best regards
>     Torben
>
>


0
demechanik (4694)
1/19/2004 3:02:20 PM
Reply:

Similar Artilces:

Where is the data menu for sorting lists?
Am trying to sort an address list into alphabetical order. Help says click on list and and select from data menu. When i click no data menu appears. can anyone help please? Thank you. Do you want to tell us what Office program you're referring to, and what version of that program? Daddy "Shakoh" <Shakoh@discussions.microsoft.com> wrote in message news:7E04A2BD-DA32-462B-A912-5469035351C9@microsoft.com... > Am trying to sort an address list into alphabetical order. Help says click > on > list and > and select from data menu. When i click no...

Attempting to perform two different sorts on one worksheet.
Great Gurus of VBA, I have a worksheet, which uses the following VBA procedure to sort th data when the last bit of entry occurs in column H. However, when want to print this spreadsheet I need the data to be sorted differentl and I am apprehensive about putting the code for the second sort int the sheet object because I am unsure about how the two might conflic with each other. Can someone give me an idea about how to have thes two different sorting routines work on the same sheet and not mess eac other up. I thought of putting a command button on the sheet to initiat the second sort but.....

How can stop excel from autochanging my cut and paste data?
Recently I need to copy and paste some data in to my excel for references. However, whichever part I copied containing text like 1-0, 1-2 the text will automatically change to 1/1/2000, Jan 2 etc.... Why is it so? How can I stop that? But when I try to reformat cell to text it becomes code numbers like 36586. Help me please. Thank you. p/s I paste the text in HMTL form if I paste special as unicode text. Hi Try pre-formatting the column as Text. Format>Cells>Text -- Regards Roger Govier "Excel doubter" <Excel doubter@discussions.microsoft.com> wrote in messag...

Separate data string into numeric and text
Hi Can anyone help me with a script, to read in a string of mixed numerical and text data, and separate them to a specific format of spacing. This is the raw data: 11-11-11,44444444,JOE R BLOGGS,2757.42, I need to convert the data into 11111144444444JOE R BLOGGS 2757.42 Firstly i need to remove the "-" and ",", so that it all joins up. There needs to be 6 chars at the start for 111111. At the 7th char i need to leave space for 8 digits (44444444 - although sometimes the no. being read in will be less than this). Then 15 chars must be left for name, and the amount will...

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

Making sure data is saved on closing application
Hi, I have built myself the following application from 'Teach Yourself Visual C++ in 21 Days' chapter 'Day 14 - Retrieving Data from an ODBC Database' http://serghei.net/docs/programming/c++/ty%20visualC++6%2021%20days/ch14/ch14.htm The application lets you edit and add records to a database (as well as just retreive them). The application uses recordset flags in code to make sure that data is saved if you edit one record and move to another. However, there is no code to make sure new or edited data is saved when the application is exited. After having poked about, I have...

Problems XSLT Transformation of XML Data to EXCEL 2000
Does Office Excel 2000 support XSLT transformation of data from XML cause am having problem when the attachment is opened in the client side if the Excels version is in Office 2000 and also its works fine if the Office Excel Version 2002 and up when attachment file is open via browser the data is in a single line of string in the first row of the excel worksheet Glenn Gomez wrote: > Does Office Excel 2000 support XSLT transformation of data from XML cause am > having problem when the attachment is opened in the client side if the Excels > version is in Office 2000 > >...

How to access data buffers in a completion routine for a KMDF driv
I'm porting a WDM upper filter driver to KMDF version. I have two questions about data accessing in a completion routine. 1. In the completion routine of my WDM upper filter driver, it was able to retrieve data from Irp->AssociatedIrp.SystemBuffer( The IRP is using Method Neither I/O, and the data was set by function driver). How to do this for my KMDF driver? 2. In the completion routine of my WDM upper filter driver, it was able to retrieve data by calling MmGetSystemAddressForMdlSafe(Irp->MdlAddress, NormalPagePriority)( The IRP is using Method Neither I/O, and t...

Expense data base
does anyone have a database to track household expenses (utilities, insurance, etc.) http://office.microsoft.com/en-gb/templates/TC102068841033.aspx?CategoryID=CT101428511033&av=ZAC000 Or http://www.microsoft.com/money/default.mspx -- Kevin3NF SQL Server dude You want fries with that? http://kevin3nf.blogspot.com/ I only check the newsgroups during work hours, M-F. Hit my blog and the contact links if necessary...I may be available. "cg" <cg@discussions.microsoft.com> wrote in message news:A799A57F-0129-408E-80A8-346685F420F3@microsoft.com... > does anyone h...

Automatically moving data #4
Thanx for all the help. I'm going to make the file smaller first. Where do I email it to -- multipla ----------------------------------------------------------------------- multiplan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1591 View this thread: http://www.excelforum.com/showthread.php?threadid=27391 Thought I gave you this reply earlier ?? Either to: xdemechanik <at>yahoo<dot>com or demechanik <at>yahoo<dot>com (both valid) -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>...

Automating transfer of data in cells
I have a time management spreadsheet with data stored against work type and date. I need to transfer this data into a similar but more comprehensive spreadsheet and wonder whether it is possible to automate this task by using the work types and dates in a macro (I have almost 10 months of data to transfer), along the lines of check date, check worktype, where argument is true enter data from cell. I think I need to use visual basic, but I can't find out how in the help screens. Any advice is much appreciated. This is not difficult providing you keep your data in simple tables...

CRM 4.0
Hi How does one configure a WF Local Service / Data Exchange Service in CRM 4.0? In a custom WF host I can accomplish this by specifying the service in the host application's configuration file. Is this approach possible with CRM 4.0? I understand that the workflow runtime is hosted by the CRM Asynchronous Service, and I have been able to register custom WF Activity as detailed in the CRM SDK documentation. However I can find no references to registering / configuring a local service. Any help much appreciated! Regards, Gareth On Oct 23, 1:38=A0pm, rgdav...@gmail.com wrote: > H...

Saving data #2
Hi all, I need to save data (results) from a base spread sheet program that i use on a weekly basis. i am in the middle of building this program, and have just discovered macros, but this, along with links is about my current knowledge of excel how can i automate to accumulate data from the base spread sheet (program) when i clear all data from the program to produce fresh results the following week, and to keep past data up to date and available for further use. Any help would be appreciated. legepe With a combination of formulas and dynamic named ranges, it is possible to just add the ...

Importing data via macro
Hi, I want to create a macro, which let me automate the process of importing the data from various files (*.xls) from a particular directory to the main table in MS Access. My Main table in access is called Wire Transfer Main table. And every Monday few specialist go in a share drive and post their completed forms in ..xls format. My macro should go into that shared directory every week and pull out the files from that directory and copy the data from those files to my Wire Transfer Main Table in access database. Please help. Thanks Liz - you're probably better off if you writ...

Adding data from another sheet.
I hope I can find some help on this one. I have a spreadsheet that consists of two sheets. The first is a form. The second is a list of questions. I have added a checkbox next to each question on sheet 2 to select the question and add it to the form on sheet 1. The problem: when you select a question on sheet 2 it will only add it to a specific cell on sheet 1. So if I select question 15 it will go in a position like it is the 15th question when actually I would like it to be the first question. The question: Is there a way to make the questions start adding to the top cell on sheet 1...

Like criteria on a combo box ot working the way I want... Please help!
All, Below is a the standard code I use in a combo box. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[llListingID] = " & str(Nz(Me![cboTerritoryName], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Me.Form.AllowAdditions = False The query used to find the data is... SELECT qryLister.llListingID, qryLister.llTerritoryName FROM qryLister WHERE (((qryLister.llTerritoryName) Like "*"+[Enter any part of Territory Name:]+"*")); All works fine for the first look-up. But in order to refresh the combo box so it allows one to &...

Get Access Data into Excel
Hi All, I am using excel macro to get data from access database. My sql query gives me 5 records or more than that. I am able to pull it different cells. But I want all the 5 results in single cell. Please help im not exactly sure what your trying to do, but maybe something like this will do the trick dim accval as string accval=rs!:XXX rs.movenext accval=accval & " " & rs!:xxx loop it till rs.eof=true hth dmoney "fi.or.jp.de" wrote: > Hi All, > > I am using excel macro to get data from access database. >...

Drill down pivot table data via VBA macro
I am trying to create a macro that will drill down subtotals from a pivot table. Since the number of rows, and colums, changes frequently I need to reference the total for the row or column. Can this be done via VBA macro? -- jmon76 ------------------------------------------------------------------------ jmon76's Profile: http://www.thecodecage.com/forumz/member.php?u=1909 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204538 http://www.thecodecage.com/forumz Excel 2007 PivotTable Show subtotal details via macro. Define subtotal with PivotField...

Cut'n'Pasting data
Greetings ! I have a CSV data file wot looks something like this - "1529.17698720957","133.597550559965" "1685.21901149326","132.817184396522" "1900.00000000000","130.300887834893" "2193.34485206410","121.730777157435" "2363.17295960769","114.937652855693" "2523.01169878763","108.544103288496" "3240.77088467590","87.805735336415" "3590.69860622591","81.680775462264" "4229.19543928027","78.487652800160" This data i...

Help Fixing Data on Item Stock Status display
GP 8.0, SQL Had an employee "sell" 9,000,000,000 units of an item at $0 price. Instead of deleting the line, he $0 the price. The invoice was posted. I discovered when our income statement showed a loss of $42b! My first mistake was to try and reverse by creating a return for the same # of units at $0 price. But somehow this created problems with the inventory. Basically, I messed around with it with a number of invoices/inventory adjustments. I discovered how to void the RTN and INV using utility > sales > remove sales history. This had the desired effect with one exc...

Data from Form to Subform to Table
I have a main form for material inspection, and another form for the inpsection results, and tables for each that store the data. For example, you have to enter the PO# and P/N on the main form, as well as some other info, then you click to open up the inspection results form in datasheet view. The inpsection results form shows the PO# and P/N that you entered on the main form, but it wont store the info in the table for the inspection results. Can someone please help me figure out how to get the info to store in the inspection results table as well as the material inspection tab...

Fuzzy matching data
Hi I have two spreadsheets of similar property data, and Im trying to create a formula in spreadsheet A that will look in spreadsheet B, find the matching property data then return corresponding data to spreadsheet A. Im using the Index formula for this and name ranges, and everything works fine if the property data in spreadsheet A exactly matches spreadsheet B. If there is a slight difference it falls down e.g. spreadsheet A property named '28 St Marks House', spreadsheet B property named 'Flat 28 St Marks House'. Does anyone know of a matching/lookup/index method that...

How can I automatically remove duplicate data within a column?
Is there a way to automatically remove duplicated data within a column in Excel? For example, I have a column of over 10000 rows. The cell contents are call numbers, many of them are duplicate and I'd like to remove them without sorting and manually removing them. Chip Pearson has a means for doing this at http://www.cpearson.com/excel/deleting.htm If it is just a single column, though, you can use Data>Filter>Advanced Filter. Once there, select Copy to a new Location and Unique Records only. That will give you a list of unique values in your column - i.e., no duplicates...

What I Like about Money 2004/2005
There are so many posts about what is wrong with this product (including some of mine in the past) that I thought I'd be a bit contrary and describe a few of the things that I really like in the hopes that others might agree and that MS might be sure not to ruin what does work well. I was so disappointed in general with the result of Money 2005 that I bought Quicken 2005 and ran both products side-by-side for some time to compare. I should also point out that I was a beta tester for Money this year and although we were able to help fix a few bugs before release, there was really n...

Whats the fastest way to retieve data in asp.net using MySql
I know this is not a MySQL forum, but we use ASP.NET and MySQL. My questions is what is the fastest, most efficient way to retrieve data using asp.net using MySQL 5? thanks in advance Are you referring to ODBC/OLE DB connection settings, how to write SQL queries, embedding queries in the asp.net pages, or calling stored procedures? I'm sorry, but your question is just too vague. Perhaps if you explained why you're asking the question and why you're asking it in a SQL Server group. FWIW, generally connecting async and writing stored procedures for ever...