I am having a few problems with dynamic named range in excel 2000.
When adding new data to the range, excel extends the range correctly, but only copies some of the formula correctly. It does not copy the formula that references a cell from another line.
I am trying to create a excel spreadsheet and have a formula =e10-e9, which does not copy down.
Can you use Row & Column numbers in a Formula the way you can in VBA?
I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula
because I want to use named ranges for the Row & column entries. (And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)
Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C
(it would = 470)
Also I have an existing Named Range HeaderRowNum (it = 16)
Currently I have a LOT of formulas like:
=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))
problem is any new data must be added between Rows 17 & 470
So I would like to create dynamic new forumlas to read like:
SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )...............
I have Sheet1 which contains a cell named "Limits" which refers to =Sheet1!$B$12.
Now I wish to have Sheet2 which should contain a cell named "Limits" which should refer to =Sheet2!$B$12.
How do I get the reference Limits:=Sheet1!$B$12 (highlighted in red part) to change dynamically according to the sheet I chose?
I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.
Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
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.
I have a spreadsheet where I've disallowed selection of multiple cells using the code in the 2nd box below, but is there a way to override this protection to allow selection of a named range? My reason for doing so is to open this workbook as read only from code in another workbook and copy the entire table. My code for doing that works ok (I frequently use it in other workbooks) but, in this case, my need to disallow selection of multiple cells in this particular worksheet is tripping me up.
This is the code I'm using to try and pull the data into another workbook:
Code:
Workbooks.Open Filename:=ThisWorkbook.Path & "RSL-Jobs-Register.xls", ReadOnly:=True
Application.GoTo Reference:="JobsTable"
Selection.Copy
and this is the code I have in RSL-Jobs-Register.xls which is preventing me from selecting the (dynamic) named range called "JobsTable".
Code:
''' I'm thinking I could have something here like (in plain English) "If selected range "JobsTable" Then"
''' (apply the code below) but I'm not sure of the code
If Selection.Cells.Count > 1 Then
MsgBox "Sorry, operations on multiple selections aren't allowed here", vbCritical
ActiveCell.Select
Exit Sub
End If
The formula for the dynamic named range called "JobsTable":
=OFFSET(Jobs!$A$2,1,0,COUNTA(Jobs!$A:$A)-2,11)
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.
Range("DataSummary),(??,??) doesn't work.
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.
My goal is that the formula in this named range references the prior 12 months. This does reference a twelve month range in my workbook, but not the prior 12 months based on this month being MARCH.
DATES =INDIRECT("sheet1!A"&MONTH(TODAY())+1&":A"&MONTH(TODAY())+12)
I want to copy =d8*k10 into several cells, but the references keep changing. I've tried several things that I've found on the internet, but nothing seems to work and the I can't seem to copy to a columnof cells.
View 3 Replies View RelatedI have a spreadsheet that imports data from a database and into pivot. Several departments use that spreadsheet, but depending on the department, they may want to see e.g. 3, 4 or 5 different fields than other departments.
My spreadsheet also includes a great number of formulas included in a macro, so whenever I get a request to include field X, Y and Z, I end up spending a lot of time adjustning all my formulas to reference the new fields.
I have one sheet with all the formulas for the entire workbook and would like to copy and paste the formulas from Sheet 1 to Sheet 2...Sheet 1 to Sheet 3 etc. without changing the cell referening in the original formula. I am not too sure how .formula works.
Sub CopyAndPaste()
' To copy formulas from Summary sheet to their respective sheets
With Worksheets("Summary")
.Range("R3").Copy Worksheets("2").Range("X3").Formula = Worksheets("Summary").Range("R3")
End With
End Sub
I have this fairly simple formula which decides whether to shade a cell or not
=AND($X$1<>"TBD",R3<>"None",AC3="Y")
This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.
I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.
E.g.
If the formula on Sheet1 is:
= sum(A1:B6)
then the copied formula on Sheet2 would read
=sum(Sheet1!A1:Sheet1!B6)
You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.
I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
This works
ws2.Range("C5").Formula = "=sum(D5:ZZ5)"
guide me on the syntax when the final column reference is a variable
ws2.Range("C5").Formula = "=sum(D5:" & Cells(5, l_LastCol) & ")"
doesn't work
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).....
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View RelatedI need to create a dynamic named range called "DIVLOC," that corresponds to a sheet called Divloc_List in the workbook. Then, I need to take this range and perform a vlookup against it. I tried to create the range in the sheet itself (it wasn't dynamic), and then used that in the vlookup in the vba code, but I got error 2402 (I think). I'm not sure how to define the dynamic range in vba code, and then use the range in the vlookup.
View 9 Replies View Relatedwhat would cause the formula for a dynamic named range to keep changing on its own? I enter the following formula:
OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!D:D),1)
Which I save and somehow gets changed to the following formula:
OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!XFD:XFD),1)
This is happening to several named ranges I created. I’ve tried to correct this by editing the formula which seems to work but changes again. I’ve also deleted the named range and recreated it with the same change process taking place.
In case it matters the named ranges are being created on a table imported into Excel via MS Query.
I am trying to create a dynamic named range to select a list of text entries from a number of different columns that I am using for Data Validation. The columns have entries of variable length, none more than 30. What I am seeking to achieve is that having selected a heading in cell C3 the available list in DV dropdowns elsewhere are governed by the contents of C3.
I have created a defined name, StartPos that uses the contents of C3 to evaluate to the cell reference (e,g $H$4) of the topmost cell in the relevant column. However when I try to use it in the usual formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) formula I try
=OFFSET(StartPos,0,0,COUNTA(StartPos:[ ],1)
I get stuck at what to put in the brackets. Ideally I would like to use the COUNTA to go down to the last entry in the column. However, even when I try to give the range a height of 30 rows:
=OFFSET(StartPos,0,0,30,1) I still get an error.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have.
So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1)
$C$15 is the first cell that holds the data for the series.
Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change)
If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
I am doing an Advanced Filter, and I am using a named range in my VBA code that decides the Criteria range.
The dynamic named range is called 'Criteria' and this is the code:
VB:
=OFFSET(Sheet1!$A$2,0,0,Sheet1!$BZ$23,41)
However, when I run the VBA code, although it works perfectly, afterwards the named range has changed to:
VB:
=Sheet1!$A$2:$AO$3
I just want this to remain unchanged, as it works great. This is my VBA code:
VB:
'Filter and send data to new sheet
Sheets("Sheet2").Select
Cells.Select
[Code]....
I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--
A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)
The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.
What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.
------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--
create a dynamic named range with dates.
Example file. !
Dynamic-test-sheet.xlsx
I need an dynamic named range for column B(Project) with a date criteria.
so for example: I need a range with all records from Colomn B(Project) from year 2013.
I want to use this range to filter all unique projects by year.
I have already a filter/formula to collect a unique list of all projects.
I have a named range in a column for January data (non-contiguous cells). Every month I insert new data in the column to the right. Is there a way to have a dynamic range that grabs the same exact cells but in the new column but unselects the previous month data? Spreadsheet attached.
View 1 Replies View RelatedIn the attached workbook, I have identical sets of data in column A separated by an blank cell. I need a macro for the user form that i need to build. The code needs to find the end of each named range, (Range1,Range2 and Range3), to insert blank row at the end of each range and to copy the value of the textbox from the user form in each row so to keep the sets of data identical.
View 9 Replies View Related