reference to closed workbook

the INDIRECT formula will not pull values from a closed workbook, wha
is a solution to this?

I am trying to reference a cell in a closed workbook on a sheet tha
has the same name as the active sheet, so I want to be able to us
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) in the referenc
to the other workbook...


Message posted from

6/28/2004 2:25:48 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 14

"buckyduke >wrote...

> INDIRECT formula will not pull values from a closed workbook, what
> is a solution to this?
> I am trying to reference a cell in a closed workbook on a sheet that
> has the same name as the active sheet, so I want to be able to use
> MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) in the reference
> to the other workbook...
> thanks

As posted by Harlan Grove,


If you want to avoid using macros and use formulas instead, your *only*
are to download and install Laurent Longre's MOREFUNC.XLL add-in, available

(which is an installer for the add-in). It's INDIRECT.EXT works with most
versions of Excel (e.g., my laptop running Excel 97 SR-2 under NT 4 SP-6)
not all (e.g., my wife's PC running Excel 2000 SR-3 under Windows Me).

The other option involves running two instances of the Excel application
with the second under the control of the first. UDFs in the first would
hard-coded external link formulas into worksheet cells in the second and
the results to their caller. VERY CRUDE compared to MOREFUNC.XLL.*****Good

Best regards,

nospam710 (114)
6/28/2004 3:46:20 AM
"buckyduke >" <<> wrote...
>the INDIRECT formula will not pull values from a closed workbook, what
>is a solution to this?

Multiposted to .worksheet.functions, and answered there. Your chosen means
of posting to and reading from Excel newsgroups is less than ideal. If
excelforum provides no means to *crosspost* as opposed to multiposting, then
post to *ONE* AND ONLY *ONE* newsgroup. It's possible excelforum doesn't
provide these usage instructions, but multiposting is *ALWAYS* bad!

hrlngrv (1990)
6/28/2004 3:47:59 AM

Similar Artilces:

Personal File not closed properly
Help! Just started using Office 2007, and every time I open Outlook, I get the message 'A personal file was not closed properly. Please wait while Office/Outlook (can't remembers which) checks the file for errors.' As it happens, the checking doesn't take that long, but it is very annoying to get the message *every* time I start the darn thing. And I mean every, not just after it has frozen or crashed or something. Any ideas for getting it fixed would be gratefully received. Thanks Gareth See -- Robert Sparnaaij [MVP-Outlook] Co...

Why excel close all files when I just want to close one files
Dear all, I opened several excel files, and now I want to close one file of the opened file. But when I close one file, all other files are closed and the whole excel is close. Is there any setting that I need to change in excel setting? Thanks There is no setting, but you have to be careful about what X you click on. If you click on the X at the top (the application window), then you're closing the application--not just the single workbook. Yes, excel behaves differently from MSWord. hon123456 wrote: > > Dear all, > > I opened several excel files, and n...

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi = Excel/VBA, soccer and music "ste...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

shared workbook
Hi, I have an Excel workbook that is shared among three users. The file has bloated to a file size of 15MB. When I unshare it the file size decreases to 800KB. If I reshare it the file size remains at 800KB. Why does Excel not clear the bloat automatically? Is it not a good idea to unshare and then reshare the workbook to shed off the excess, i.e. are you getting rid of important backgound data and in doing so causing problems later on? TIA, Jarryd Jarryd I've been pulling my hair out for the past 2 weeks trying to figure ou why my shared excel file grows at an abnormal ...

Reverse a General Ledger transaction for a closed year.
I forgot to reverse an entry in 2003 and I closed 2004. I know that the standard answer is that this type of error cannot be corrected; however it is unacceptable to me that there is not a way in Great Plains to correct this. Can we correct the transactions in the table? Scott, The only way to do this would be to have MBS Professional Services open the 2004 back up for you so that you can post to 2003 and re-close 2004 when done. This will cost you for their consulting time, but I believe this will be less in the long run than trying to fix this yourself in the tables. Your GP Partne...

apply format to all pages in a workbook
Clear DayI use a tracking sheet at work to track everything I do throughout the day, so my workbooks are done monthly. When I get the initial page all formatted, is there any way to apply this formatting to all 30 pages in the workbook w/out going through each one and pasting from the original? I am using excel 2000. Thanks If you select the whole of the first sheet & copy, you can then go to the 2nd sheet and use Edit|Paste Special|Format to replicate the first sheet's formatting on the second sheet. Haven't tried it but you may find it works just as well if you select all she...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software "Blinds Nottingham" <> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Website search closes and reopens some sites in IE8
XP Home 5.1.2600 Srvc Pack 3 IE8.0.6001.18702 "A problem with this webpage caused Internet Explorer to close and reopen the tab." Just entering a character in certain websites' Search Box results in the "This Tab Has Been Recovered" msg after the site is closed and reopened. BUT, if I paste a character string into the Search Box, the site remains open and the search is executed in a proper manner!?! This happens on a few websites including "Oscar Nelsen" <> wrote in message news:736...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

VBA: close Adobe Reader
I have created a PDF file out of Excel and Adobe Reader is automatic opened. I want to close Adobe Reader by VBA code. Under references I have added the Adobe library. I use this code but it doesn't work. Can somebody help me please? Jan Prins Sub closeAdobe() Dim wdobj As Acrobat.AcroApp On Error GoTo foutmelding Set wdobj = GetObject(, "Adobe Reader") With wdobj .Show .Exit End With Exit Sub foutmelding: MsgBox "Cannot close Adobe" End Sub ...

cell linking to another workbook
I have cell referencing right now in my workbook A for ex. In cell D4 have =Sheet2!A25 I have been able to link data between sheets in the same workbook, bu I would like to link cells from 2 different workbooks, is thi possible -- Message posted from unless you mean something more profound than i can understand, this is short and sweet answer: =[VIN.xls]Sheet1!$A$ -- Message posted from It did work, thanks alot. However, there is about 10,000 cells whic have to have this formula in them. When I copy and paste this formul into the...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

Hello, I have a Userform with a close button. I do not know the command to close the userform. Plese help. Bye Siegfried Hi Siegfried use Unload me -- Cheers JulieD check out ....well i'm working on it anyway "Siegfried" <> wrote in message news:d4gi5h$g7f$02$ > Hello, > > I have a Userform with a close button. > > I do not know the command to close the userform. > > Plese help. > Bye > Siegfried > thank yo "JulieD" <

Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <> wrote in message news:43ee4603...

Sharing workbook over the web
How can I share a workbook over the web? I'd like multiple users to be able to open and update the document simultaneously (in the same way it works when we're all sharing the workbook on a shared network). Is Sharepoint the only way to do this? If so, how does that work? What do I need to implement that? Thanks! - Rob ...

Wrong closing quotes today
Multiple positions seem to show incorrect closing quotes on the Moneycentral server this evening. The "charts" on that service appear to be correct, but the quote detail is incorrect. One example: ALL 65.77 but really is 65.30 Fidelity and Reuters, as well as the chart displayed with ALL on moneycentral, all show the 65.30 quote. Checking time and sales data shows no trades during the last hour at 65.77. I think Fidelity would show an after hour trade price if such occurred. Any ideas as to the problem? This has not happened in the past, that I've noted. --ron On Thu, ...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

Sheet Reference
Is there a formula for listing the current Sheet. I know about the filename cell("filename",A1) which returns the entire path, but I just want the Sheet name to appear. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > Is there a formula for listing...

R1C1 reference #2
Excel keeps changing to the R1C1 reference style, but only in one of my workbooks (I believe in just one of the worksheets). I keep changing it back (Tools - Options - General - R1C1 Reference Style), but when I move or copy cells in the workbook, it changes back to R1C1. Anyone have an idea? Look at the TOOLS / OPTIONS / SETTINGS is the 'R1C1 References Style' box checked? If not, check it then save the workbook. Not sure if this is your issue but it's worth a try. Good Luck, -- Gary Brown "Tony S" wrote: > Ex...

Using Several workbooks....
I have a question regarding using several workbooks to handle a job. I am tracking clients (in this case inmates in a jail). I have a set of core data that I call the "Master List". It contains the following: Inmate Name Inmate NUmber Living Unit Admit Date Release Date Classification Needs Assessment Right now I have all departments running off the one workbook. After the first seven "Core Data" columns, I have other stuff... like 12 columns of programming (drugs and alcohol, violence prevention... etc...) and then 6 more miscellaneous columns and finally several colu...

Hyperlinks Within Same Workbook
I'm having a heck of a time creating hyperlinks in a workbook I received from someone. Essentially, I added sheets and drew graphs. One of those sheets I plan to make an index with this structure: ! Leading Indicators MZM 1,1a REIT 1.1b Pipeline 1.1c Household - nondurable Stock Market S&P 500 1.2a REIT 1.2b Pipeline 1.1c Household - nondurable 2 Coincident Indicators T-Bill...

How do I Shorten Object References?
ThisWorkbook.Sheets("Vessel") I'm always referring to this Sheet and Sheet "Operations" I've seen code where people shorten the objects... How exactly do I do that? Dim ws as worksheet set ws = ThisWorkbook.Sheets("Vessel") ws.Range("A1").value = "Shorten" "Benjamin" wrote: > ThisWorkbook.Sheets("Vessel") > I'm always referring to this Sheet and Sheet "Operations" > I've seen code where people shorten the objects... > How exactly do I do that? Benjamin, Here's ...