Adding sum of text

Example:
A              B             C
a	1         abcd
b	2
c	3
d	4
e	5
f	6
g	7
We would like C1 to equal the amounts of B1:B4 when we type letters "abcd."
0
HP (9)
7/22/2004 4:57:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
596 Views

Similar Articles

[PageSpeed] 4

Hi!

Depends on how representative of the reality your little model is.

As it stands, a single formula placed in, say, D1 will calculate th
equivalence of abcd in C1
=VLOOKUP(MID(C1,1,1),TB,2,0)+VLOOKUP(MID(C1,2,1),TB,2,0)+VLOOKUP(MID(C1,3,1),TB,2,0)+VLOOKUP(MID(C1,4,1),TB,2,0)
(entered as a single line).

where TB is the Name of the range A1:B7.

This will only deal (for obvious reasons) with exactly 4 letters in th
string in C1.

If you want to deal with variable lengths of strings in C1, I woul
turn to VBA.
Also, if you really want (as I read your post) to put the letters in C
and have the answer returned in the same cell, VBA is the only way.

Come back if there's more information to add.

Al

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

0
7/22/2004 11:01:22 AM
Reply:

Similar Artilces:

Text Box Data Validation
Having a bit of a problem with what should probably be a simple task. Outlined, what needs to be done is as follows: Text Box is initially locked to prevent accidental data changes User double clicks In double click event, text box is unlocked allowing data to be edited. In the BeforeUpdate event user is presented with a message box so they can confirm they want to change the data If the user selects "Yes", data is changed, focus is changed to next control and text box is locked. If the user selects "No", new data is discarded, focus is set to next control, and...

Text field database query problem
Im doing a query with multiple criteria on a database that has similar text items in one column. eg FM, FM3, FMO, or FMC6. If I query for FMC6 I obtain the expected number of records but if I use just FM in the query criteria I pull in all the additional FMxxx records. How do I go about limiting the records pulled in by the query. Ive tried using "FM" or 'FM' in the query but to no avail. Thanks "mike@multiline.com.au" wrote: > Im doing a query with multiple criteria on a database that has similar > text items in one column. eg FM, FM3, FMO, or FMC6....

Creating a contact group from text file
I got 10 e-mail addresses in a word text file can I somehow make it into an address group or contacts without having to open a card manualy for each one and typing name nickname e-mail etc.? thnx L. You need to make sure it is in the correct order, however, you can add commas between fields and import it as comma deliminated file. It is one of the options for importing. "ronba" <lironba@yahoo.com> wrote in message news:1c7e9546.0411091706.7c3adb96@posting.google.com... > I got 10 e-mail addresses in a word text file > can I somehow make it into an address group or c...

Help Importing Text Files
Hello, Here is my situation. I work for an electronics repair facility, a new company we are doing work for is sending jobs to us via FTP as .txt files. If you open these text files using notepad, they look like jumbled words. For example, 0000001111232Johnsmithbeverlyhillsca90210. What I need to do is tell Excel to import this file using this formula: the first 'xx' spaced are for the customer's ID number, the next 'xx' spaces are for the customer's first name, the next 'xx' spaces are for last name, etc. Here is what I tried so far: 1. Set column headin...

Excel 2007 text sorting problem
I apologize in advance for decidedly noob lingo... In short, Excel is doing a very odd sort of last name/first name columns in a worksheet containing data from two separate lists. Here's an example: Sandra H Bailey Ward Bailey B B BAILEY BARBARA BAILEY BARBARA BAILEY DEBBIE BAILEY ELEANOR BAILEY ELOISE BAILEY JAMES BAILEY JANET BAILEY JANET BAILEY JANET BAILEY JESSIE BAILEY JIMMIE BAILEY JOHN S BAILEY JON...

charts, cubes, text problems
Hello I am wanting to create a stacked chart (2 stacks) with a trendline (seperate figures), but it won't allow me to do that. I can only create a normal chart (ie. not stacked) with a trendline. Is there a way to do this in powerpoint / excel? Any suggestions? I need your help urgently. Thanks Siobhan - The problem is that Excel doesn't think you want a trendline of something that depends on more than one series, since the variations are lumped together. But you can fake it: Add the data in the worksheet, to get the stacked values, and plot these as regular unstacked seri...

Prompt to choose text in PowerPoint
Is it possible to add a prompt each time I open a custom template that prompts me to select from a list of text? We would like to add a document designation (e.g. Internal, Copyright, Restricted). We would like the user to be prompted automatically every time they create a new presentation to choose from the designations our company uses. There's no native way to do this but a vba programmer would be able to set up an addin or in 2007 a custom template that did this. -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint...

removal of text shadow ran amonk
How do I remove text shadow that is beneath each letter in each word? I changed slide design in which the text had shadows, and some slides' text title has shadow behind each each letter in each word, which is what I want. However, there is text shadow beneath each letter in each word as well and it's upside down, which is what I would like to remove. By the way, when I delete the text altogether and retype it, the same problem recurs. Thank you in advance for your assistance. Sounds like the shadow was applied to the placeholder on the slide master. You'll w...

Text function 12-11-09
i have a database which i need to extract the supplier name and they are not in same order for example RPO-09393-Supplier AAA 980-Supplier BBBBBB AHU-OIJ-JUH-Supplier CC As you can see the suppliers name are always at the back, how to build the formula just to extract the supplier name (different lenght) that always at the back. Thanks Howard =TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)) "Howard" wrote: > i have a database which i need to extract the supplier name and they are not > in same order for example > > RPO-...

Excessive e-mail size for just text, more info
When using Outlook 2000, using Word as the e-mail editor, writing a simple e-mail in HTML format with a few colors of text, the e-mail file size becomes either 32MB or 44MB in size. When the text is cut and pasted into Word, the file size is very small, when re-pasted into a new e-mail, the size increases again. Acting on an earlier response, I found the following... E-mail file size = 44Mb, yes Megabytes! Word Document (test.doc), size = 94.7 kb Word HTML (test.htm) file size is as follows: HTM file size = 23.7 kb support files in sub directory (3) size is editdata.mso = 46.7M...

keyboard shortcut to wrap a text in cell.
keyboard shortcut to wrap a text in cell. Nitin, ALT+Enter. Hope that helps. If this helped you, please click 'yes' at the bottom of the post where it asks if the post was helpful to you. huber57 "nitin kansal" wrote: > keyboard shortcut to wrap a text in cell. ...

Excel 2008 will not SUM
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I can SUM items just fine in Excel for PC, but when I use the formula bar in Excel for MAC 2008, it just keeping adds cells and never gives a total. I have followed the tutorials and copied the sample formulas into a new blank spreadsheet. <br> This is the most basic function you can do in formulas and yet it won't do it. <br><br>Why do they even make Excel for MAC if it won't work?????? No sarcasm intended, but Excel seems to work fine for millions of others, at least in this rega...

text box look up
I am using a userform with a textbox to look up certain surnames and give me the people with it, I have asked before about this but the problem I am having it is only i type the name smith in the box and it comes up with the first smith in the line. How do I get it to move on until I get the one I want? I have seen in some programs you type "smith, a " and it comes up with Alice Smith or something similar. Here is the code I have been using and have been given extra, can someone give me advise on how to set it up. Private Sub TextBox7_AfterUpdate() Sheets("PRODUCTS")...

Code for making a text box visible and invisible
Hello In my data entry form there are some fields cboTag, txtZero etc. cboTag is having a value list which is LS1201;LT1300 etc. What I wish to do is if cboTag selection is LS1201 txtZero shoud be invisible to desable any data entry. Where as cboTag selection is LT1300 then txtZero should be visible to enable data entry. Kindly help me to resolve this. Thanks in advance The code I tried is as follows which gives an error invalid qualifier. Private Sub cboTag_Click() Dim txtZero As String If cboTag = "LS1201" Then txtZero.Visible = True Else: txtZero.Vi...

Copying/pasting text to text box and exporting as webpage
With Publisher 2003 when I copy text from a webpage and paste it into a text box in Publisher 2003, it shows up fine, and is in the same format as the original. But, when I try to use the "Publish to the Web" feature and create a webpage out of it, and see a preview of it in a web browser, the text runs off the edge of the page. How can I get the text to stay within bounds of the text box I created? I am trying to redo a series of webpages I created without having to type the content all over again. I would like to do this without turning the text into a "picture". ...

merge unformatted date with text
My original date column was formatted 02/01/71. I created a new column and created a custom date format; 020171. I would like to take this column and merge it together with the last 3 digits of a column where a ss# resides. This column is a text column. For example, the new column should look like 020171008. Can anyone help? I tried this based on your example and used the =Text (originaldate,mmddyy) function and used a =Right(ss#,3) to create a new column for the last three of the ss#. Then Concatenated the two fields resulting in 02171999. Good Luck! >-----Original Message...

Corrupted Text in Home page
In Money 2004 on "My Money Home Page" all of the text under the headings "Breaking News" and "Headlines from MSN Money" is corrupted. It looks to be a programming language like Visual Basic,,,for example: var PXCL=newDate(0);var PrModATR=0 et al. There are 15 to 20 lines of text like this under each heading. Any ideas on how to repair this? Thank you. Jeff I just tried Money 2004 myself and could not reproduce this. The "headlines from MSN Money" looked fine. By the way this is on Vista and IE 7. So, it may be due to your environment. What opera...

Import a Text File
Hello, I am currently using a transfer text macro to import a text file. This works great is the name of the text file is always the same. Is there a similar marco (or any other suggestion) that will allow me to choose the text file to be imported? This text files always contain the same information, just different names. Thanks for all your help. Kevin "mccallen60" wrote: > Hello, > > I am currently using a transfer text macro (Access 2007) to import a text file. This works great if the name of the text file is always the same. Is there a similar ...

Formula to increment sequence only if text in a particular cell
Hi, I'm new to this, and not sure how to do this, I'd like to increment a field by one only if there's text in a particular cell. This is what I have, so I'm trying to write a formula that automatically populates column A...this is what I have tried =IF(C4<>"","Test_"&TEXT(ROW(A1),"000"), "") --- but this formula increments even when no text in B, so I end up with a value of Test_003 in A3, where I need to have Test_002 A B 1 Test_001 some text 2 ...

Displaying data sort order as descriptive text in footer
I have a spreadsheet that will be printed and sorted (in different orders) frequently. It's an inventory list. While Excel allows me the capability to sort the data by various columns I don't see a way (by function, field, etc) to grab a descriptive string describing that sort order (e.g., the text "Serial Number, Lease Expiration Date"). What I'd like to do is put this descriptive text in the footer of the document so that sort order is properly indicated to the user without the user having to change the footer manually to match their own desired sort order. ...

Macro needed to find text
I have sheet with 2 columns and 1200 rows. Column A is numbers 1 to 1200 Column B is varying length text strings. I wish to paste different longer text strings into column C and then find if any of the strings in column B occur within the strings in column C. If yes, I wish to have the corresponding number from column A put into column D. e.g. 1 abc defghij 2 2 efgh xyzxyz 3 mnopq wwwabcwww 1 4 rstu wefghw 2 Is this possible with a function or a macro? Regards Have a look in the vba help index for FINDNEXT -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com...

Please help me with altering text in excel
I have imported text data and it is surrounded by double quotes "" How do I get rid of them without going through each cell One way, assuming source in A1 down Put in B1: =SUBSTITUTE(A1,"""","") Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mark" wrote: > I have imported text data and it is surrounded by double quotes "" How do I > get rid of them without going through each cell ASAP Utilities, a free add-in available at www.asap-utilities.com has a feature that will delete any character y...

Push out Office via AD/Group policy
Ok, I have gone through the process to install Office 2007 through AD/Group Policy Management Console on machines in our domain. With other applications we push out, if the application isn't installed, it installs it. If it is installed, it doesn't do anything else. It appears though that with the Office Enterprise 2007 that if it IS installed, that it re installs Office 2007... Is there a way to configure AD/GPMC to only install Office if it ISN'T installed already????? Thanks Steve Phone Support for Office Enterprise is free. See the below Here you wi...

data import and text to columns
I am trying to import a table, separated by delimiters, from the web. The table's data changes frequently. I can use text-to-columns to sort the table into the proper columns, but don't want to have to do this every time I refresh the data. Is there any way around this? Heres the table I'm talking about. http://polar.ncep.noaa.gov/waves/latest_run/enp.46062.bull Thank you, David David Try turning on the macro recorder while you open the web site, select all and copy then paste to an Excal sheet and do your Data>Text to Columns as pipe de-limited. Run that macro when you...

Excel XP
When i create a new 3d chart and label the x and z axis, the description of the z axis is long so I wanted to orient it 90 degrees. When i do this, however, the text box remains the same size and therefore cuts out part of the text (ie only part of the title is displayed despite lots of room on the z axis) Follow-up. It appears to be only my screen view when orienting an "axis title" on y or z axis variosu degrees. The text box appears to remain the same size and therefore distorts the text displayed, however, when I print the document, or when it is displayed in "cha...