range specified by the value in another cell

I have the following problem:

I have let say 100 numbers in column A, and a number 
n<100 in cell B1. I want to have the sum of the first
n numbers in column A, so I would need to specifiy the range
A1:An, but I need this independently of the actual value of n.
I have tried A1:A(B1) with but it doesn't work.
So has somebody any idea

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

0
1/15/2004 10:44:54 AM
excel 39879 articles. 2 followers. Follow

1 Replies
543 Views

Similar Articles

[PageSpeed] 24

Hi

2 ways for it

1. Use the formula
=SUM(INDIRECT("A1:A"&B1))

2. Create a named range
RangeToSum=OFFSET(SheetName$A$1,,,SheetName!$B$1,1)
The formula will be
=SUM(RangeToSum)


-- 
(When sending e-mail, use address arvil@tarkon.ee)
Arvi Laanemets


"Zsolt Feleki >" <<Zsolt.Feleki.102k8s@excelforum-nospam.com> wrote in
message news:Zsolt.Feleki.102k8s@excelforum-nospam.com...
> I have the following problem:
>
> I have let say 100 numbers in column A, and a number
> n<100 in cell B1. I want to have the sum of the first
> n numbers in column A, so I would need to specifiy the range
> A1:An, but I need this independently of the actual value of n.
> I have tried A1:A(B1) with but it doesn't work.
> So has somebody any idea?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
garbage (651)
1/15/2004 11:06:56 AM
Reply:

Similar Artilces:

Retrieve Picklist Values from caseOrigin
Hi all, Im attempting to retrieve lookup values for "caseorigincode" contained in the incident entity. If someone has a sample of how to do this in Vb.net, it would greatly be appreciated! Thanks, Larry B. ...

Trying to copy address book from one laptop to another
I exported a Windows CSV file from Outlook called contacts.CSV, containing all the names and addresses from the folder, "Contacts". I installed Office 2003 on the new laptop and copied across the datafile ok, but I'm having problems setting up the Address Book. I'm thinking the CSV file is not the way to transfer the address book details, in which case, how can I get the Address Book on the new laptop to work in the same way as the Address book on the old laptop? I used to be able to click Tools > Address Book and all contact details would be there. The same list as is i...

Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file path (testing to see if the folders already exist along the way) with a specific, data dependent file name. It works, but I'd like to tweak it a bit and I'm not sure how to write the code. I'd like to save only the active sheet instead of the whole workbook. I'd also like to save only the values and formats to the new file, not the formula and macros. Anyone have any advice on how to accomplish this? Here's my current code: Sub SaveName() If Not Len(Dir("g:\users\one\" & Range("h6&...

Portfolio Server
I am an admin on the entire account and it will not allow me to do so. ...

Linking to Another Excel File (File Name Changes)
Hi, I have 2 separate Excel file worksheets. Basically, one file is updated each month and will have a different name (example: File052007 for May, File062007 for June, File072007 for July, etc.). Currently, I'm trying to link cells in my other file to the updated file, but I don't know how to set the link so that it can change each month. I was trying the concatenate or (A1&A2&A3) options to try to link cells together, but haven't had much luck. Here is what the formula should look like: ='C:\FOLDER\[File052007.xls]Sheet1'!$A$1 I made 2 cells in this worksheet ...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Scan a coupon value into RMS
Is there an add-on that will allow you to scan a coupon and decode the value of the coupon and enter it into RMS? Jay ------=_NextPart_0001_83041615 Content-Type: text/plain Content-Transfer-Encoding: 7bit Good morning Jay, Thank you for posting your coupon question. I have not heard of any add-ons that would do this; and would recommend that you enter this as a Product Suggestion: http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en -us/wizard.mspx?dg=microsoft.public.pos&lang=en&cr=US&type=suggestion Todd Berger Microsoft Online Support Get ...

How can I test for nulls within a range without specifying each ce
This is my (disgustingly beginner's) function: =IF(AND('Worksheet2'!A5:A12="", 'Worksheet2'!B5:B12=""), "Both Columns Null", IF(AND('Worksheet2'!A5:A12<>"", 'Worksheet2'!B5:B12=""), "All of column A range populated", "Some of Column B range populated")) The important part is where I am specifying the range in a different worksheet ('Worksheet2'!A5:A12="") and testing the cells for nulls. The rest of the function is just retesting the same cells for di...

count based on another field
I have a table in my database which records when a case is marked by an assessor. The table shows the case number, the assessor ID that the case has been assigned to and also an indicator to determine whether or not the case is closed. I need the query to show: The assessor ID number The number of cases assigned to a particular assessor the number of cases assigned to a particular assessor that are incomplete The first two are fine, however, how do I limit the last part of the query to show only the cases assigned to each assessor? thanks <barrynichols@gmail.com> wrote in message...

Dynamic range #2
In C2:C65 I have dates as follows (DK standard): 01-01-01 01-02-01 01-03-01 etc. indicating January 2001, February 2001, March 2001 and so on up to 01-04-06 (indicating April 2006) in cell C65. In about a fortnight new data will appear i C66: (01-05-06) and later on C67: (01-06-06) etc. I know how to create a dynamic range if I always had to start from the top, but here I want to be able to enter for example 01-01-05 in cell H1 and the dynamic range should now be, not from the top, but from the cell C51 (which holds 01-01-05) and as far down as I (just now) have data. Hans Knudsen I fou...

Cannot insert value null into column 'Doctynam',Table tempdb
When trying to invoice a Purchase order we are getting the error Cannot Insert the value NULL into the column 'DOCTYNAM', table 'tempdb.dbo.#PODetailed_______ Column doe Have you tried stopping and starting the SQL Server service? If doing so does not fix the problem, check to see if you have any customizations. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Taff" wrote: > When trying to invoice a Purchase o...

Label a Percentage Bar with Value
This has to be simple... With Excel 2003, I have a cluster column chart with bars comparing the percentage of total projects assigned (red bar) with the percentage of projects completed (green bar) for various employees. So the bars represent percentages, but I'd like to label the "Total" bar with the actual number of projects. I'm sure this can be done; I remember doing it myself long ago. Many thanks If I understand correctly: right click a Total bar on the chart on the shortcut menu use Format Data Series open the Data Label tab and check the Value box best wishes...

Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell? Hi Carter one option - well two really: if the information is in cells A1, B1 & C1 and you want it combined into D1 then in D1 type =A1 & " " & B1 & " " & C1 this will give you the contents of each with a space between OR =CONCATENATE(A1," ",B1," ",C1) which will give you exactly the same thing Hope this helps Cheers JulieD "Carter" <anonymous@discussions.microsoft.com> wrote in message news:5fc401c48ac0$9cc86830$a301280a@phx.gbl... > How do I combine tex...

Paste to adjacent cells
Hi All If I have Data in cells A1 to A10 or C1 to C23 etc and I want to paste more Data along side, to B1 to B10 or D1 to D23. Even better would be if I could paste to either side of the data. Is there away to do this without having to highlight those cells, this would save me allot of time. Thanks in Advance Dave, When you paste, it pastes the amount of stuff you've copied. If you've copied 10 vertical cells, that's what it's gonna paste. You need only select the top-left-most cell before you do a paste. -- Earl Kiosterud mvpearl omitthisword at verizon period ne...

Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show thae value but when I enter anything else in the cell, that is visible. The formula is in the cell, its just not visible. On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote: > I have this strang excel file. When I enter formular into it, it don't sh= ow > thae value but when I enter anything else in the cell, that is visible. T= he > formula is in the cell, its just not visible. Probably the sheet is protected and Hidden check box of Protection tab of Format Cell dial...

how do I free units cells?
I try to put in alphabetical order a column but appear a messagge about cells joined togheter. How can I free the whole page ? thanks and sorry for my english Try selecting the whole sheet Ctrl A then Format Cells Alignment and make sure the merge cells box is unticked. then try your sort Reagards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107 View this thread: http://www.excelforum.com/showthread.php?threadid=513559 ...

Another time calculation issue, thanks for any help!
Hello All, I was hoping to do this without to much difficulty but not having much luck. I got the round down to the nearest 15 minutes. NOW I would like to round up to the nearest 15 minutes (ie. .25, .5, .75 ONLY), in fifteen minute intervals. Here's some of what I've tried so far... Round(1.2/0.6,0) answer: 2 would like 1.25 Int(1.9*8.0+0.6)/8.0 answer: 1.875 would like 2 -Int(-1.2)/60 answer 1.66 would like 1.25 I store the time in decimal format (I need that for another calculation which works perfectly). Any Ideas? Gina Whipp "I feel I have been denied critical...

Range of data is variable...
I am working on a year-end report for my clients that shows them information associated with their accounts. Most of the information on each client's reports relates to the current year and is easy to retreive from the tables that store all the clients' information. However, there is historical information for each client that is more difficult to report. I want to display a graph for each client showing the year-to-year change in "value" for them. But since each client has a different number of data points, it is proving hard for me to format the graphs properly. ...

Finding the Last Value in a Range
Excel 2000 I have the following data: Cell Value A1 5055 A2 5047 A3 5044 A4 A5 5039 A6 5037 A7 5032 A8 5028 A9 5026 A10 A11 5019 A12 5014 A13 5017 A14 5021 A15 A16 A17 A18 A19 A20 A21 Formula The column starts out empty and the values are entered one day at a time. The formula at the bottom of the column needs to always return the value in the last non-blank cell in the A1..A20 range. For example, after the first day, when only cell A1 is filled in, the formula s...

Conditional format based on data from another column
Hi Yesterday I asked about the possibility of conditional formatting a range of 20 random numbers into 2 different formats. Thank you for that, but now it seems that 3 of my 20 numbers are now requiring a different cell to be highlighted in yet another colour. Currently the situation is as follows:- Column G has a list of random numbers ranging from 1-20, with columns either side pertaining to the random number located in the relevant cell in Column G. It now appears that 3 numbers in this random list do not require highlighting in Column G and have amended the condition formattin...

find values by cross columns and rows
I need count values, crossing rows when match in columns values. Take a look at the Countif( ) and SumIf( ) functions. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "FCruz" wrote: > I need count values, crossing rows when match in columns values. ...

cell won't wrap text
Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. All other cells seem to wrap text fine. One cell in particular won't wrap text. It displays all XXXX instead. I did an Edit|Clear and re-entered the text and it did it again. I adjust the height of the cell to no avail. 70+ other cells in this SS work just fine with the same amount of text. Any thoughts? Try changing the format from Text to General -- Gary''s Student "GrHopp" wrote: > Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text. > All othe...

Format a cell to display as all caps
Is there any way to format a cell so that text entered will always display in caps? Hi Not Directly !......but you can use the UPPER function. If your data is in A1, put =UPPER(A1) in A2. This will convet whatever you type in A1 to Uppercase in A2 HTH Michael "WAF" wrote: > Is there any way to format a cell so that text entered will always display in > caps? No. You cannot format a cell to display caps. You can use event code in the worksheet to change the text to caps when you enter it. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column &g...

A macro to copy & paste many rows (a range) to the next column ..
Hi, I have some data that needs to be reformatted. (see below) The data has 4 columns, Col A ("Position"), Col B ("Assay") and Col C ("Alpha"), Col D ("Sample ID"). I want to select "the Col B & Col D sorted data" to be copied and pasted to the adjacent Cols-E thru H, and so on for all the different Assays (Col B) till end of file. Can this be done through a macro? A present I am filtering on Col C and then copying and pasting manually for each Assay. e.g WELL ASSAY_ID Alpha SAMPLE_ID A01 Statin1 C 1 ...

Extract data in the right part of a cell
I have some cells contains "*" , I want to extrat the text to the right of the "*". For example, if A2 contains "txt*123", I want it turn out to be "123". Thanks. Try the following formula, =MID(A1,FIND("*",A1)+1,LEN(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "plumstone" <plumstone@discussions.microsoft.com> wrote in message news:731CB03C-AD8F-43D8-BFAB-6AD50DEBE799@microsoft.com... > I have some cells contains "*" , I want to extrat the text to th...