How do I use text in a cell as a range name in a formula

If cell A1 had the text TEST in it
and TEST is the name I have given to a group of cells using the name box
what formula can I use to give me the sum of TEST, thats is the sum of the 
cells in the group called TEST.
I understand that I can simply have =SUM(TEST), but I want the formula to 
refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously

Any help appreciated
Thank you
0
Kiwi (6)
4/11/2005 2:43:03 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
377 Views

Similar Articles

[PageSpeed] 23

In this case, you want to use the INDIRECT function. E.g.,

=SUM(INDIRECT(A1))


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Kiwi" <Kiwi@discussions.microsoft.com> wrote in message 
news:A9E126CC-29D9-46A4-BF9B-92094BC7E3BA@microsoft.com...
>
> If cell A1 had the text TEST in it
> and TEST is the name I have given to a group of cells using the 
> name box
> what formula can I use to give me the sum of TEST, thats is the 
> sum of the
> cells in the group called TEST.
> I understand that I can simply have =SUM(TEST), but I want the 
> formula to
> refer to Cell A1 to get the name ie =SUM(A1) doesn't work 
> obviously
>
> Any help appreciated
> Thank you 


0
chip1 (1821)
4/11/2005 3:05:40 AM
Thanks Chip, easy when you know how

"Chip Pearson" wrote:

> In this case, you want to use the INDIRECT function. E.g.,
> 
> =SUM(INDIRECT(A1))
> 
> 
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> 
> 
> 
> 
> "Kiwi" <Kiwi@discussions.microsoft.com> wrote in message 
> news:A9E126CC-29D9-46A4-BF9B-92094BC7E3BA@microsoft.com...
> >
> > If cell A1 had the text TEST in it
> > and TEST is the name I have given to a group of cells using the 
> > name box
> > what formula can I use to give me the sum of TEST, thats is the 
> > sum of the
> > cells in the group called TEST.
> > I understand that I can simply have =SUM(TEST), but I want the 
> > formula to
> > refer to Cell A1 to get the name ie =SUM(A1) doesn't work 
> > obviously
> >
> > Any help appreciated
> > Thank you 
> 
> 
> 
0
Kiwi (6)
4/11/2005 3:17:02 AM
Reply:

Similar Artilces:

Check current cell is empty
Hi, I'm getting stuck... If I enter some new data in Sheet2, column A, then this cell will be copied to last cell of sheet 1 columnA automatically. This task can be done now IF the data is overwriting some existing data, no need to copy. my logic is if the active cell is empty, and the target column is 1, then do the copy and paste. else, do nothing The question is: how can I check the current cell is empty? Thank you in Advance Bobocat Sub tester() If IsEmpty(ActiveCell) Then MsgBox "Activecell Is Empty" Else MsgBox "Activecell Is Not Empty&quo...

Using expression builder object
Hi, I'm developing a wizzard in Access which builds import templates for various data sources to a fixed set of tables. In step 3 the users must be able to build an expression ; for instance Left([Fieldx],20) . Now I would like to have a command button on my form which calls the Access expression builder to allow the users to use this to build the expression. This expression will then be stored in a text box linked to the templates table. Anybody know how to call and use this object from VBA code? -- Kind regards Noëlla DoCmd.RunCommand acCmdInvokeBuilder I th...

move cell contents
Is there a way to move a cell contents to another cell with a formula. ex: if a5="Name" then move g5 to j5? Also, I am using =INDEX(Sheet1!B3:B12,INT((RAND()*10)+1),1) to pick random names from a list. I have the formula in different place pick random names from different list. This does work, but I have different list with some of the same names and with the random pick I do not want the same name to appear. -- Thanks for any and all help. Davidl Hi David a formula can only affect the cell it is in, it can't move or change another cell for this you need some code ...

Outlook 2003 and "Check Names"
Hi, We are testing Outlook 2003 at our office now and there is one very frustrating "feature" that is bugging all of us. When we create a new e-mail message and type in the first name of a person from the global address book and hit ctrl- k to check the name...we get a response that Outlook doesn't recognize the name and there are "no suggestions". The only way Check Name will successfully resolve the name is if we type it last name first (ie, Gates, Bill). This was not the case in Office XP or Outlook 2000. You could search by first name (or partial first name)...

visible cell only
I'd like to use the PERCENTILE function in a list that has been autofiltered and get the results based only on the visible cells. I've used SUBTOTAL in order to get count, average, min and max. But I need to get the .25 and .75 percentile figures for the filtered data (visible cells only). I've scoured these forums. I've scoured the web. I've found some vba code that was supposed to select only visible cells but it doesn't work for me. I posted last week in the programming section of these forums (and again this morning) but got no reply. I figure...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

multiple Domain name delivery problem
hi, I currently have Exchange Server 2003 Build 7638:2 SP2,. We have multiple domain names being delivered to the exchange store. I have nothad any problems, but i currently have one user that is not receiving emails with attachments from one certain "internet" sender to one of her email addresses, but the other address works fine & if they send emails without attachments, everything works fine from either address. I have had the user send them message with attachments to me & the user with the problem & i get the message, but not the other user! I even use message...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

how do I automatically update a text box in a chart?
Hi, You can link the textbox to a cell. When you update the cell the textbox should reflect this. Select the textbox border and then in the formula bar enter the complete cell reference, for example, =Sheet1!A1 Cheers Andy Kath wrote: -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

Drag and Drop file/directory names on Tree Control.
hi, I am writing an application in which i have one Tree control. Now i want that , when user drag & drop any file/folder on tree control the file name/folder name should be add in the tree control item. User may drag and drop file from desktop or may be from directory. I just want the path of that file/foder name nothing else. so plz tell me will it be possible ... Plz Help me............. Klic..... > I am writing an application in which i have one Tree control. >Now i want that , when user drag & drop any file/folder on tree >control the file nam...

formula based on format
Is there a way to have a formula that is based on a cell colour or based on the way a cell is formated? I need it to count the number of occurrences that this happens. -- Thank you for your time. Windows NT Office 97 Hi you'll need VBA. See for an example: http://cpearson.com/excel/colors.htm "James Kendall" wrote: > Is there a way to have a formula that is based on a cell colour or based on > the way a cell is formated? I need it to count the number of occurrences > that this happens. > -- > Thank you for your time. > Windows NT > Office 97 This ...

owa not resolving names properly
Exchange 5.5 sp4 - just recently OWA is not resolving addresses properly - the administrator at this site states: > There is a strange situation happening when using OWA to send emails. If > we try to send an email to an account out of our domain (i.e. > jravazzano@yahoo.com) the recipient name and domain get wrongly resolved > to our own domain thus generating a message undeliverable. > > For example using OWA a test email was sent to "jravazzano@yahoo.com" the > address gets resolved to j@ourdomain.com The admin is going to give me an account to test with, but...

adding name /creating field/query?
Hello, I can create an invoice_number field in a query using the primary field ID from the main table as invoice_number: ID but if ID say is 100, I cannot work out how to create renewal_invoice_100 Cheers Geoff Geoff We aren't there. We can't see what you're looking at. Where did "renewal_invoice_100" come from and what does it mean? Please post the SQL statement of the query you are trying to use. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://micro...

what's the formula for adding symbols in cells?
I have a chart that has blank info in the legend. I want to add an * to indicate something, but just inserting a symbol doesn't work. Any ideas? Thanks. Debi - To add information to the legend, you need to add to a series name. Right click on the chart, select Source Data from the pop up menu, click on the series tab, select a series, and either type something in the name box, or click in it and select a cell with the mouse. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Debi wrote: > I have a chart tha...

Install Exchange 2003 Server on new Hardware with same name
I got a new server to move my Exchange Server Installation. I want to keep the name I'm using now for my Server. Already read TechNet Article "How to Move Exchange Server 2003 to New Hardware and Keep the Same Server Name". Is there anything else that I should be aware of that is not on this TechNet article? I'm using a different Disk Configuration, does this matters? Should this be a smooth process? Thanks for any help you can give. The drive letters MUST match, because the paths of the databases are at the Active Directory. The disks can have different sizes than the or...

Lookup text with multiple search criteria
I know how to look up data and text using vlookup and hlookup, what I' like to know is whether there is a way of looking up data based on mor than 1 search criteria; for example If I wanted to look up an item in a 4 column database, I'd us something like this: =VLOOKUP("Apples",A2:D10,4,false) But that only looks for Apples. Say I wanted to lookup data based on subcategory of Apples, e.g. colour. If it was a number, I could use SUMPRODUCT and (assuming named range were in use) do it like this: =SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total)) But obvio...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

outputting values to a range from one formula
I wish to generate a table automatically by means of a single formula that applies an iteration on a starting given value with a given step, and the computed values are posted/entered automtically into cells from a given cell onwards, say below it, until the computed value reaches a certain given limt. This is somehow the inverse of INDIRECT or of OFFSET. These can pull values from a variable addresses of cells. What I need is to push values into a variable addresses of cells. Can anyone help me on that?? Thanks. :confused -- Shafe ---------------------------------------------------------...

Using Visio HTML output within frames
Hi, I want to include visio HTML output in a frame of another html file. Unfortunately it is not working. I understood the problem is in vml_*.htm files. It is due to, the target arrtibute(pointing to _parent) in v:shapetype tag and href attribute (pointing to #) in v:shape tag. These attributes should point to "_top" and "<target-html-file>#" respectively inorder to work. I want to change these options while saving .vsd as web page? I would appreciate if you can help me in this regard. <v:shapetype id="VISSHAPE" target="_parent" coor...

Can you only merge up to a certain number of cells
I am working on microsoft excel 2003, I have a sheet that I merged cells starting with line 8 through 43...when I type my information in the merged area I can see all that I am typing...say it goes up to line 30 once I hit the enter key I can only see up to line 20. Even when I print it out it only prints up to line 20...I have checked to make sure there are not locked cells etc. I cannot figure out at all why this is happening...is there only up to a certain number of cells you can merge? From "Excel Specifications and Limits" Length of cell contents (text) ...

Calculating age in a label or text box on a form
Hello All, I have a text box for birthdate for a user to enter the birthdate and I want to the age for the user to be automatically calcuated and displayed in another label called "Age." What formula can I use? Missy ...

Newbie Question: Using Web Services
Hello All. I've been trying to implement Infopath with CRM with no success. I've tried the example, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crminfopathcrmintegration.asp, to no avail. I have followed the example to the letter, but find the information about publishing the Web Service to the server to be somewhat lacking. Admittedly, I am not a programmer, and I am continually running into an error in line 54: xmlDoc.LoadXml(objQuery.ExecuteQuery(objBizUser.WhoAmI(), strAllAccountsFetchXML)); Has anyone else ...