Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Indirect Reference To Closed Workbook


I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.

The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.

Example: ...


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
INDIRECT And Named Ranges Referencing Closed Workbook
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 Replies!   View Related
Reference Closed Workbook To Activesheet
how to go about or refer me to how exactly you assign a closed workbook to be active. I know this sounds off and its probably the wrong way to word it so here's my problem. I am developing a macro in Visual Basic 2005 using VB. I have a connection string that looks like this:

Private m_sConn4 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:2005.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""

What I need to do is connect to it and update which I have done successfully, then save it as 2006.xls. Is there some sort of m_sConn4.SaveAs or something of that nature.

View Replies!   View Related
Add COUNTIF To Cell & Reference Closed Workbook
i'm trying to do a COUNTIF from an open workbook. The range I want for it is in another workbook, which I do not want to have open every time I run the macro.

This is my current
Sub cellLink()
Sheets("Calls In-Out Trend").Range("ag18").Formula = _
"=COUNTIF('C:***[*.xls]sheet name'I:I, QXO)"
End Sub

editthe range is just so I can see if it's giving the right values, the range won't be like that once I get it working. I'll probably be using logic statements to place them in the appropriate cells. The * is just me taking out the directory names, its on the desktop)

It can copy cell values from another unopened workbook, but when I try to put in COUNTIF it doesn't want to compile/run the script.

That's basically the question. Here's some background and my aim:

I'm scanning row I:I for certain keywords which I then count up and put into corresponding columns of a chart.

View Replies!   View Related
Reference Data From Closed Workbook Macro Code
Following syntax is incorrect, what is wrong? This should happen: Select op a specific worksheet (named DATA) in the workbook Report.xls and I empty the contents

Then I try with a 'With' statement to read the contents of a file named "sourcedata.xls' with helds a worksheet named "source". The values in this sheet should be tranfered to the sheet DATA in the workbook "Report.xls"

Sub FetchDataFromClosedWorkbook()
Dim FileName As String
Dim SheetName As String
Dim cellRange As String
Const ReportPath = "C:ExcelReports"
Windows("Report.xls").Activate
Sheets("DATA").Select
Cells.Select....................

View Replies!   View Related
Vlookup In A Closed Sheet, By Using Indirect
IF(ISNA(VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))

It is the part regarding til indirect that gives me problems

This form I can get it to work

IF(ISNA(VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP($B$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))

But here I need the Omklist.xls to be open. And that I dont want.

The what i want is that the user can use the sheet with out oping the data sheet..

View Replies!   View Related
Copy Range From Closed File & Paste To Closed Workbook
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View Replies!   View Related
Row Reference Using No Indirect Or Offset
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.

New York (column header)
Wages
Lights
Net Profit

Boston
Lights
Wages
Sales
Net Profit

How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.

View Replies!   View Related
Worksheet Reference In Indirect Function
I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.

Using

=INDIRECT(I$1&"!YADDA")

I get a REF error because of the inclusion of the hyphen in the worksheet name.

How can I fix this without eliminating the hyphen?

View Replies!   View Related
VLOOKUP - Indirect RangeName Reference
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).

Cell A1 contains a fuel cost.

Cell A2 contains a formula that returns a RangeName.

In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.

If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.

Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.

View Replies!   View Related
Indirect Function To Reference A Named Range
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:

=sum(indirect(A1))

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 Replies!   View Related
Sheet Cell Reference And INDIRECT Formula
I have this formula in excel 2007:

=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").

Blad is Dutch for sheet, by the way.

In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.

View Replies!   View Related
Combining Text And Cell Reference With Indirect
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")

But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.

View Replies!   View Related
Increment Indirect Formula Column Reference
COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.

How do I change my formula so that the numbers increase automatically?

View Replies!   View Related
Variable Cell Reference In INDIRECT Formula
I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,($A9) and insert this in a cell BUT the $A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?

View Replies!   View Related
Open A Closed Worksheet W/ Cell Reference
I am trying to open a stored file on a network drive (with the file location stored in cell E4). When I try to run the macro, I get an error message on Line 3. (Sheets(E4.Value).Activate)

Does anyone know what I can do to fix this, or have a better method to open the closed files?

I've read that it's possible to reference information from a closed sheet, but I don't think that I'm experienced enough in VBA to take on that effort yet.

View Replies!   View Related
Transfer Data From Open Workbook To Closed Workbook
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"

to

closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6

View Replies!   View Related
Copy From Closed Workbook Then Create New Workbook
I've got a problem with this code, have been wracking my brains about. Here is the process I am trying to do:

1) Copy a range (a2:av1000) but (ideally) find the last populated row from a closed workbook (with a different password)
2) Create a new workbook and paste this data into it at A2
3) Close all the workbooks but only save the new one.

View Replies!   View Related
Copy From Closed Workbook To Open Workbook
I'm trying to write a macro that accomplishes the following:

" Book 1" is already open. The user runs a macro that lists all .xls files in directory "d:measurements" The user selects the desired file from the list or box the macro copies from this "book 2" " sheet 3", " range A6:I107 and pastes (values only) into "book 1", "sheet 5", "range A6" End of macro.

I want to accomplish this without opening the selected file (book 2)

View Replies!   View Related
Pull Value From Closed Workbook Where Workbook Name Is In A Cell
I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:

to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.

Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"

The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.

View Replies!   View Related
INDIRECT Update With External Workbook
Is it possible for the INDIRECT function to be tweaked so it updates even when the external workbook is closed?

View Replies!   View Related
Indirect Giving Me A REF Error When Closing Workbook
My indirect formula doesn't work when I have the entire path defined. I have the entire filepath specified (which will never change), and then I have cell A6 with the tab name of the worksheet on the workbook
.
Workbook 1 named 'Testme' contains the table with range a2:b5
Worbook 2 contains the formula

I put this formula into workbook2 (the tab in the example is named hitab & I put that name in cell B6)


Here is what I have:Does not work even when other workbook is open
=VLOOKUP(B6,INDIRECT("'H:CUSTOMERSCurrentClientAMRO and General SuppliesBearings, Motors, PTsSavings Tracking[Testme.xls]"&A6&"'!a2:b5"),2,FALSE)

This DOES work when workbook is open, but not when it is closed (which would seem logical if when the workbook is closed, the formula needs the exact path)
=VLOOKUP(B5,INDIRECT("[Testme.xls]"&A6&"!$a$2:$b$5"),2,FALSE)

I can hard code everything except the tab name. Why doesn't Indirect recognize an entire file path and also why does it give a REF error?

- I did a search and found that some people said to use the add-in Indirect.exe. I followed the link and the page was no longer found.

View Replies!   View Related
Sum In A Closed Workbook With VBA
I was wondering if there's any way to sum inside a closed workbook by usin' VBA? I think I figured out how to do it just with formulas, but I was hoping to be able to make like a macro or something to do it, 'cause its kind of time consuming having to go back and forth to all these workbooks.



View Replies!   View Related
Max Value From Closed Workbook
Is there a formula or vba code that would allow me to get the max value from a column in a closed file/workbook?

View Replies!   View Related
GetValue From Closed Workbook
when using the Active X Data Objects 2.8 Reference in excel 2007, i run into the problem with the GetValue function. After running the script the output sheet shows #REF! in all specified cells?!

Sub test12()
p = Location
f = file
s = "Sheet1"
For r = 1 To 150
For c = 1 To 12
a = Cells(r, c).Address
Sheets("Cars").Cells(r, c).Value = GetValue(p, f, s, a)
Next c
Next r
End Sub..........


View Replies!   View Related
COUNTIF On A Closed Workbook
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.

Do all workbooks have to be open when using COUNTIF?

If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.


View Replies!   View Related
Lookup Value From Closed Workbook.
I have a workbook called Book1 that was created from another macro. I am attempting to do a lookup using columns B2 and C2 and look for the same values in columns G6 and J6 from a workbook called Marine Moves..If a match is found copy the value from Column H insert that value into Book1 Column A, if possible I would like the Marine Moves workbook to remain closed during this process.

View Replies!   View Related
Referring To Closed Workbook
I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:

='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4

Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.

View Replies!   View Related
Prevent Workbook Being Closed
I display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.

In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.

View Replies!   View Related
Getting Data From A Closed Workbook
I am trying to use this code to pull data from a closed workbook. The code will be in workbook Book1 and I will be pulling information from closed workbook Book3. I need to pull data from cells A1:A4 from Book3 and place it into Book1 on Sheet2 in cells A1:A4. I receive an "Subscript out of range error" on this line of code

View Replies!   View Related
Pull From Closed Workbook
I have a folder that contains hundreds of excel workbooks. Each workbook has 20 or more sheets. All workbooks have the same look and design. All workbooks have macros that they use to function and the macro I want cannot interfere with their macros.

What I want to do is copy certain cell values (some numeric and some text) from several sheets from these workbooks and paste them into another workbook (a master workbook) that is essentially collecting these values into a table without having to open all these workbooks. For example, let’s say all of the workbooks are named with a numeric value starting with “one.xls” and that the sheets in these workbooks are named “a”, “b”, and so on. So, in the master workbook in cell “B2”, I want the name of the first workbook searched (“one.xls”); then in cell “C2”, I want the value from cell “J45” on sheet “b” of “one.xls”; in cell “D2”, the value from cell “B32” on sheet “b” of “one.xls”; in cell “E2”, the value from cell “K13” on sheet “e” of “one.xls”; in cell “F2”, the value from cell “R43” on sheet “k” of “one.xls”. The in cell “B3” of the master workbook, I want the same information as before except from “two.xls” instead of “one.xls”, and so on down to “three hundred.xls”.

View Replies!   View Related
Retrieving From A Closed Workbook
I have tried to find how to retrieve a worksheet from a closed workbook and I am almost sure that there was an answer how to do this in FAQ but I cannot seem to find it, does anyone know where this thread is?

View Replies!   View Related
Closed Workbook Still Showing In VBE
I and a colleague are working on some code to automate a web page. We've been able to get it to fill in the needed forms, run a report which comes out in Excel format and save the output to a specified shared drive location.

However, when we attempt to close the resulting window we are unable to...

The webpage creates a new Internet explorer window which we're able to capture and do a wb.saveas, but when we do wb.close, it does not generate an error, but the new window does not close.

In the VBE, the workbook is visible as an object, but in Excel if you go to Windows on the main menu and attempt to select it, it's not listed and the Unhide option is greyed out.

So the object is a member of the workbooks collection for the VBE, but not Excel...

View Replies!   View Related
Copy From Closed Workbook
Copy From Closed Workbook

I have been trying this method for a while, I just don't know what to put where, & what names to change.

Trying to pull ranges from database.xls to cost.xls

View Replies!   View Related
Get Values From Closed Workbook
I have a master file with two sheets. I have another input file with only one sheet. Without opening the input file, is it possible to copy the range and paste special values only into a duplicate sheet in the master?

View Replies!   View Related
Values From A Closed Workbook
I wrote here a few days ago about an issue retrieving a closed worksheets values and I got help from RoyUK(see code below and link), but for some reason the values are never entered into the open worksheet.

Retrieving From A Closed Workbook
Sub FillingSheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
Set wb = Workbooks.Open(filetoopen, True, True)
With ThisWorkbook.Worksheets(1)
. Cells.Value = wb.Worksheets(1).Cells.Value
End With
wb.Close False
Set wb = Nothing
End Sub

View Replies!   View Related
Schedule Macros To Run While Workbook Is Closed
I'm trying to look at options for scheduling macros to run at a particular time. Ideally I would like to run one at 3:00 am during every week night. From what I've looked into, I can run the OnTime procedure and it could schedule this to run each night. Does anyone know if this is correct, and if there are any other options avaliable to me?

Also, does anyone know if this can run automatically if Excel is closed, or does Excel have to be open? Does the workbook have to be open?

View Replies!   View Related
Lookup In Closed Workbook Without Opening
I want to lookup data in a closed workbook, that is located on my desktop, without having to open that workbook. I have been trying code as follows, but having problems with the syntax.

View Replies!   View Related
Copy Worksheet From Closed Workbook
I am trying to use a routine that I copied from another thread on this forum, but I get an error message: Runtime error "1004" Copy method of worksheet failed.


View Replies!   View Related
Copy Cells From A Closed Workbook
I have a folder at this location c:documents and settingscharliedesktoplabor

it contains numerous files that have this format 01_Projections with 01 being the store number and it goes from 01 to 20. the word projections is the same on all 20.

I want to access cell A12 on a sheet titled "summary" in all files and have bring over to another workbook without having to open each one. How can I accomplish this?
What if I want to bring back numerous cells from this same sheet and place them in cells in the open workbook?

View Replies!   View Related
Copy Data From Closed Workbook
I have 7 workbooks I would like to copy data from. The data in question is a sheet called "Accident Book" in each of the 7 workbooks.

I know how to copy the data, but how do I reference these 7 closed workbooks ? They are all in the same directory, but these are other files in there that I dont need, so I dont just want to point to the directory.

I have this code from Nimrod but can't get it working.


Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range("A65536").End(xlUp)
.FormulaArray = "='" & "\Bls36dc10WorkH&S" & "[" & Central 2004.xls & "]" _
& "Accident Book" & "'!" & range("A6")
.Value = .Value
End With
End Sub


I actually want to copy data from A6:N6 down to the last row entry, then paste this into the next available row in the activesheet. I need to do this from 7 different workbooks.


View Replies!   View Related
Display Data In Closed Workbook
I have 1 excel workbook open. It is in a common directory with several other excel workbooks which are closed. In the open workbook, I have created a vba macro that completes various tasks - only within the open workbook. I want to modify the macro in the open workbook such that it will specify data to be displayed in the closed workbooks. Then, when the closed workbooks are opened next, the data will reflect what the macro had specified.

View Replies!   View Related
Copy Data To A Closed Workbook
how do i copy the data from cell C1 in the active workbook "name1.xls"
to cell D10 in the closed workbook "name2.xls" in "sheet1" without opening the
closed workbook?

View Replies!   View Related
Update Data On A Closed Workbook
I created a form and the values that it takes are Date, Issue, Name, Team Member and Cause.

I use a Production Support Report (workbook that I don't want open for people to see) and it will have tabs for each month ie; January

how can I without opening Production Support Report grab the values from the form then update the Production support report by finding the months sheet and sticking the values at the last row.

View Replies!   View Related
VBA To Copy Data From A Closed Workbook
I want to add a button to my excel workbook that when pressed allows the user to browse to an excel file, once selected, the macro then looks for a specific sheet with a specific name and then copies some data (not entire sheet) into a sheet in the doc where the button was pressed.

View Replies!   View Related
Importing A Sheet From A Closed Workbook
I found this code on the net, and it works, it DOES import the sheet specified from a closed status, but it adds 0 where there were blank cells.

The worksheets are static, the names will not change, but the information on them varies in # rows but the # of columns depends on the page..

The use of this import is to upgrade the program, by importing the sheets from the old version.

The first part browses for the file, which is good, because it could be stored anywhere and named anything.. I have it set up to "Click to Import"....

Private Sub CommandButton1_Click()

GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Personnel", "A:H"

End Sub

This next part does the importing, but it fills all the columns and rows that were blank with 0's and fills all the way down to 65536 thru my column H that I specified that had data above.

Sub GetValuesFromAClosedWorkbook(fPath As String, _
FName As String, sName, cellRange As String)

how this code can be modifed to do the following..

Detect and copy ONLY the used range of the closed workbook.

View Replies!   View Related
Lookup In Closed Workbook Error
Column D = Dates to be used in each of the matches.

Cell C2 = data-validation cell, for choosing the specific Named Range that contains the data to be returned from the closed wb.

VBA code automatically opens the closed data file and then closes it after returning the data to the main file.

find the error in the cell formula (or named range formula) that’s preventing this from working correctly? (see atttached files)

View Replies!   View Related
Moving Data To A Closed Workbook
I have tried to move data from my active WB to a closed one with the following

View Replies!   View Related
Update Only One Worksheet From A Closed Workbook
I have code that links my existing workbook to a closed workbook. It works as planned but it goes to far. In my first workbook, the one containing the code, I may have up to 30 worksheets. If I am on Sheet5 I may need to run the vba code to update cells X:X only on Sheet5 but instead it updates cells X:X on all Sheets in the workbook. How can I keep this from happening?

I have asked this on another forum but it seems to be a difficult thing, perhaps impossible thing to keep from happening.

View Replies!   View Related
Moving Information From A Closed Workbook For Use In Another
I am working with two files, both files have multiple worksheets. File A has a worksheet that is filled out almost entirely by equations. I need to move the information from that worksheet into file B. In file B i will process the information further. Also, I will print it and it has to look exactly the way it does in file A. I want all this to happen automatically so I am looking for some sort of function or script that will perform this task.

View Replies!   View Related
Referring To A Worksheet In A Closed Workbook.
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month. Kind of lost on how to possibly make this work. I basically need something like: =location/[workbook.xls]worksheet!cell - where worksheet = B12 and B12 =month(today()) and is formatted to MMMM

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved