Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Sum Range Defined By Reference Cell


I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.

A B

1 =Sum(A1:A5)
3 =Sum(A2:A6)
5 ...
3 ...
5
6

I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Cell Reference Used For Defined Range
In the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.

View Replies!   View Related
Cell Reference Or Defined Name For A Sheet
I'd like to be able to refer to a cell or local scope defined name within my sheet that references another sheet, or accomplish the same functionality in some way.

For example, if I have a sheet MyInformation and then another sheet MyPrintedInformation, where the printed information sheet shows MyInformation in a more printer-friendly format, I might have this in MyPrintedInformation:

a cell or defined name that says (this doesn't work, but just as an example):
let's call the named variable TheSheetToPrint
='MyInformation'. Then from within MyPrintedInformation all of my references look like this

Some cell :

=TheSheetToPrint!A2

View Replies!   View Related
Application-defined Or Object-defined Error :: Range & Offset
this is the line it gives the error on.

Range("A1").End(xlDown).Offset(1,0).Select

View Replies!   View Related
Application-defined Or Object-defined Error :: Add Named Range At Run-time
Trying to add a named range at run-time

Here's what I have so far ...

View Replies!   View Related
Range Selection Error (application-defined Or Object-defined)
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):

View Replies!   View Related
Application-defined Or Object-defined Error Using Range
So I'm correcting macro at work and keep running into this error:
"Application-defined or object-defined error"

View Replies!   View Related
Name Last Cell In Defined Range
In several places in my code I need to define ranges and save them in Application.Names. I need to be able to reference the last row and column in that range but I don't know how. Is there a reference within the Range object for the boundaries used to create it?

ie, the B3 in Range("A1:B3")

Not all of the ranges will have content in them and many of them will overlap, so looking for the last non-blank cell won't work.

View Replies!   View Related
Sum With Out Cell Reference
i m trying to see if there is any way that i can get a sum for a colum with out actually selecting a specific cell. this way no matter how many cells are in the sheet it goes to the last cell and sum's that column. Here is the code I have been able to get rid of most of the specific cell references that are at the bottem of the page excetp this one. Here is the code i have so far.

Sub Macro1()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select......................

View Replies!   View Related
Sum Across Sheets With Variable Cell Reference
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.

However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?

View Replies!   View Related
Application-defined Or Object-defined Error On An Active Cell
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round?
In free-run mode from excel i just get a #VALUE!

View Replies!   View Related
Reference Cell Looks At One Range Then See If Any Exist In Another Range
I have a workbook with two sheets. The first sheet is the data sheet.

Data Worksheet
A B C D colA colA,B colA,B,C colA,B,C,D
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR
The next sheet is where the magic happens. Separate this worksheet into two sections. The reference section (column A and B) and the entry section (column E-G). Column D is a formula that will concatenate column E-G together. In the entry section any number of combinations can be entered. In the reference section I need to take the style number and reference it back to the data sheet. Then take the possible combinations from that style (column E and greater) and see if any of those combinations exist on the other sheet in column D. If so then say added....otherwise say needed.


"....In Pricing Sheet" is where I am looking to put needed or added or something like it.
A B C D E F G
STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE
CR10600S Added 1042021 104 20 21
CR10600SX Added 1042022 104 20 22
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P Needed
Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas.

View Replies!   View Related
Sum For Defined Column
regarding excel formula for summing of data in my list, please refer to my attached sample file, that allows me to define particular column when it ends? In this sample, cut-off should be B16 with criteria at B17 thus B18 should give Beta's Sum total ColB to ColG. I've tried to use "sumif" with "if" but I don't how to start constructing it. And how about for two criteria? (Sumproduct). Or any idea the ends up with same result.

View Replies!   View Related
Getting A Range Reference From A Cell
i need to copy a range from one workbook ("weekly data") to another ("Yearly data"). The range to copy is called 'weekly data'. The ranges to paste to are called 'week 1' through to 'week 52' (this is the yearly data sheet). On the yearly sheet there is a cell Po1' that week on week will state the week number ie 'week13', 'week14' etc. code to use this data in 'P1' to tell the macro to copy to the appropriate week. I have attached as far as i got (then failed due to lack of experiance).

presuming that both books are open

HTML windows("weekly data").activate
sheets("weekly transfer").select
range("weekly data").copy
windows("yearly data").activate
'only one sheet in workbook
range ' data from p1 .paste

View Replies!   View Related
Reference A Cell In Sheet Range
Im currently located in Sheet1. I want to reference a cell in Sheet2.Range("B2") using a R1C1 reference.

View Replies!   View Related
Fastest Way To Reference A Cell - Range() Or Cells() ?
I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.

The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).

for k = 1 to n

if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...

Would it be faster to use this syntax (which I just found out about):

if Worksheets("Personell").Cells(k,4) > dtCompleted then ...

It would cut out 2 functions calls, trim() and str(), so it would be faster, right?

I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...

So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.

View Replies!   View Related
How To Turn Range.find Into A Cell Reference
I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.

View Replies!   View Related
Passing A Value In A Cell To A Named Range Reference
I have a formula which is using named ranges-

=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)

where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38

The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)

Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.

I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.

The goal would be to have a couple of rows with the following that could be edited with new values

View Replies!   View Related
Reference Last Cell In Dynamic Named Range
In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.

This is because the number of roads changes for each project.

I created a dynamic range name surfacing with the following formula:
=offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)).

My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.

View Replies!   View Related
Dynamic Named Range Sort Including Cells Outside Defined Range
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View Replies!   View Related
Start Function Range Reference Row From Cell Value
Is there a way to start match from a relative position?

Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?

View Replies!   View Related
VBA - Format Range Font Based On Value In A Reference Cell
I am preparing a model for a client that runs excel 2003. I need to change the format of a range based on the value in a cell (reference cell) that is outside the range to be reformatted.

Reference cell is a named cell with the value calculated with a Match() function, the only possible values are (1, 2, 3).

I need the VBA code to retrieve the reference cell value and apply one of three formats to the range. I believe the code for that part is something like:

Private Sub Format_Font()
Set vrange = Range("d63:r63")
Set refrange = Names("ind_agentfee").RefersTo
Set refrange = Mid(refrange, 2)
On Error Resume Next
If refrange = 1 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Else
If refrange = 2 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Else
.Style = "Percent"
End If
End Sub

I am having problems getting the proper opening statements and declarations correct and also get an "Else without Then" error with all combination's that I am attempting. I am sure that the code is mess now as I started with a recorded macro then modified it multiple times while researching the web.



View Replies!   View Related
Microsoft Visual Basic: Compile Error: Sum Or Function Not Defined
I receive: "Microsoft Visual Basic Compile error:

Sum or Function not defined" after I have run a macro. I recorded the macro.

The macro code is: ..

View Replies!   View Related
How To Update (automatically) The Range Of A Defined Name Range
I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.

View Replies!   View Related
Cell Reference :: Cell Range?
I have a formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Sheet1!N2:N65536) -ROW(Sheet1!N1),0,1)),--(Sheet1!N2:N65536="Dog"))

Calculating the sheet takes forever because of the cell range. Sometime the sheet will have 10 rows other times it will have 10,000 rows, so I put the range N2:N65536. I already have a count of the number of rows in a cell. Can I refer to that cell in the range, for example: N2:N&B1 (where B1 has the number of rows)?

I tried it a ton of ways but can't get it to work.

View Replies!   View Related
Sum A Range Of Cell
I would like to Sum a range of cells which has specific text (Text will be given specific value as classified below) in them.

Here is my classification:

I need column 1 to 7 to be added & the final result should be displayed in 8th column (SL)

HSL = 0.50
SL = 1
HCL = 0.50

1 2 3 4 5 6 7 SL CL HSL SL 1 HCL HSL SL 1 3
0.50


View Replies!   View Related
Sum Cell Range On Specified Worksheet
I have the following line of VBA code that sums a range of cells on the active worksheet. I need to change that to sum on a specified worksheet. Is there a way to do that? I thought it would be simple, but have had no luck... Actually using variables for the cells, but simplified here for example's sake.

If Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(123, 2))) = 0

View Replies!   View Related
Control The Sum Range With A Cell
I would like to define the range of cells in the sum with a number in another cell. Say I want to sum the past 10 years, I would like the sum definition to be controlled by another cell that includes the number 10.

If I want B1 to be the sum of A1:A10, B2 to be the sum of A2:A11, etc., I would like to be able to control the range of the sum with say, C1, which in this case would contain the number 10.

This would allow me to easily change the range of data I am summing: if I changed C2 to 15, B2 would give me the sum of A1:A15 for example.

View Replies!   View Related
Sum A Range Based On A Cell Value
I am looking fo a simple way to carry out the following..

My Spreadsheet has a range of data from column D to O

I have a Total Column Q

I have a Cell, eg A2, where I enter a value from 1 to 12

Depending upon the value entered into A2 will dictate the range of cells to sum, ie

A2 = 4 - Column Q would Total Range D?? to G??
A2 = 12 - Column Q would Total Range D?? to 0??
etc.

View Replies!   View Related
Sum Range From Active Cell To Last Used
I Have searched existing threads but cannot find the answer to my query,

As part of a longer macro, I want to sum all cells in a column which contains data. Each day the number of cells that contain data will vary.

I have tired specifying all the cells in the column which contain data as a range, by selecting the top cell and using the

Range(Selection, Selection, Selection.End(x1Down))

But I cannot get a SUM Formula of this range. If someone could show me the correct syntax to get a sum from a range like this I would be very grateful.

View Replies!   View Related
SUM Range Based On Cell Value
I'm trying to create a = SUM( RANGE) forumla, but, I need the first cell reference to represent the value of a different cell...

something like...

=SUM("A1"1:H1)

Where A1 holds the value of a cell number. ie, cell A1 may have the value 'e', so I want the result to be the sum of range e1:h1.

View Replies!   View Related
Deleting A Defined Range Name?
Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?

View Replies!   View Related
Range Defined By Inputbox
I have a spreadsheet that has 7 columns starting at B and 12 rows. Column B has numbers from 1 to 12 down the rows. I have an inputbox that asks the user for a number from 1 - 12 that relates to a tax period. What I'd like to do is depending on what number they give have that define the range. If they gave me the number 2 my range would be C11:F11, if the number was 10 the range would be C7:F7. The inputbox returns strTaxPeriod.

View Replies!   View Related
Add A Value To A Defined Name Range
I need to add a value to a Defined Name Range

ThisWorkbook.Names ("Test"), RefersTo = 10

How do I add a value to "Test" storing a tracking value

I use to keep this value on a hidden worksheet.

but my co-working messing around change this value.

hopefully using a Named Range would help prevent them from messing with it. I am not allowed to password protect the worksheets.

View Replies!   View Related
Corners Of Defined Range
How does one select a range with multiple rows and columns, then select the cell in any one corner of that range. What does the "Set" command do?...is it used when an inputbox command is assigning a range instead of a value to a variable

View Replies!   View Related
Find Last Cell In The Range And Sum It Via Vba
complete the following partial code.

I am trying to determine the last cell for Non-ILEC piece and then want to insert one row below the last Non-ILEC cell and insert "Total BS Non-ILEC" and sum "H" Range just like the way its done in the partial code. The partial code I have posted is working perfectly fine with regard to ILEC piece. I need to further add the Non-ILEC piece in the partial code to accomplish my task ....

View Replies!   View Related
Sum A Range If A Cell Contains A Text String
If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)


View Replies!   View Related
Open Ended Cell For Sum Range
I have numerical data in column A rows 1:14.

I'm adding it up in row 15. Sum=(A1:A14)

Now I've been asked to insert more rows with data above row A14.

What can I put in my Sum formula in place of A14 so when I insert
a new row, any numerical data above it in column A automatically adds up?
Sum=(A1:?)

I don't want to have to go and change it to sum=(A1:A17) if I
insert 3 rows.... I have a huge file to create with sections like this. User
may have to insert new rows. They need to all add up.

View Replies!   View Related
FillDown Range Defined By Variables
I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown.
I think my idea is good, but either syntax is off or not going to be allowed to do this.

.Range("F" & lLrwT + 1: "F").FillDown

>>I have posted this on VBAx too.<<

View Replies!   View Related
Allowing User Defined Range
I would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....

View Replies!   View Related
Create Loop In A Defined Range
i created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).

i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on

here is

View Replies!   View Related
SUM X Column Range Based On Criteria In Cell
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.

this is what I have come up with....

sumif(N10:N49, "Business", AW10:AY49)

but it returns incorrect values and not the whole range????

Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...

H2 and H3 are my attempted formulas

View Replies!   View Related
Set Bold To Font On Another Worksheet: Application Defined Or Object Defined Error
'Code1
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new").Activate
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
'Name
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
'city
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)

the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.

View Replies!   View Related
Application Defined Or Object Defined Error When Creating A Pivot Table
I keep running into this error when I try to create a pivot table.

The debugger highlights the following line:

View Replies!   View Related
Application-Defined Or Object-Defined Error Setting Validation Message
I have problems with setting Validation input title and message. When in column "C" I type product code, data validation of corresponding cell in column "D" changes to list of diameters allowed for that product. That works fine. But i also want to set validation message to show allowed diameters.I set it in VBA using named ranges.
But for every second line (13, 15, etc on attached file) I get an application-defined or object-defined error. The rest (14, 16 etc) works ok.

View Replies!   View Related
Rotate Range, Keep Defined Names Consistent.
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.

i.e.
lets say i have the following cells filled:

1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1

etc.

what i need in the end is this:

1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7


View Replies!   View Related
Counting The No Of Cella Given In A Range Defined In Two Cells
I have values in cells which is the address of a different cell. So consecutive columns define the range.e.g.

View Replies!   View Related
Workbook With A Globally Defined Named Range
I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following

View Replies!   View Related
Summing Data In A User Defined Range
I have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.

I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.

qty
week 15100
week 16123
week 1789

How do I sum from 15 to week 17 inclusive or any other range selected?

View Replies!   View Related
Find Blank Cells In Defined Range Name
I am using Excel 2003. I have a range that I have defined in my sheet called "March". Within this sheet, I have a range name called "ACCOUNTRANGE". Basically it comprises the column B10:B200. Within this range, I may be inserting rows so range is dynamic. Well one of my problems is finding BLANK Entries in my range. How do I loop through the range to find BLANK entries and prompt the user that a BLANK entry was found, then it stops the loop and if none is found, nothing happens and continues on.

View Replies!   View Related
SUMIF Function: Sum A Range Of Cells Greater Than Whatever Is In Cell D8
I am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)

The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved