Referencing Named Range Without Using Worksheet Name

Aug 13, 2014

I want to export data from a set of named ranges in workbook x to another set of ranges in workbook y. The names of the ranges to be exported from and to are spesified in a table "Table_Export". Problem is I do not want to spesify in what sheets these respective ranges are located, seeing that I do not know the actual sheet names. But since the ranges are global it should somehow be possible to reference them in vba without referencing the respective worksheet?

The relevant code can be found below where either of the two 'commented lines within the if statement are what I want to accomplish.

Dim x As Workbook
Dim y As Workbook
Set x = ThisWorkbook
Set y = Workbooks.Open(Range("Export_to").Value)
Dim export_control As ListObject

[Code] ....

View 2 Replies


ADVERTISEMENT

Tab Named By Referencing A Cell Within A Worksheet?

May 6, 2008

Is it possible to have a tab named by referencing a cell within a worksheet?

View 9 Replies View Related

Referencing A Named Range Or Sheet Dynamically?

Apr 19, 2013

I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.

View 3 Replies View Related

Macro Referencing Named Range In Worksheets Private Module

Sep 12, 2006

I'm having trouble calling a defined range within a VLOOKUP function in VBA. If the named range is located on the same sheet within which you are running the macro, everything runs fine and all is well in the world.

However, after I relocated the range to a separate sheet (a 'SourceData' sheet to tidy up the user interface sheet), I was getting the following error message:

Method 'Range' of object '_Worksheet' failed

The name is correctly defined - Range("DaysInYear").Select still picks up the correct selection - it's just the VLOOKUP will no longer function correctly.

Here is part of the macro's
For I = 1 To NumberOfDays
Range("A1").Value = DateAdd("d", -(I - 1), EndDate)
If Application.VLookup(Range("A1"), Range("DaysInYear"), 3, False) = 1 Then
If Application.VLookup(Range("A1"), Range("DaysInYear"), 4, False) = 0 Then
ActualNumber = ActualNumber + 1
End If
End If
Next I

View 9 Replies View Related

Referencing Range In Specific Worksheet?

Mar 19, 2014

I've tested and I'm getting errors trying to reference a range in a specific sheet.

I want to sum a range for further calculation and can get this to work perfectly if I don't specify a sheet - but it sums the range in the wrong sheet as the code is within a With statement range:

[Code].....

I've checked that wsSumm contains the 'Summary' worksheet value and that's all fine, I've also tested the following:

[Code] ....

I've also tried replacing wsSumm with Worksheets("Summary") and Worksheets(1), but get exactly the same results, so no it's not a fault with the worksheet variable. Also tried using Application.WorksheetFunction.Sum, but again exactly the same results.

So, why I can't sum the range on the specified worksheet? Is is something to do with being inside a With statement for another worksheet - though I've not come across problems with this before...

View 3 Replies View Related

Using A Worksheet's Named Range In A Chart

Apr 21, 2006

I have a chart with a series whose formula looks like this:

=SERIES("ACTUALS",BOD!$E$42:$AB$42,'OE2_EOM_Mar_06_(20APR_0900)_EDIT_THIS_ONE.xls'!BodOblActData,7)

The named range, BodOblActData, is a named range in the workbook, but how can I change the formula to use a named range in the work sheet?

View 3 Replies View Related

Determine Named Range Worksheet Name

Jul 20, 2007

Given a named range ,e.g., "myRange" how do I know the worksheet name it belongs to?

View 4 Replies View Related

Vlookup (named Range (Start_Date) On Another Worksheet)

Aug 17, 2009

I have 18 columns in a worksheet. (Let's say AA:RR). I've filled A1:R1 with the following series: WEEK 1,WEEK 2,.....WEEK 18.

I also have a named range (Start_Date) on another worksheet. Column 1 in that named range is WEEK 1, WEEK 2,......WEEK 18. Column 2 contains Start Dates for each week. If I input the following formula into cell A10:

View 2 Replies View Related

Copy Named Range Definitions From One Worksheet To Another

Mar 23, 2007

How can I copy the Named Range definitions from one worksheet to another in the same workbook? In case it matters, the Named Ranges refer to cells in a third worksheet in the same workbook.

View 6 Replies View Related

Named Range. Refer To Current WorkSheet

Jan 2, 2008

I put all my named ranges in a seperate worksheet in the workbook.

On a different sheet I have all my data with references to the named ranges. But what it gives me is the cell information from the sheet on which the named range is located and not on the worksheet where I actually need the calculations to be done.

How can I make the named range refer to the cells in the worksheet in which it sits?

Worksheet 1
---------------
1
2
3
4

Worksheet 2
--------------
I name the function Red apples
RedApples =CONCATENATE($A1&"Red Apples")

Worksheet 3
--------------
=RedApples
=RedApples
=RedApples

Question #1
How do I make the name reference in worksheet 2 refer to
the cells located in the worksheet in which i use it. In other words, when I use RedApples in Worksheet 3, it gives me what is sitting in A1 in worksheet 2 instead of in A1 on Worksheet 1.

Question #2
How do I make the name reference in Worksheet 3 keep stepping down along Column A in Worksheet 1?

Formula at issue in actuality is

=IF($S5>0.149,Morethan15,IF(AND($S5<0.15,$S5>0.999 ),Morethan10,IF(AND($S5<0.999,$S5>0.0499),Morethan 5,IF(AND($S5<0.05,$S5>0),Morethan0,IF($S5<0,Lessth an0)))))

the morethan15,10,5,0 and lessthan0 is defined in the last worksheet

View 6 Replies View Related

Change Worksheet Named Range To Absolute Values?

Feb 28, 2012

Have a query that pulls general ledger data. Named Range SALES defined for Sales Account numbers. What is the best way to always have SALES (which pulls as negative number) converted to absolute values?

I have in my mind that it would be some sort of worksheet change event . . . but I don't really grasp the byval/byref TARGET concept by itself, let alone whether it can work on a named range.

View 5 Replies View Related

Save Single Worksheet As File Named From Range

Jun 18, 2007

About 2 weeks ago Reafidy posted a reply to the following thread. Save Individual Sheet Based On A1. As I do not want to hi-jack anyone elses thread so I am starting a new one. The code posted does basicly what I need however I was wondering if it is possible to save only "ONE" specific sheet which could be specified in the code, to a specific folder specified in the code and under a name defined by a cells contents. eg.

Save a single sheet named "MatData" from a workbook, name the saved sheet from a "named range on sheet1" & "Cutlist" & ".xls"
to a Folder on the "D" drive called "Saves"

View 3 Replies View Related

Referencing With Named Ranges

Apr 22, 2009

Ok I have been trying to figure this out for way to long. Say I have a list of values in A1:A4. I have named this range MyRange. If I wanted to refer to this range somewhere else in the workbook and enter =MyRange it will only work provided the "=MyRange" is in the same rows as the original data.

For example if I enter MyRange in cells C1 through C4 it would mirror the data in column A. But if I enter MyRange in C5:C8 I would get a #Value error. Does anyone have any ideas to make this more of an absolute reference.

Basically anywhere I enter MyRange into a cell I want to see A1. If i drag that formula down I want to see A2, A3, A4, etc....

View 9 Replies View Related

Copy Named Range Data To Worksheet Based On Dropdown Or Combobox

Feb 10, 2013

I have a worksheet called "Lookup" with several dynamic named ranges (each is 1 column wide) including facility, department, shift, etc. On a summary worksheet in the same workbook I want users to use a dropdown or combo box (don't care what type) in cell B2 to select a facility and then based on their selection, copy the department named range data and paste it into the summary sheet beginning in cell A5 and paste the shift named range data into the summary sheet beginning in cell B5.

Example: user selects "AR Plant" from the dropdown or combo box and the data from the "AR_Rpt" named range is pasted into cell A5 and the "AR_Shift" named range is pasted into cell B5.

View 9 Replies View Related

Filter OLAP Pivot Table Based On Named Range In Another Worksheet

Mar 14, 2012

I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.

Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.

In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.

Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...

'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem

[Code]....

View 4 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 Replies View Related

INDIRECT And Named Ranges Referencing Closed Workbook

Oct 4, 2005

I have tried using PULL from Harlan Grove's posts to workaround this but am coming up with #VALUE errors.

Here's what I have:

Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230

I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R however it returns #REF!

I need a solution to replace INDIRECT so I do not have to have both workbooks open together. The named ranges are static, but reference external workbooks.

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

Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.

As a simplified example:

Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!

So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.

I was thinking of using something like this:

Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If

I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.

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

Referencing Every Nth Row From Another Worksheet

Dec 19, 2008

I need to create a worksheet whose sole purpose is to return the values from every 17th row from another worksheet in the same workbook.

I have spent a few hours trying to research the correct answer and OFFSET comes close but either I'm doing it wrong or it's not what I need.

I want to be able to drag the (every 17th row from sheet 1) formula down about 100 rows.

Assume A1 as starting place. I would prefer a formula answer as opposed to a macro or VBA.

View 13 Replies View Related

Referencing The Name Of A Worksheet

Jun 24, 2009

way to reference the name of a worksheet in a cell, or even better, use a cell reference to dictate the name of a worksheet.

In other words, I would like to create a new sheet, and set it's name ='Sheet1'!C1 so that if I changed C1, the worksheet would automatically be renamed to whatever is typed in C1.

If that is not possible, I would like to have a cell be tied to the name of a given worksheet, so that if I renamed the worksheet, the cell would change accordingly...similar to the &[Tab] function used in headers/footers.

View 13 Replies View Related

Referencing Another Worksheet

Dec 23, 2009

I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work. The problem arises on a summary page.

When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down. ='Campaign Responses - Cash Break'!A2

This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned. However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from......

View 2 Replies View Related

Referencing Text In A Worksheet

Oct 22, 2007

I have been having problems referencing text in a worksheet.

I tried cleaning it, didn'twork.

When I do T() some cells come back empty. I don't know why?

How do I get the text?

View 4 Replies View Related

Referencing Worksheet In Formula?

Dec 8, 2011

I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!$C:$O,10,0)

I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!$C:$O,10,0)

View 9 Replies View Related

Referencing A Cell In A Worksheet.

Jul 25, 2006

I used to think I was moderately intelligent. Certainly, I thought I knew my way around a computer. Then I got the bright idea to try and learn VBA. Since then every attempt I've made in trying to produce the most basic of code results in mad fits of rage and me wanting to slit my wrists. I've read half a textbook and I keep trying to write simpler and simpler code to get some sort of result, just to give me the drive to carry on but no matter what I try it always goes completely wrong.
So before I stick me head through the computer screen, here is today's attempt. I want to select a worksheet. I then want to copy the contents of cell A2 down to A1200.

Private Sub CommandButton1_Click()
Sheets("Calls Outcome").Visible = True
Worksheets("Calls Outcome").Activate
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1200")
End Sub

I’ve stepped through the code and I get an error on “Selection.AutoFill Destination:=Range("A2:A1200")”. After spending several eight-hour days trying to get something out of VBA I have zero energy and zero patience.

View 6 Replies View Related

Worksheet Referencing And Position Recognition

Jun 4, 2009

I have a bit of a complicated one here so I have attached my book, its probably easier to undertstand the query by looking at the book. Hopefully somebody can help me out here.

I have 2 worksheets:

A results worksheet - this contains all category of results, each category has been given a specific sort id.

A foreign worksheet - this contains all categories found on the results worksheet with sort id = 7.

Now the complicated bit. The whole exercise is too pick up the correct exchange rate for the foreign category (sort id 7). each foreign type has a specific number at the end of its description in brackets e.g (3).

At the bottom of the "results worksheet" is a key which says what type of exchange rate to use for this type e.g. (3) Price of CAD 22.9 converted using exchange rate of CAD 1.9645 = £1.

What I need to do is on the foreign worksheet, it to recognise the number in the item description, keep it in mind then switch over to the "results worksheet" find the keys at the bottom of the page (the key is not necessarily in the same place all the time!!), match the number in the items description with the corresponding key and then find the exchange rate.

As i said very difficult to explain, since I reletively zero experience with this kind of work in excel. Luckily I been muddling my way through a work project gradually thanks to a forum member here. so i hope we can keep this up!

edit:
two restrictions in the way this solution can be done:
1- Results worksheet can not be modified, but it can be referenced to using INDIRECT, and you can make any number of modifications outside of the results worksheet.
2. Any solution must be automatic.
/edit

I have attached my workbook and highlighted the bits I need to fill and the picks I need to pick up.

Would be ever so grateful if some1 could show me the light here.

View 14 Replies View Related







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