Referring To An Area Relative To A Named Range

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


ADVERTISEMENT

VBA - Referring To Named Range On Different Sheet?

Jun 20, 2012

I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:

ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
'Add new sheet and paste data
ActiveSheet.Name = "Raw_data_Sector_Summary"
ActiveWorkbook.Sheets.Add.Name = "RawData"
Range("A1").Formula = "=RawData!(RawSectorData)"

View 5 Replies View Related

Creating A Named Range And Referring To Its Cells

Nov 30, 2008

here is the snippet of code I'm using

View 11 Replies View Related

Relative References To Named Range

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

Referring To Named Ranges

Dec 11, 2007

I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.

Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.

View 9 Replies View Related

Created Named Ranges Referring To Wrong Worksheet

Aug 22, 2007

I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.

Sub maxLifData()
' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top.
Dim ws As Worksheet
Dim wsName() As String
Dim i As Integer
Dim mCount As Integer
Dim nr1 As Range
Dim nr2 As Range
Dim head1 As String
Dim head2 As String
Dim wsRangeName() As String
Dim maxF As String
Dim fRange As Range
Dim rng As Range
Dim mRng As Range
head1 = "STCN_DPTH"
Set fRange = Range("G3")
Set mRng = Range("A2")
Redim wsName(Worksheets.Count)
Redim wsRangeName(Worksheets.Count).............

View 4 Replies View Related

Named Formulas To Reference Relative Rows?

Jun 17, 2009

Formula: B2+C2

In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.

As it stands I can only get the formula to calculate within the same row.

View 12 Replies View Related

Automatically Round A Named Area

Nov 16, 2007

i have a file that people enter sales figures into each row. the numbers are sometimes beyond 2 demical places.

how would i go about making a macro button, that when pressed, AwardTotal (name of the all the award cells in column e) is rounded to two decimal places. I then would like to sort all the rows that might be inserted, by column "C" (name field).

people will insert rows, with row 11 being the first row with data. Row 10 is hidden, and row 9 is the header row. but the number of rows inserted will be different each time.

I already have a button set up (that someone helped with here of course) that asks how many rows to insert.

View 9 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies View Related

Listbox Referring To Hidden Range

Apr 11, 2014

I have this code for a texbox to search into a listbox

It's working properly but sometimes it doesn't look at all the names in the list.. probably because some of the rows are hidden?

View 4 Replies View Related

Referring To Range() Using Shortcut Notation

Sep 23, 2008

I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.

For example, Range("A1:B10") would be [A1:B10].

I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?

If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.

View 9 Replies View Related

Determining If Cell Is Part Of Named Range And What That Named Range Is?

Aug 16, 2014

Let's say you have a named range, Rng1, which consists of cells A1 & A2. In vba how would you report back what, if any, named range the following cells resides:

Code] .....

here are multiple named ranges so using intersect is not feasible. Essentially, through code, I will be given a range and I need to determine if that range if part of a named range.

View 5 Replies View Related

Indirect Formula Syntax For Referring To A Range On Two Worksheets

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

Sumifs With Date Range Criteria By Referring To Ribbons

Apr 14, 2014

I'm trying to create a sumifs that has 'before date X' and 'on or after date Y' as two of the criteria.

In the past, I have simple done a DATE(x,y,z) function inside the sumifs, but I'm trying to change this for reasons that would take a little while to get into. Let's just say it would make my coworkers lives much easier.

My hope is to get these date range criteria by referring to ribbons which have the dates in mind in them. Here is the formula I have now, and an example. See the red part of the formula.

Say I'm trying to have before Aug 1, 14 and on/after July 1, 14 as two criteria. FBP column A is where I would have the dates that this criteria would search through:

B1: 7/1/14
C1: 8/1/14
Formula: =SUMIFS(FBP!$G:$G,FBP!$B:$B,"=E",FBP!$A:$A,">="&B1,FBP!$A:$A,"

View 5 Replies View Related

How To Insert Date Range Automatically Which Is Referring Current Week

Mar 6, 2014

i got a problem with date range.actually i wanna to insert date range automatically which is referring current week.

View 2 Replies View Related

Defined Names Referring To Nonexistent Range Won't Pick Up Once Existing

Feb 17, 2010

I have created 500+ defined names that refer to worksheets that do not exist (yet). When I add the previously nonexistent worksheets, the defined names that refer to them are not "live" and do not work in functions where that name is used. If you go into the defined names and click on the ones that refer to the now existent worksheet, they start to work, but I was hoping there was a way around this step (e.g. a simple macro that says "update all defined names").

I created all the names so that the person who will eventually be using this will only have to add an appropriately named worksheet and put the data in...then everything will automatically work. I do not think adding names is difficult, but the person who will be using this does, so I want to avoid that person mucking around in my names.

View 8 Replies View Related

Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This

Jun 2, 2006

I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

View 2 Replies View Related

Making Named Range The Last Active Value Used Within Another Named Range?

Jul 19, 2013

I'm trying to make my named ranges remember the values of the last active cells used within another named range. The purpose of this is to make my charts dynamically change dependant on two criteria selected. My spreadsheet currently updates itself as and when I change the active cell within a single named range, dynamically changing the chart data by using Lookup based on the active cells value. However I want to get away from having several charts showing, I would like to have a single chart which dynamically changes based on a second selection. So the first selection is for a department (Facility) which changes the chart data relevant to that department, the second selection is to dynamically change the chart shown for the pre selected department.

Picture2.jpg

Using the following code when updating just one criteria with several charts

VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Application.Intersect(ActiveCell, [MeasureType]) Is Nothing Then
[valMeasurePicked] = ActiveCell.Value [code].....

which works fine but I'm not sure how to add a second selection criteria because my code uses Activecell. I thought that the VBA needed to set the last used value of a range as a variable and therefore allow the second criteria to be selected but am not sure how to put it into practice.

View 2 Replies View Related

Countif- Use A Named Criteria As Well As A Named Range

Oct 25, 2009

if I can use a named criteria as well as a named range. In essence what I am looking to do is count certain cells that meet the criteria in a certain named named range,

View 9 Replies View Related

Combine Two Named Ranges Into 3rd Named Range

Mar 14, 2013

Merge two columns into one list in excel

I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).

Here's the formula from the example:

Code:

=IFERROR(INDEX(List1,ROWS(C1:$C$1)),IFERROR(INDEX(List2,ROWS(C1:$C$1)-ROWS(List1)),""))

I've played around with it, but could not come with any that worked.

View 3 Replies View Related

SUMIF With Relative Range?

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

Using Relative References In Range Names

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

Insert Relative Formula Into Range Via VBA

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

Fill Relative Formula Into Range

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

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 View Related

How To Count Range Relative To Dynamic Reference

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

Use Range Of Selected Cells As Relative Reference

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

Selecting Relative Reference Range For Fill Down?

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

Reference Cells Relative To Activecell In A Range

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

Add Relative/Absolute Formula To Range Macro

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







Copyrights 2005-15 www.BigResource.com, All rights reserved