Access/Reference Named Range In Another Workbook

Oct 5, 2006

I have two workbooks wkA and wkB. I have a named range in wkB called BName.. can someone tell me whether it is possible to set a range variable (say, raA) from wkA to this named range in wkB. I have tried something to the effect of set raA = wkB.range("BName") but this doesn't work.. I've also tried set raA = [].range("BName") to no avail. I'm wondering whether named ranges can actually be read from other workbooks.

View 4 Replies


Using INDEX With Named Range Reference

Nov 14, 2008

I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:

View 14 Replies View Related

Cascading Ranges - Vlookup (make My Reference Range Access Multiple Columns)

Feb 10, 2010

I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?

View 5 Replies View Related

Embed Named Range Into Table Reference

Sep 15, 2014

I have a named range called ReportMonth and I want to embed it into this Table reference to make the number of columns that are summed dynamic:


I tried ="SUM(tblFcastTemplate[@[Column"&(ReportMonth+1)&"]:[Column28]])"

View 4 Replies View Related

Creating Column Reference From Named Range?

Apr 22, 2014

I am attempting to obtain the last non-empty row in a column of a spreadsheet by using the following formula


This works fine.

However, I'd like to make it more dynamic and be able to obtain a usable column reference (i.e., the A:A portion of the formula) from a named range (single cell).

View 1 Replies View Related

Passing A Value In A Cell To A Named Range Reference

Apr 8, 2008

I have a formula which is using named ranges-


where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38

The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)

Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.

I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.

The goal would be to have a couple of rows with the following that could be edited with new values

View 9 Replies View Related

Convert Named Range To Cell Reference?

Sep 18, 2012

I'm working with a very complex financial workbook with over 3,000 named ranges and would like code to replace the named ranges with the cell references and display it in a message box so the user can easily trace the references without removing the named ranges from the formula. There is a lot of code with the named ranges, so actually converting them to cell references is out of the question.

I found this code, but obviously it physically replaces the range names in the cell. Hopefully it can be converted to display it in a text box.

Sub FixReferencesToCellNames()
Dim c As Range, n As Name
For Each c In Range("A1:IV65536").SpecialCells(xlCellTypeFormulas)


What I am looking for is that a user select a cell that they want to see the references, run the code to see the cell references for that particular cell in a message box.

View 5 Replies View Related

Indirect Function To Reference A Named Range

Apr 30, 2009

How would I used the indirect function to reference a named range in the workbook in formula creation.

For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:


In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.

I'm using Excel 2007.

View 9 Replies View Related

Reference Last Cell In Dynamic Named Range

Nov 14, 2006

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.

View 4 Replies View Related

Reference Non-Contiguous Named Range In Function

Feb 14, 2008

I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933

This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.

View 3 Replies View Related

Data Validation - Cell Reference To Named Range

Nov 7, 2012

If I have two cells and a named range mexico

A1, A2.

A1 contains the string: "mexico"
A2 is the cells that has required data validation (drop down list).

I want A2's validation reference to be dynamic, in the sense that I can it reference it to A1; converting the string "mexico" to a named range mexico

I tried inputting = INDIRECT(A1) into A2

But I lack clarity in my understanding of referencing.

What is the correct procedure here.

View 2 Replies View Related

Copy Data Over Based On Reference And Named Range?

Jul 22, 2014

I got a very technical sheet which needs your experience to work.

open the attached file below to see the workbook.

It has 3 different sheets. "RESULT", "INPUT" and "SWITCH"

On "RESULT" Cell (O11) is where the person would put a Reference.

This reference is then checked on the "INPUT" sheet column A.

From the first cell it appears in Column A, until the last cell it appears in Column A should be the Row Range (Blanks included).

But I want that range (Column A to G) to be copied to "Switch" sheet. Pasted in Cell A1 downwards.

In the "SWITCH" sheet I have already given all the GREEN Cell formated areas a name - INFO.

I want all the data in INFO to be inserted into "RESULT" sheet between row 18 and 19.

I have colour coded most of the areas for you.. And added a button from which the Macro / VBA should run when clicked.

View 1 Replies View Related

Reference Named Range By Position Macro Code

Mar 11, 2008

This seems really basic, but I can't seem to find it...

Using Visual Basic, if I have a named range, lets say like "NAMEDRANGE", how can I refer to cells in that range by their position in the range? For example, if I want to refer to the cell in the 2nd row and 3rd column of the range.

Also is their an easy way to refer to the first(top-left) cell in a named range?

View 3 Replies View Related

Circular Reference With Dynamic Named Range Used In Formula

Aug 13, 2008

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.

View 2 Replies View Related

Using Cell Reference As List Title When Transposing Named Range

Jun 21, 2014

I am building an interactive sheet to profile a number of divisions based on a summary sheet of about 1200 rows and 90 columns of profiling criteria

I have attached an example but essentially:

1. I would like to be able to select from a list a value which would also be a named range title
2. Have the range information transposed into a row

What works:
1. I can transpose the range information when I use the following formula
I used OFFSET(Governance,COLUMN()-MIN(COLUMN(HGovernance)),0)

The problem:
I cant get it to use a cell reference (i.e. When I select Governance out of a Drop down list of Finance, HR, Governance etc). Having to type in "Governance" to bring back governance range information is fine, but not great when I am trying to profile the Finance or HR division as it then becomes too manual.

View 11 Replies View Related

Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter


and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

View 1 Replies View Related

Copy Named Range From Another Workbook

Dec 27, 2009

In my project I have two workbooks. I am working on getting one to pull data from another depending on which employee is selected from a drop down list.

TestLOG.xls contains a worksheet for each employee, with named sections within for various training the employee has, and down each row has information on the date this was received, initial, recurrent, etc. It is only this single worksheet for each employee that any data entry occurs, which makes things a lot more organized and efficient.
TestFORMS.xls is the workbook accessed by the records department which has different worksheets depending on what data is to be presented. When an employees name is selected, I need the form to pull the specific data from another workbook, and post it on the current worksheet. This get repeated a couple times to fill the adjacent columns of data. I can't just select the whole table from the other workbook because in this current worksheet for example, only specific columns are pulled from the other workbook.

To better explain the flow...
Current workbook is TestFORMS.xls
Current sheet is Test
Closed workbook is TestLOG.xls

In TestFORMS, sheet Test, when named cell Employee is selected with a value...
Then open TestLOG in the background and open sheet of same name as Employee, and cope range ACtype.
Back in TestFORMS, sheet Test, paste the ACtype data in the range named Type.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ename As String
Ename = Range("Employee").Value
If IsEmpty(Ename) Then Exit Sub
Range("Type").PasteSpecial Paste:=xlPasteValues
End Sub

Within the same sub, I would add additional lines of code to copy and paste the other ranges needed on the current worksheet.
My current hangup is that I get an error at Range("ACtype") as that is not the proper way to call the range.
There's more work to be done with the macro yet, like having it pull the data with TestLOG closed, which I believe is done by listing the path to filename.
Then I need to maintain cell formatting on the new table when the data is posted to it.
Hopefully the final task is to get this working so that when a new employee is selected in TestFORMS, the previous employees data is cut and the new posted, as the worksheet is just printed out for each employee and put on hardfile.

View 9 Replies View Related

Use Named Range From Another Workbook In Formula

Aug 19, 2006

I have two spreadsheets. The sourse spreadsheet already has many named ranges which i would like to use for the main spreadsheet to pick up its information. Is there an easy way when using vlookup or the like to name the range in the other spreadsheet?

View 5 Replies View Related

Calling Named Range From External Workbook

Jun 12, 2009

I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.

I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.

View 3 Replies View Related

Workbook With A Globally Defined Named Range

Feb 19, 2010

I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following

View 4 Replies View Related

Named Range: Create Copy From Another Workbook

May 28, 2008

I have a named range within a sheet. It was pulled across when i performed a move/copy - create copy from another workbook. The name contains characters that cannot be displayed or recognised and so they are displayed as square
symbols. This causes me an issue as i cannot delete the name. Even if i paste the list names, i cannot copy/paste the name as the characters are not recognised. I also tried using a macro to delete all names, which did delete all names, with the exception of this one.
The VB code used was :

Sub Del_Names()

Dim myname As Name

For Each myname In ActiveWorkbook.Names


Next myname

End Sub

View 9 Replies View Related

Open Workbook Where Named Cell Contain Name & Path, Copy Range And Paste Values

May 19, 2009

I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the


command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).

I can use the


command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.

I realise this is probably very basic and I've searched the forums but can't find any identical postings.

View 5 Replies View Related

Table Named Production In Access

Jul 31, 2008

if I want to look up a table named production in Access.

Name: bob,jason,robbert,christy
Dept: bins,cars,cars,sewing

now if i wanted to be able to look up jason then have it populate which Dept he worked in, in cell A1 in my excel spread sheet what code could I use. Just want to look up one area in my access data table then show it in A1 one item at a time.

View 9 Replies View Related

Expanding Named Ranges And Access

Jun 6, 2009

Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.

View 9 Replies View Related

Reference To Name Range As Array From Another Sheet / Workbook

Jan 28, 2013

I have the below formula


I would like to copy it across and use different named ranges for the red part, hence have it reference to a cell with the name of the range.

i.e. (which doesn't work)


View 9 Replies View Related

Data Validation List :: Reference A Range Other Workbook

Nov 23, 2007

I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?

The range is =$Q$5:$Q$13

Is it possible to reference another sheet? If yes, how is is done?

View 9 Replies View Related

Connecting Excel To Access Queries - Named Ranges

Jan 11, 2014

So I have an excel workbook that is connected to an Access database I use. When the workbook opens, it refreshes the data from a couple queries in Access. However, every time it refreshes the data, it modifies several named ranges pulling from areas of those tables.

Example: $A$2:$A$50000 becomes $A$2:$A$50002 if there are two new lines entered into the table.

Because of this, some of the lookups and arrays I use are mismatching and giving errors. How to prevent the ranges from updating when the table refreshes?

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

Query_from_MS_Database_Database: Reference By Name A Query From An Access Dbs

May 26, 2006

query_from_MS_Database_Database. how do i reference by name a query from an Access dbs (e.g., qyery_from_MS_Database_Database)...?

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

Copyrights 2005-15, All rights reserved