Relative Indirect Formula To SUM Range
Feb 13, 2008
I am using VB in Excel to write a formula for summing a column of numbers that will vary in length. I have been using the Indirect formula and have the start and end cells of the range identified in another location. My problem is, the macro in vb is sending the Indirect formula to the cells containing the start and end points by using relative functions ie. go across 30 columns and up 10 rows to get start cell. I wanted it to just save the cell location of the start and end points.
EG.
VB formula is: "= SUM(INDIRECT(R[-23]C[27]&R3C29):INDIRECT(R[-23]C[27]&R3C30))"
whereas I just want it to read "=Sum(Indirect(C4&D5):INDIRECT(C4&D6))"
where cell C4 gives the column letter and D5 the row number of the start of the range and C4 and D6 give the column letter and row number of the end of the range.
View 9 Replies
ADVERTISEMENT
Oct 6, 2006
I am setting up a spreadsheet for user data entry. I have one sheet set up as a template to enable users to copy the required data header cells to subsequent sheets and (the problem) - to different locations on the subsequent sheets. The template is using validated lists with the criteria drawn from the cell/list directly above the current list. For example, the cell in R11C2 is validated/refering to the range: =Campaign
The cell directly below this is validated/ filtered by: =Indirect(R11C2). This works great in the template, or any subsequent sheet in which the cells are all located in the same row/column. However, when the template is pasted in a higher row, the Indirect refers to R11C2 rather than referencing the cell directly above.
View 2 Replies
View Related
Nov 25, 2007
I am updating a wsheet via a form using vb to locate next empy row etc.
The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
Previous entry to sheet:-
A1=1
B1=2
c1=A1+B1(formula)
New entry via form selecting next empty.
A2=(updated from form.)
B2=(Updated from form.)
C2=(Update code VB)
This is all okay and functional the problem starts from here
I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.
View 3 Replies
View Related
Jan 23, 2008
Here is my array code
Dim Values() As String
Dim count As String
count = 9
For count = 9 To 1800
Values(count) = "=IF(AND(E" + count + "<>"""",AH" + count + "<>0),(AH" + count + "/E" + count + ")*100,0)"
count = count + 1
Next
I'd make it a static array but eventually I'm going to have to set it up where I wont know the upper bound.
Here is the other part of my code that I don't know what I'm doing.
If Range("A9") > 0.01 Then
Range("AI9:AI1800") = 0.01
Else
Range("AI9:AI1800") = Values
End If
I keep getting mismatch errors on the count variable, but I need it to be a string so I'm not sure what do here.
View 9 Replies
View Related
Jan 10, 2008
I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).
View 3 Replies
View Related
Sep 22, 2007
using a macro im trying to paste in a sum formula, however this will be pasted onto different sheets and the number of records will be different for each so i cannot use absolute locations. the only constant is that all ranges will start in row I9. when it runs the following code
Range("G" & (ActiveCell.Row)).Select
ActiveCell = "Total"
Range("I" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(I8:R[-1]C)"
Range("J" & (ActiveCell.Row)).Select
ActiveCell.FormulaR1C1 = "=Sum(J8:R[-1]C)"
Range("I:J" & (ActiveCell.Row)).Select
Selection.Copy
Range("I4:J4").PasteSpecial
it fills in =SUM('I8':I13) how can i make it insert it without the ' ' around the I9? and how can i select the two cells? (again, cannot be absolute..)
View 3 Replies
View Related
May 26, 2008
In my data validation I have the following formula as the Source: =INDIRECT(SUBSTITUTE($F$2," ","_")). I want to be able to copy the cell and paste it into another cell and have the formula update to reference the new cell. Currently the validation is for cell G2 and references F2. I want to be able to copy G2 and paste it in G3-G6 having the formula update so it refrences F3-F6.
View 3 Replies
View Related
Jun 10, 2008
I have a formula that works fine in the cell of an Excel spreadsheet but I'm struggling to translate it into VBA (your help please).
The cell formula is:
=If(B2="", "", B2 & " (version: " & F2 & ")")
I want to iterate through all rows in my spreadsheet (about 2000) incrementing the relevant row numbers in the formula @ each pass - eg changing B2 -> B3 -> B4 etc and F2 -> F3 -> F4 etc where column 'B' contains the name of the product and column 'F' contains the version number, resulting in "Product Name (version: 123)" per row.
The closest I've got to this is:
Sub LookupNameInColumnA()
Range("A2").Select
Dim i As Integer
For i = 1 To Selection. CurrentRegion.Rows.Count - 1
ActiveCell.Formula = "=IF(B2="""", """", B2 & "" Version: 999"")" ' problem line?
ActiveCell.Offset(1, 0).Select
Next i
End Sub
This works OK'ish but I want each line to reflect the different data per row. I'm struggling to increment row 'B' and row 'F' in the formula, having tried "B & i + 1" and "F & i + 1" but am getting confused with quotes and concatenating strings within formulas.
View 5 Replies
View Related
Jul 5, 2012
B1=Name of WorkSheet
B2=Name of 2nd Worksheet
I am trying to write a sum formula using Indirect so that the end-user can enter the names of the sheets in B1 & B2 (the values are always in cell F5 on each sheet). I thought this would work but it is throwing a REF# error.
=SUM(INDIRECT("'"&B1&":"&B2&"'!F5"))
with some added spaces for clarity:
=SUM(INDIRECT(" ' " & B1 & " : " & B2 & " ' ! F5"))
View 9 Replies
View Related
Mar 16, 2008
Is there a way to absolute reference multiple cells at one time?
View 5 Replies
View Related
Dec 27, 2012
I have data headings in A1-E1 and actual data in A2-E2. I have a SUMIF function in F2.
A B C D E F
A B A B B
1 2 3 4 5 =SUMIF(A1:E1;"A";A2:E2)
When I insert new data columns between column E and F, the formula still refers to columns A-E although I would like to have the newly added columns in that SUMIF-function included.
For example, if I add 3 new columns, the new SUMIF-function (now in cell I2) should become =SUMIF(A1:H1;"A";A2:H2).
Do to modify the SUMIF-function to do that?
View 4 Replies
View Related
Jan 17, 2005
I have a spreadsheet with very long formulas that reference a whole lot of variables in the same column. I tried naming the cells so that writing of the formula is easier to do. However, I need to copy the column across for other entities of varying nature, and these copied cells refer to the same named cell. Can I name a cell and copy it sideways so that it retains some sort of name with allusion to the column that it is in?
Andrew van Gruting"
I have tried copying a formula with a named range and, as expected, it maintains an absolute reference. Is there a way that he might be able to copy but drop the absolute reference and copy with relative references. Can I place something in front - the equivalent to the $ sign, but rather a relative sign.
View 6 Replies
View Related
Jun 5, 2009
I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).
I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.
Thus I can paste new data in the new worksheets, and have all my calculations done.
Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.
Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)
View 4 Replies
View Related
Dec 5, 2006
I was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.
We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2
Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,
but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....
I did some searching on the internet and found samples only with the numeric based sheetnames.
View 10 Replies
View Related
Mar 6, 2009
In cell A1 I have the text Testme
Testme is a Named Range
with RefersTo set to:
=Sheet1!$D$3:$D$5
In VBA - How do I
Range("A1").copy Range("D4").PasteSpecial xlPasteValues
And D4:D6 will show
123
456
789
This thing is beating me up -- Indirect is likely involved, but,,,
View 9 Replies
View Related
Aug 18, 2009
I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
View 3 Replies
View Related
Feb 24, 2009
Is there a way to write formulas so the variables are relative to a sheet?
I have a workbook that collects monthly electrical meter readings and calculates billing for each tenant. A new sheet is used for each month and the formulas need to refer to the previous month’s sheet for the prior meter reading.
For example cell C36 in the sheet Feb09 might contain: =Jan09!D36 Where Jan09 is the sheet name from the prior month. When I copy sheet Feb09 to make sheet Mar09 I’d like the formula in C36 to be =Feb09!D36.
View 9 Replies
View Related
Aug 6, 2008
I'm importing comma delimited files into Excel. These files will contain formulas, but do not know on which row they will end up in once imported. My formula's need to reference the row they have been pasted to. For example, If I paste the formula on Row 2 the formula would be =A2+B2
Row 3 =A3+B3
However, since these formulas are coming in via a non-excel file, I need the cell reference to be dynamic. Something along the lines of =A#+B# where # would be interpreted by Excel as the current row the formula is on. (I know that '#' isn't correct.)
View 2 Replies
View Related
Apr 1, 2014
I am trying to create a formula to count a range relative to a dynamic reference (at least I think that's you would phrase it).
I thought I was on to a winner with this:
Formula:
[Code] .....
But it doesn't seem to accept the : as a legal operator.
I suspect the answer is to use SUMPRODUCT somehow.
View 6 Replies
View Related
Sep 7, 2009
I'm working on or any part of it on here, because the work belongs to my employers and would constitute the loss of trade secrets. It would also take me way longer than I have the time to invest to put together a functional duplicate of the relevant part of the sheet just to be able to post it.
With that in mind, what I'm trying to do is refer to part of a range that spans five columns (though it could conceivably span more in the future, so I'd rather not count on that detail) and a constantly changing numbers of rows. The part I want to refer to includes all columns, and all rows except the first and the last in the range.
View 5 Replies
View Related
Mar 8, 2013
I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).
For example, I have this:
A
B
C
D
E
1
No
Not Very Far
[Code] .....
I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:
A
B
1
No
Not Very Far
[Code] ........
So far, I have this:
Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])" '
View 9 Replies
View Related
Dec 11, 2013
I'm trying to use relative references and am have having trouble with selecting a range for the fill down command.
I'd like to copy a formula down in the last column from row 1 to the last row with data. Column D is static so I can always use it to find the last row. The last column will vary; sometimes it might be G another time it might be X. I'm able to get the formula entered into the proper cell but I can't figure out how to select the range for a fill down.
If I was not using relative references and cell X1 always had the formula then Id code it like this:
'*** Find last row with data
Range("D1").Select
LastRow = Range("D65536").End(xlUp).Row
'*** Calculate length of cell in previous column
[Code]....
It's the last range statement that stops me. I haven't come up with a variation that allows me to select a range from the cell on the first row of the last column and select the last row with data for the same column.
View 3 Replies
View Related
Nov 4, 2008
I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......
*****end pseudo
I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....
Here is my code so far:
Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select
'Do for all cells in the row
Do
ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True
******End Code
Range doesn't like activecell.offset. Does anyone know how to do this?
View 9 Replies
View Related
Dec 27, 2012
In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.
On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.
Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.
I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?
Dynamic Range.xlsx
View 8 Replies
View Related
Oct 9, 2003
I'm trying to use Indirect in a formula where named range refers to multiple non adjacent cells; e.g. ever other cell {A1, A3, A5... etc}; however, when I use indirect, I recieve a #ref error. Replacing the indirect with the actual named range will make the formula work just fine, or using a named range that a continuous range works fine as well. It seems that indirect doesn't like non continuous ranges. ?
View 6 Replies
View Related
Jun 29, 2012
$TC$2:$WX$3
is an indirect range that resides in cell B15. It is constantly changing and the expectation is that the X_Y plot would adjust accordingly. It represents the data range of the chart. The chart does not carry with it any title.
So how do I approach this without using vba? As always any input is highly valued.
View 2 Replies
View Related
Jan 18, 2013
I have the simple code below to perform and filter and copy:
Code:
Sub Macro1()
Sheets("Data Dump").Select
Range("A1:F100000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"K1:L73"), CopyToRange:=Range("P1:U1"), Unique:=False
End Sub
However, the CriteriaRange can change depending on which OptionButton the user chooses on a different tab. My reference the range is in Cell N1. So how can I use Indirect to refer the range in Cell N1 instead of the current CriteriaRange of K1:L73?
View 5 Replies
View Related
Oct 27, 2013
can I put formula to cells on relative basis?
For instance can I write "=A1 + B1" and have a change event the formula populate for the corresponding column that changes? such that a change in C7 for instance populated the result of the formula for A7 + B7 in cell D7?
View 7 Replies
View Related
Dec 8, 2008
It's been a long time since I have worked with Excell, it use to be the " $ " that made things absolute or relative.
I have this formula:
='1'!C194+C193 on sheet 1 named "1" without the " ". I need to copy it across 31 sheets. I thought that it was the " ! ", without the " ", that makes the address absolute so I took it out and then attemped to copie it to other sheets. The idea was that the formula would then read: ='2'!C194+C193 on sheet 2, and ='3'!C194+C193 on sheet 3 and so on. The formula is in the exact same location on each sheet, C194. But this won't work. I am at this time doing each formula one at a time. I have many workbooks that will use the same style of copy and paste; about 15 workbooks each with 31 sheets.
It would help if someone could explain how to save time. I do know how to use the Ctrl to select different cells and how to use Ctrl+Page UP/Down to change tabs. I just have to get the formula correct.
View 14 Replies
View Related
Sep 21, 2009
I currently have a list of totals that i want to easily be able to change the cells that make up this total.
I have been trying to set up one cell that contains the formula and then the remaining totals below are linked to this cell. Thus I change the formula in the top cell all the other totals also change.
View 9 Replies
View Related
