Cell Reference Formula Outside Workbook

I am trying to reference a cell outside of my current spread sheet.  I
am using a cell in my current sheet as an input for the extension of
the file I would like to look in.  So I have this defined as LINK.  And

the value of link is something like C:\\Myfolder\ready.xls

I am trying to set up a formula in my sheet that will use this
reference to the cell on the first page, which I defined as LINK so
when I change the value of the cell all my values will update to the
new folder and document, but retain their previous page, column and row

references.


For example.  My current formula reads
[C::\\Myfolder\ready.xls]Sheet1!B6


I'm thinking I should be able to use LINK&Sheet1!B6


This should make it possible to continue to update the link reference
on my page. 


Any help would be greatly appreciated. 


Mike

0
mikeismag (2)
9/6/2006 4:44:52 PM
excel 39879 articles. 2 followers. Follow

1 Replies
509 Views

Similar Articles

[PageSpeed] 43

You'd want to use the =indirect() function, but that only works when the
"sending" workbook is open.

But Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

mikeismag@gmail.com wrote:
> 
> I am trying to reference a cell outside of my current spread sheet.  I
> am using a cell in my current sheet as an input for the extension of
> the file I would like to look in.  So I have this defined as LINK.  And
> 
> the value of link is something like C:\\Myfolder\ready.xls
> 
> I am trying to set up a formula in my sheet that will use this
> reference to the cell on the first page, which I defined as LINK so
> when I change the value of the cell all my values will update to the
> new folder and document, but retain their previous page, column and row
> 
> references.
> 
> For example.  My current formula reads
> [C::\\Myfolder\ready.xls]Sheet1!B6
> 
> I'm thinking I should be able to use LINK&Sheet1!B6
> 
> This should make it possible to continue to update the link reference
> on my page.
> 
> Any help would be greatly appreciated.
> 
> Mike

-- 

Dave Peterson
0
petersod (12005)
9/6/2006 5:37:39 PM
Reply:

Similar Artilces:

formulas #27
are there any sites that i can go to where the symbols and terms used in making a formula are described or listed The help is an excellent reference. If you are looking for a tutorial, then http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials at David McRitchie's site Chip Pearson has pages on specific types of formula http://www.cpearson.com/excel.htm Look at his topics index -- Regards, Tom Ogilvy "detlghtpd" <detlghtpd@discussions.microsoft.com> wrote in message news:D096D4A3-C153-4B43-A6CE-5C18E96F6450@microsoft.com... > are there any sites that i can...

how to count cells with equal sign
Hi guys please help me to write this formula: if the integers of the A1 and A2 are of the same sign(+ or -) =1 if not =0 I need to count cells with equal sign and i don't know how to expres in formula "equal sign" Appreciate your help -- Message posted from http://www.ExcelForum.com Hi! Perhaps use the fact that, if you multiply together two numbers wit the same sign the answer will be positive. And if you multiply two wit opposite signs, the answer will be negative. So: =if(A1*A2>0,1,0). This doesn't cover the cases of A1 and/or A2 being zero. Does tha matter? If i...

putting date and time together in 1 cell
Hi I need to convert New Zealand time to UK time, the time is displayed in two parts A B date time dd/mm/yy hh:mm:ss How do i get cell A and cell B into one cell with the format dd/mm/yy hh:mm:ss and then I can just minus 13 hours. any ideas please. =SUM(A2,B2,-13/24) and format the cell as dd/mm/yy hh:mm:ss. HTH Jason Atlanta, GA >-----Original Message----- >Hi I need to convert New Zealand time to UK time, the time >is displayed in two parts >A B >date time >dd/mm/yy hh:mm:ss > >How do i get cell A and c...

Formatting 0 values to show blank cells
I am using the =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) to return a zero value. For printing purposes I need the 0 to not show in the cell (blank cell). I can do this by using the accounting format, but a dash (-) still shows in the cell. The sheet is protected to protect the formula. How can I protect AND not show anything in the cell WHILE keeping the value at "0"? You can use set the custom format to ;;""; See Worksheet and Excel table basics > Formatting numbers in Excel help file for details. "RLD" wrote: > I am using the =...

Copy formulaS between Workbooks
This might be some sort of follow up question from Ken Runge post "Cop formula between Workbooks" from Oct-23-2003. I would like to copy som formulas from one workbook to another without getting a reference t the original workbook ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com Syslock, This happens, I think, where there are cell references to other sheets. One way is to copy the text of the formula (drag across the text in the formula bar), instead of the cell...

unneeded duplicate namespace reference
Hi community, I would like to identify a part of one xml document via a XPath and then to copy the element (with all its child nodes) into another xml document to a specified location (also given via XPath). All that goes fine. Only one point isn't like I wanna have it: the element, that has been inserted into the target document, has an additional (redundant) namespace attribute. Here my small demo application: using System; using System.Xml; using System.Xml.XPath; namespace NSPConfusion { class Program { static void Main(string[] args) { XPath...

How to change a Macro based on content of a cell
Hi, I need to import a text file into a spreadsheet. I can do this manually by doing File > Open > then select a text file. Then Excel goes through the Text Import Wizard I can automate this by recording a Macro in Excel. The macro looks like this (Excell wrote that for me, I did not): Sub Macro1() Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(12 _ , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True End Sub How can I change ...

Formula Error
I have a list of codes in F$19:F$1500. I was attempting to use the following code to assign a number to each unique value in the list and repeat the previously assigned number if it is duplicated. This works for the first 15-20 numbers, but returns an error (#n/a) afterwards as if the code stopped working. I have tried EVERYTHING... Same result each time. =IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G $18:G18)+1) Any clues? My guess... =countif() treats everything as text. So the text string ="1" (or '1) will be counted as a match as well as the num...

insert new row doesn't add formula
I have a table A2->M100 In the M Column I have a sum to calculate the total of that row =SUM(A34:N34) for example Now if I want to add a row into that table, I click on the number o the left, that highlights the row then I go insert>>row and that keep the formatting of the cells. BUT it doesn't add the =SUM(A54:N54) in the M column, and so I have ha to edit each cell by hand to get the correct formula. Is there an easier way to add a row and keep fomulas within that row but obviously automatically change the numbers to suit the row -- Message posted from http://www.ExcelForu...

how to sort cells with sheet reference
i an unable to sort the data in cells containing a reference to anothe sheet. it seems as though excel is treating this reference as absolut and not relative. is there any way to do this, such that thes equations adjust to the sorting? any thoughts would be appreciated. thanks -- vba_neophyt ----------------------------------------------------------------------- vba_neophyte's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2418 View this thread: http://www.excelforum.com/showthread.php?threadid=37804 one way would be to copy and paste specail values only - a...

Excell Cells: Auto Formatting
I have protected a workesheet and unfortunately when the people submit information on the cells and it is too long than the actual height of the row and it wont extend. What can I do? When protecting the sheet allow users to format cells and rows. Gord Dibben MS Excel MVP On Tue, 9 Feb 2010 13:14:01 -0800, Sophie <Sophie@discussions.microsoft.com> wrote: >I have protected a workesheet and unfortunately when the people submit >information on the cells and it is too long than the actual height of the row > and it wont extend. What can I do? How do I jus...

range to cell
Hello, I am writing a program. I have a excel range object. And I want to point another cell at this range. how can I do that? On the range there is column = 4 row = 3. but I cannot just say =4,3 or something like that? don't you need =D3? why is the column D and not 4? strange =sheets("Sheet1").range("D4") or =sheets("Sheet1").cells(3, 4) -- HTH... Jim Thomlinson "greg" wrote: > Hello, > I am writing a program. > I have a excel range object. > And I want to point another cell at this range. > how can I do that? > On the ran...

if the cell value exceeds the limit to have a dialog box appear
Is there a way that, if the cell value exceeds a certain limit, a dialog box would appear? Thanks for any assistance. Dave Check Data Validation: Select the cells Data>Data Validation Allow: (check out the number options: whole number, decimal, etc) Does that help? *********** Regards, Ron "Dschro" wrote: > Is there a way that, if the cell value exceeds a certain limit, a dialog box > would appear? > > Thanks for any assistance. > > Dave > Ron, Here's the scenario -- on an Expense Report: If an employee was to fill in any amount in the &...

Sum the products of a formulas
I have a gradebook that returns values using the "LOOKUP" function for a 60 question multiple choice test. I need to sum the product of the returned values. I can use the paste as text, then convert the text to a numeric value, but I have to perform this function for each cell, and I have 14,000 cells. Is there a way to setup this formula to return a value that i can then sum? =LOOKUP(I60,{"a","b","c","d","e"}, {"0","1","0","0","0"} The formula returns a 0 or 1 for each test q...

Excel cell display
I want a text box to display when I click on a particular cell - how can I make that happen? Would Insert> Comment work for you? "slantt" wrote: > I want a text box to display when I click on a particular cell - how can I > make that happen? Yes, thanks - I found that after I posted of course (newbie to Excel). I was looking to see if there was a macro I could gen to do the same thing... "ufo_pilot" wrote: > Would > Insert> Comment > work for you? > > > "slantt" wrote: > > > I want a text box...

Question On Excel Formulas
Hello, Is there a way (addin utilities or otherwise) to cause the formula in a cell to display the values for the component references making up the formula? -i.e =A1 *(B2+C4)/D2 would display based on the cell reference values as: =5*(4+6)/8 Thanks for your help, David S> Not in that way, AFAIK But look at Tools>Formula Auditing>Evaluate Formula BTW your requirement is one reason why I always recommend to do small steps per cell, so you can easily check all intermediate result. In your case, Cell1: =B2+C4 Cell2: =Cell1/D2 Cell3: =A1*Cell2 -- Kind regards, Niek Otten...

Quickly locate a specific page/tab in workbook
I have a workbook with over 1000 pages/tabs. Is there a command to quickly access a specific page/tab without scrolling through the entire series to get to the one I want? One way: In pre xl 2007 ( might be the same in xl2007 I just don't know) Right click on the arrows in the lower left corner of your worksheet, (to the left of the tabs) and you will get a list you can scroll thru to find the worksheet you need to go to. Mike Rogers "touchstone" wrote: > I have a workbook with over 1000 pages/tabs. Is there a command to quickly > access a specifi...

unlock frozen cells
I opened excell program to add data but it is locked. I can't add nor delete data. None of the functions work. It opens, but is locked. Help It might be that you have a 'Freeze Panes' issue. To unfreeze the windows... In 2003 - WINDOWS > UNFREEZE PANES in 2007 + VIEW > WINDOWS > UNFREEZE PANES -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Windows 7" wrote: > I opened excell program to add data but it is locked. I can't add nor delete > data. None of t...

formatting cells #3
Is there a global format that will change the same cell on multiple sheets within the same worksheet, simultaneously? If you select a group of sheets - e.g, select the leftmost sheet tab, hold down shift and click the rightmost tab - any changes you make in one sheet gets made in all of them, including formatting. Right-click a selected tab and choose "Ungroup sheets" before performing operations that you want to pertain to one sheet only. In article <2d64701c469c1$2e8ddc30$a301280a@phx.gbl>, "C.C." <anonymous@discussions.microsoft.com> wrote: > Is...

Why Am I getting a blank in the result cell?
Can someone assist me with what I'm doing wrong with the formula below? It keeps returning a blank in the resulting cell. I am trying to calculate the weighted avg. of production scores for an employee based on hours worked per month. columns B,D,F,H,J, and L are the hours while C,E,G,I,K, and M are the prod scores. Manual calculation should result in a 90.9% weighted averge. B C D E F G H I J K L M wgtd. Avg. 129 117 - - 29 75 19 32 - - 50 55 90.9% =IF(ISERROR(SUMPRODUCT((B13...

relative references in Macro
I have a question about how to make relative references within VLOOKU macro work. For Example: Dim sht As Worksheet Dim rng As Range Set sht = Worksheets("sheet1") sht.Range("H12") = "=VLOOKUP(B$4,rev,3,FALSE)" sht.Range("G12") = "=VLOOKUP(B$4,rev,3,FALSE)" I want the G12 cell to reference C$4 without changing it in the macro Just as it would if I have the formula in H12 and then drag it over t G12 instead of using the macro. Thanks a lot -- Message posted from http://www.ExcelForum.com i have not tried this for your specific example ...

CELL REFERENCE MATH
I am using Exel to analyze mechanical test date. The data files can be 600 to 6000 points. I need to capture an 50 point average at the 25% and 75% points in the data. Is there a way to auto adjust where the averages are taken based upon the number of data points. I can get the number of data points using the COUNTIF function. Is there a way to add this result as the numeric part of a cell reference and do the appropriate math to select the correct ranges. I hope this explanation makes sense. Don't multipost. Either post in *ONE* newsgroup only or *CROSSPOST* to several newsgroup...

Can't receive email from outside network
I am unable to get email messages to my inbox from outside of our network. I have been told this is an easy fix in the settings, but i don't know how. HELP! Outlook needs to be configured to use a SMTP server. Look under Tools. >-----Original Message----- >I am unable to get email messages to my inbox from outside >of our network. I have been told this is an easy fix in >the settings, but i don't know how. HELP! >. > ...

Can I link multiple sheets within a workbook together .....
Currently I have one sheet per month and would like to add another sheet as a "year end" tally sheet. Is there a way to link multiple sheets within a workbook and have them automatically sum together??? hi, yes and there are a number of techniques to use to do it. I am not sure about your data but i would just use a simple formula like =(sheet1!sometotalcell). this would bring the data over to the summery sheet. set in up in monthly columns or by catagory with months down the side. then summ the totals for grand year end total. >-----Original Message----- >Currently I ...

System checklist available outside of GP
Is the system checklist that is available under the Tools menu in GP 9 also available as a separate document someplace? The Foundation I training manual includes just the first portion. I would like to be able to provide the entire checklist to my students for a project and not require them to go into GP all the time to access it. Thanks. Jerry You could possibly write a report. The Setup Checklist Master is SY40600, you would need to cross reference to get the Display names. The Checklist Master includes the Technical Name and the Pysical Name. "Jerry Flatto" wrote: >...