Variable Cell Ranges In Formulas
Feb 23, 2008
I need to run a formula each month which calculates the ‘product’ for a range of cells in a column, where the cell range will change each month. The starting cell for the range is static, but the range end will always change - additionally, the last valid cell will always be followed by a null value cell. The attached example shows the basic format of my data – a range of values, by row, which will always end with a null value cell. The formula I’m using (to match my example) is: =(PRODUCT(A10:A13) –-> and I want to display the result in cell C10.
While I’ve been able to identify the ‘ending good row’ several ways, I haven’t been able to figure out what to do with this information – I seem be be lost in the translation of OFFSET values to CELL REFERENCE values. Has anyone encountered this specific scenario and found a solution?
View 3 Replies
ADVERTISEMENT
May 2, 2014
I'm working on a project whereby ranges of values are "scored".
The current layout shows ranges of within cells, and a hidden row with single values for the formulas to utilize. This means that whenever ranges are adjusted, the singles values also have to be changed.
I've attached a portion of the spreadsheet as an example.
Is there a relatively simple way to have the formula recognize the ranges as values and "score" accordingly?
If there is, can this be used for both ascending and descending ranges?
I should add that the ranges are not always percentages.
View 9 Replies
View Related
Aug 12, 2014
I would like to add another step that pastes the formulas copied from the active sheet, to specific ranges of cells.
I would like the PasteSpecial XlFormats to apply to all cells on the active sheet, where as the PasteSpecial xlValues would only apply to all cells not in the ranges that the Formulas are getting pasted into.
Also for formatting purposes I have a few cells that are merged in the active spreadsheet, that unmerge when I run the macro. Ideally I would like any previously merged cells to stay merged once the macro is run.
Below is the VB I have...
VB:
Sub SaveSheet()
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
[Code]...
View 3 Replies
View Related
Nov 30, 2008
I am working on a project to import cellular phone usage data from Sprint into a workbook.
All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.
The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.
I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.
Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................
View 17 Replies
View Related
Mar 7, 2013
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
View 1 Replies
View Related
May 31, 2009
I am trying to combine three IF formulas that depend on ranges that vary. I think the attached sheet does a much better job of explaining what I am looking for than I can do.
View 3 Replies
View Related
Feb 18, 2009
I am having trouble entering a formula that measures how many times "Open" occurs in one column IF another column's entry is between 2 dates or within one particular month time frame. I've tried a bunch of formulas but can't seem to get anything to work!
View 9 Replies
View Related
Dec 23, 2008
i have an excel sheet with two columns, and depending on the status of the equipment, i need to compare the two columns and if the data is the same, fine, otherwise i need to display a msg box with an error. the columns have formulas in them, and i assigned a name to the cells i want to check, i keep getting errors with the following code, when the colums are the same, it is still displaying the msg box with the error. what am i doing wrong?
For Each Row In Range("Dev_Found") 'Loop through each row in Column C
If Row.Value ActiveWorkbook.Names.Item("Dev_Left").Value Then
blah = MsgBox("Your % Dev for after does not match % Dev before, please correct on form!", vbOKOnly, "Error")
Exit For
End If
Next Row
View 9 Replies
View Related
Apr 18, 2006
I am wanting to build a macro to calculate the average of a range of cells. I have about 2000 lines of data, and I want to average the first 12 cells (then paste the answer somewhere else), then average the next 12 cells, and so on. Using a loop to do this is simple enough. My problem is that I can't insert variables into the average formula as the cells to be averaged
Sub AutoAverage()
For x = 0 To 20
For y = 0 To 171
FirstRowRef = Workbooks("NP FT01-03 010206.xls"). Sheets("NP - FT01") _
.Range("a5").Offset(12 * y, 12 * x)
LastRowRef = Workbooks("NP FT01-03 010206.xls").Sheets("NP - FT01") _
.Range("a16").Offset(12 * y, 12 * x)
Workbooks("Mega Spectrums.xls").Sheets("NP - FT01").Range("a5").Offset(y, x).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(FirstRowRef:LastRowRef)"
Next y
Next x
End Sub
View 2 Replies
View Related
Sep 29, 2006
I have been handed a workbook which has been used and amended by a number of people over about 5 years. The workbook has lots of named ranges, many of which are obviously now defunct. Others may be defunct but I'm not sure. I would like to delete all of the defunct ones.
Does anyone know a way in which I can identify the cells in which a named range is currently being used?
View 8 Replies
View Related
Oct 3, 2007
I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop.
Sub TransitionMatrix()
Dim P1 As Range
Set P1 = Cells(2, 2).Resize(3, 3)
Dim P2 As Range
Set P2 = P1.Offset(5, 0).Resize(3, 3)
P2.Cells(0, 1) = "P2"
For i = 1 To 3
For j = 1 To 3
P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")"
P2.BorderAround Weight:=xlMedium
Next j
Next i
Dim P3 As Range
Set P3 = P2.Offset(5, 0).Resize(3, 3)........................
View 3 Replies
View Related
Oct 22, 2007
I have a Formula = SUMIF('Master List'!B3:B190,AE197,'Master List'!AG3:AG190)
When I copy this formula the next line is as Follows =SUMIF('Master List'!B4:B191,AE197,'Master List'!AG4:AG191) Is there something I can do to in order to make so that when I copy the formula is would read =SUMIF('Master List'!B3:B190,AE198,'Master List'!AG3:AG190). Elliott Auto Merged Post;Nevermind I used the formula = SUMIF('Master List'!$B$3:$B$190,AE197,'Master List'!$AG$3:$AG$190)
View 2 Replies
View Related
Jan 25, 2012
I have a workbook with several sheets and named ranges like ES_Date, ES_Range, ES_Volume or MC_Date, MC_Range, MC_Volume. The 1st two letters are the name of the sheet where the named ranges are located (one is dates, another volume, etc.).
What I'm trying to do is calculate averages, extract maximum and minimum values from the the named ranges using a start and end date that are entered in cells "H6" and "I6". The formula I am using right now is:
{=AVERAGE(IF((ES_Date>=H6)*(ES_Date
View 2 Replies
View Related
Mar 18, 2007
I am trying to write a macro that will cut the text in a given cell to 16 characters. My code works as long as the text is always in the same cell, but I eventially want to ask the user what cell to use. So I want to replace the “RC[-25]” with my WorkCell variable. I can’t seem to figure out how to put it in there.
Sub test()
Dim SizeOfRange As Integer
Dim WorkCell As String
SizeOfRange = 10
WorkCell = A1
For x = 1 To SizeOfRange
Range("z" & x).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-25],16)"
Next x
End Sub
View 6 Replies
View Related
Nov 17, 2007
I need to select in a sheet the cells which have name reference to see if the whole sheet is dependent , i have tried to trace the dependents and precedents but that didnt work,
View 5 Replies
View Related
Jun 20, 2014
So I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
222.xlsm
View 13 Replies
View Related
Dec 31, 2006
In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?
Here is a sample cell formula:
=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))
The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.
GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.
$A$2:$A$332 is the column which contains the G/L account number to be matched.
$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.
$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.
I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.
View 12 Replies
View Related
May 8, 2008
I have an Excel File with 2 worksheets.
Sheet1 contains column A as "Product Number", Column B as "Product Family". Rows contain about 20,000 lines of data. A Range has been created to cover both columns, and named "ProdRange"
Sheet2 contains Column A (blank), Column B with "Product Number"
What I am trying to do is start at the top of Sheet 2 "Product Number" (B2 is first cell ref), and use this value to do a Vlookup on Sheet 1, to extract the "Product Family" code on the Offset 1 - and insert it back into Sheet 2 Column A.
I have this below :
For i = 2 To LastRow
Range("A" & i).Value = "=VLookup("B" & i, ProdRange, 2)"
Next i
BUT for some reason it doesn't like the [ "B" & i ] part of the equation.
View 9 Replies
View Related
Nov 3, 2006
I cannot seem to get the worksheet function sum to work with varables in this module. It is placing zeros where the summed data should be. The variable are showing proper start and end ranges for summation....
View 5 Replies
View Related
Jul 11, 2008
Need to pull data from Sheet4 to sheet1 by Sales Person based on a validation list cell on sheet1.
Each Sales person has a different number of accounts listed on Sheet4. The data is setup like this (my apologies for not knowing how to copy and paste the data)
Will Use Jane Doe and John Smith As examples-
A1, Jane Doe, Customer Name, Data, Data, Data, etc.
A2, Jane Doe, Customer Name, Data, Data, Data, etc.
A3, John Smith, Customer Name, Data, Data, Data, etc.
So Jane Doe has 2 customers total, and John Smith has 1 customer.
I am dealing with a total of 300+ Sales People and over 4,000 customers, all with a different number of customers per sales person.
How in the world can I write a code that will pull all of Jane Doe's customers when she is chosen from the validation list, and paste those customers and their coinciding data where I need it to paste?
I have the validation list working, so all I need at this point is help with the copy/paste code.
View 9 Replies
View Related
Sep 17, 2009
I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.
I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.
=COUNTIFS(Extract!B:B,"
View 9 Replies
View Related
Aug 23, 2008
Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE
Welcome to Ozgrid.
Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
View 8 Replies
View Related
Aug 10, 2007
In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.
View 9 Replies
View Related
Oct 25, 2007
Need formula which can sum Amounts from varying Weekly time periods and the result be recorded in the appropriate month? I've attached a simple example of the way the output needs to look and a sample data table below.
View 3 Replies
View Related
Oct 10, 2013
I have a reference dataset that looks like:
Adweek Start_Date End_Date
201201 05-Jan-12 11-Jan-12
201202 12-Jan-12 18-Jan-12
...
In the dataset I would like to merge the variabe Adweek, looks like:
Date Sales
05-Jan-12 $100
06-Jan-12 $110
...
15-Jan-12 $150
...
I'd like to get to here:
Date Sales Adweek
05-Jan-12 $100 201201
06-Jan-12 $110 201201
...
15-Jan-12 $150 201202
...
View 2 Replies
View Related
May 5, 2009
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2()
Dim WhatToPrint As String
WhatToPrint = Sheets("Reports").Cells(3, 6).Value
'sets the variable to equal the contents of cell D3 which contains the formula
'summarising the print ranges I want to print
Sheets("Reports").Cells(3, 6).Select
ActiveCell.FormulaR1C1 = WhatToPrint
' pastes the variable in cell F3 - just to check that it looks like I want it to
Sheets("Reports").PageSetup.PrintArea = WhatToPrint
'uses the variable to set print area - this is where it fails!
'if you replace the variable with the contents of cell F3 the macro will work
ActiveWindow.SelectedSheets.PrintPreview
End Sub
View 3 Replies
View Related
Jun 22, 2008
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
View 5 Replies
View Related
Mar 10, 2014
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB:
'activate sheet and chart
Worksheets("Home Page").Activate
ActiveSheet.ChartObjects("Histogram").Activate
'set variables for chart
With ActiveChart
[Code] .....
View 1 Replies
View Related
Feb 13, 2007
I am trying to use a combination of dependent named ranges and variable length ranges, so one can select in eg Col A truck name from a drop down list, and then in Col B, the engine variant only for that make of truck. The indirect(substitute) function works well if I define the dependent range name (the engine variant) using actual cell refs. However if I use the variable length name definition as described in Ozgrid (ie with offset function), I get an error message and the dropdown menu freezes. Is there anyway to overcome this without resorting to VBA?
View 4 Replies
View Related
Nov 4, 2009
I have attached the xls. I have an input sheet with 3 columns to enter data. Each column is linked to a separate worksheet with a formula (Carrys 1000 rows long). I need to be able to pull the populated data from those 3 worksheets and paste into 1 column continuously on another worksheet so all data is on top of another without any spaces.
I made an if statement so that if there isn't data pulling from the input sheet the a blank cell is left to indicate the last row to copy data from and paste on the final sheet.
View 5 Replies
View Related
