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