handling links to external spreadsheets

I have a large number of links to cells in an external spreadsheet
that look like the following, where different cells on the source
spreadsheet are referenced:

for example, contents of cell Z5 on the destination spreadsheet:
='http://dummy_address.com/teams/projects/[data_analysis.xls]sheet1'!
C3 (source location)

I would like to have the ability to easily change the path, filename
and sheetname for this large number of references while leaving the
target cell (C3 in the case above) the same. Is there some method
where I could put the path, filename and sheetname in cells on the
destination spreadsheet and then in each of the destination cells put
a formula that would assemble the complete string to reference teh
source cell?

Thanks
0
10/9/2008 4:10:47 AM
excel 39879 articles. 2 followers. Follow

2 Replies
785 Views

Similar Articles

[PageSpeed] 57

John <jck.off...@gmail.com> wrote...
....
>for example, contents of cell Z5 on the destination spreadsheet:
>='http://dummy_address.com/teams/projects/
>[data_analysis.xls]sheet1'!C3
>(source location)
>
>I would like to have the ability to easily change the path,
>filename and sheetname for this large number of references while
>leaving the target cell (C3 in the case above) the same. Is there
>some method where I could put the path, filename and sheetname in
>cells on the destination spreadsheet and then in each of the
>destination cells put a formula that would assemble the complete
>string to reference teh source cell?

If you need the full path, these aren't open files. In that case,
there's no good option for handling this purely by formula. If the
path, filename and sheetname wouldn't change often, it may be
expedient to use something like option 1 in the following article from
the archives.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075
0
hrlngrv1 (375)
10/9/2008 5:17:20 AM
On Oct 8, 11:17=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> If you need the full path, these aren't open files. In that case,
> there's no good option for handling this purely by formula. If the
> path, filename and sheetname wouldn't change often, it may be
> expedient to use something like option 1 in the following article from
> the archives.
>
> http://groups.google.com/group/microsoft.public.excel.worksheet.funct...


Thank you for the pointer Harlan. After looking at the options I think
I'll just stay with what I've got.

PS - Thanks for the reminder of Frank's contributions to these groups.
0
10/9/2008 10:43:57 AM
Reply:

Similar Artilces:

cannot create a recordset object for a link table
Hi, I have a mysql dadtbase and access has a table called web_stat link to it. I have the following code Set dbs = CurrentDb Set fromset = dbs.OpenRecordset("web_stat", dbOpenTable) the 2nd line gives me a invalid operation error. It works fin if I replace web_stat with local_stat, ie a table I created on acces. How can I use DOA to access a link table?? Ted Check the DAO Help file, but I'm pretty sure dbOpenTable is an invalid instruction with a linked table. Perhaps you want dbOpenDynaset or dbOpenSnapshot? I think Dynaset is the default with a linked table (so ...

Linking tab names
Is there a way to name a tab by linking it to a cell on the worksheet? I have numerous worksheets whose tab name corresponds to the title i cell A2 - I would love it if I could do a formula which would make th tab names automatically be whatever is in A2. Any ideas -- Message posted from http://www.ExcelForum.com right click sheet tab>view code>insert this. Now, when a2 changes so will the tab name. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address <> "$A$2" Then Exit Sub ActiveSheet.Name = Target End Sub -- Don Guillett SalesAid Software donal...

Show cells have been read
I have several (10 different companies) workbooks reading (linked through "SUM") a sales forcast from another workbook called SALES. I have highlighted the linked cells in the 10 individual workbooks to show which cells are linked and update automatically. BUT... now I need to see which cells in the SALES workbook have not been read into the individual ones so I can check which cells have not been read yet. How would I go about differenciating between cells that have or have not been read? Is there a conditional format or a formula that would work? Thanks in advance for any h...

Report only the linked one
Hello, I have the member's form where is the tab of Note for this member can be shown up. In this note subform we can make a note with fields like Date, time, subeject and memo. How I want to have a command key, to print the report this the hisorical notes on the particular member which is being shown in this form/subform. Can anybody help me how can I make it? Thanks very much. -- Frank Situmorang If I understand your question, you can either make a query that includes both tables, or you can make a report/subreport just as you made a form/subform, depending ...

Will my External Hard Drive work on Windows 7?
Hi all, Just picked up a Western Digital EHD for my current XP system. However, we may be upgrading soon to a 64-bit WIndows 7 system. The box (it was made before W7 was released) does not mention compatibility with Windows 7. Will is work anyway? davey1108 wrote: > Hi all, > > Just picked up a Western Digital EHD for my current XP system. > > However, we may be upgrading soon to a 64-bit WIndows 7 system. > > The box (it was made before W7 was released) does not mention compatibility > with Windows 7. Will is work anyway? As long as the ...

link a combo box to a picture
Hello, I have a form that I enter information into 3 txt boxes and it gives me an answer in another text box then I click view and it brings up a rpt that gives me the list of 10 barcodes I need and directions on how to enter some info from the form and it shows me a picture of scanable barcodes. What I want to do is on the form when I select an entry from on of the combo boxes (cboGuage) and when I click to get to the report it will show me the barcode related to the number selected from the cboGuage list. I have 10 numbers in the cboGuage list and 10 barcodes that coraspond to these n...

Can I avoid annoying Update Links message
I find more and more that I get the "update links?" question when I open a workbook. I make frequent use of sheet names in formulas. Does the message appear if I simply use a sheet name in a formula in the workbook? I wish I could at least figure out what "link" Excel thinks it sees, and tell it to forget it. Can I? This is minor, but annoying to me. For someone not familiar with Excel's eccentric ways, the message can cause panic. Hi, Joe. Try this: http://www.officearticles.com/excel/removing_links_in_microsoft_excel.htm ************ Anne Troy www.Offic...

Counting rows in filtered spreadsheet?
I have a spreadsheet with filters to allow me to view different queries on the data. Is there a quick way to find out the number of rows once filters have been applied. Numpty question but frustrating to find answer. Any help appreciated. Sisco --- Message posted from http://www.ExcelForum.com/ One way =SUBTOTAL(3,A2:A1000) will count visible rows when you apply a filter -- Regards, Peo Sjoblom "sisco >" <<sisco.104njd@excelforum-nospam.com> wrote in message news:sisco.104njd@excelforum-nospam.com... > I have a spreadsheet with filters to allow me to view...

To paste link or to =cell ref??
Please could someone explain to me the difference between using the paste link function & the “= cell ref” function to link cells between workbooks. (I find link paste useful as I can link large numbers of cells at one time where as I have to use the “=” method for each cell. Maybe there is a way to do large numbers of cells with the “=” method?) Thanks There is not relly a quick way in using the "= cell ref". But if you do use the "=Cell Ref" method, whn you copy this to another cell, the cell ref changes in repect to its placing. This may help you, by copying ...

Need help with OutputTo an Excel Spreadsheet
I have a table (APPROVED FOR CAL OUTGOINGt1) that needs to be exported to pre-existing spreadsheet "IMPORT DATA" tab. The spreadsheet is located on the desktop. How do I do this? Look here: http://msdn.microsoft.com/en-us/library/bb214134.aspx Regards Kevin "DevilDog1978" <DevilDog1978@discussions.microsoft.com> wrote in message news:8C0B05FF-AFC9-4B30-9E84-4519AF501C20@microsoft.com... >I have a table (APPROVED FOR CAL OUTGOINGt1) that needs to be exported to > pre-existing spreadsheet "IMPORT DATA" tab. The spreadsheet is locate...

Embedded File Opening upon opneing spreadsheet
I have embedded files within my spreadsheet, and the if I change worksheets and then go back to the worksheet with the embedded files then all the embedded files open automatically. Is there an option somewhere that is causing this to happen? ...

Error Handling for "my" menu bar
I am creating a menu bar. This menu bar as you can imagine call out to run a macro. If that macro does not exist you get an error message. How do I capture that error message and replace it with my own "error" message? Thank you ...

Problems with Spreadsheet Audit Tool
I have a spreadsheet which periodically will not allow me to audit its dependents. It will show its precedents however. I usually have to close it down and start it up again to get it to work. Why is this happening? What can I do to get it to work consistently aside from building the spreadsheet from scratch? Thanks ...

How do I set up a multi-page org chart from an excel spreadsheet?
I am trying to figure out how to set up an Excel spreadsheet using multiple worksheets for each individual page of an organization chart. When I use the Org Chart Wizard it only does one page of the spreadsheet. Assuming there is no charge for your assistance... any information or samples you could provide would be GREATLY appreciated. Wy don't you have a hidden Excel page that contains ALL the other pages, and then run the Org Chart Wizard on that? "Martie" <Martie@discussions.microsoft.com> wrote in message news:BE7BE81A-ED09-4E1C-952B-061FFA05AF70@microsoft.co...

NDRs forwarding to external contact
Hello, I have setup a contact with an external address, I then forward a user's email to this contact (as well as to their local mailbox). The forward works fine from within Exchange from internal users, but if an external user sends to this user, it delivers only to their internal mailbox and bounces for the external (#5.5.0 smtp;551 This is not a relay host). I appreciate Exchange shouldn't act as an open relay, but this is only specific, preconfigured email addresses. Do I have to enable this somewhere else? In ESM Navigate to Global Settings container, Internet Message Formats, ...

Dividing All My Spreadsheet Entries By 10
Whenever I enter a figure into a cell in a spreadsheet it automatically divides the number I enterd by 10. How do I stop this please. I have tried re-installing and tried "Detect and Repair" - any idesa please. Hi David! Try: Tools > Options > Edit Remove check from "Fixed decimal places" -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. David, Type 10 in a free cell, go Edit > Copy or Ctl+C, then select the area you want to ...

Help with Linking
I need to do three things, if you can answer any of them that would b great. First, is there a way to make a macro that goes directly to the "Ope File" screen? What about linking it to a folder name? Second: I have a worksheet set up to pull information from a secon sheet, which needs to be imported first. All the equations return #RE until the text file is imported, which is named Sheet1 by default. wrote all my equations to pull from Sheet1, but if a new sheet get imported, I need it to pull from that sheet, Sheet2 (default). M equations won't change to Sheet2, or Sheet...

Links in Excel
Is there a way to get a list of spreadsheets that a given spreadsheet is using. Edit > Links >-----Original Message----- >Is there a way to get a list of spreadsheets that a given >spreadsheet is using. >. > ...

Links tutorials
Dear all, It is very important for me to get acquinted with links in Excel. I would be pleased if you guide me for any tutorial/resource that tells the basics and backbone of how links work in MS Excel. My Excel version is 9.0.6 You can also give website resources. Thank you in advance. Mustafa Hi Mustafa, You have additional answers in excel.links You have asked the same question several times in the Excel newsgroups. Basically you are not reaching more people but are asking the same people who may visit different newsgroups at different times. Please stick to one newsgroup a...

MSP excel linking
Dear sir I have created one sheet in excel which had some linkings from MSP. But later I found out that the links used to get disabled when ever I reopened the sheet.I have MSP 2007 and Excel 2007. Can the link be trusted.I have prepared some cash flows in excel. The start and end dates have been linked with main shcedule in MSP. So can I trust the links will they hold good in the longer run? or there is some otherway. Is there any other way by which my links to the specific fields in excel will get updated when ever I updated the main schedule in MSP? -- Ame...

How to Paste Link a cell with color
I have a user who is pasting a cell from one spreadsheet to another but the original cell has a color background and when it is paste linked, the color is lost. Can anyone help with why this happens? dah That is the way it works, a link never contains the format, the user needs to do it twice, first paste the link and then paste the format -- Regards, Peo Sjoblom "Paste Link" <PasteLink@discussions.microsoft.com> wrote in message news:767FEDEC-E3B0-45DD-A6D0-2BB75FC742F0@microsoft.com... > I have a user who is pasting a cell from one spreadsheet to another but th...

Linking invoices
I was wondering if anyone knows if there is a way to link invoices to supporting documents prepared in other programs. For example, if a customer has a question on an invoice, we would like to be able to view the invoice in GP and link to the customer's PO that was scanned in or the proposal that was prepared in Word. This would save time if it could be linked somehow. There are several ways to do this. There are some neat third party apps that scan and automatically link the documents. The simple way, if the documents are already scaned and stored is to link them using OLE to...

Email Not being sent that has an email link in the signature
I am using a signature for my emails that contains an email link to which the receipient can respond to my emails - it is a different email address than the one I am using. Somehow this link is being blocked by the Outlook settings from being sent. The email will show as sent, but never reaches the server. If I take out the email link from the signature, the email sends just fine. Any ideas of what settings to change? Thanks! "tdwan" <tdwan@discussions.microsoft.com> wrote in message news:92462DAC-25FF-4F61-ADAD-6985937C6A71@microsoft.com... >I am usi...

Dynamic Links Excel 2003
Good afternoon. I use an excel 2000 file that has dyanmic links in it used for stock quotes. I save that file onto a disk and move it to another computer that also has Excel 2000, but no internet connection to receive the dynamic information. Upon opening it asks me if I want to update the links, I say no and it lists the static value at the point of save from the original computer. I upgraded computer #2 to excel 2003 and even though I say I do not want the links updated, it will now NOT open with the last static value saved from computer #1. I know have to do the copy and paste s...

Linking cells from differnet worksheets.......How?
Please bear with me here, I know and understnd very little about Excel. I have gleaned and created a few work sheets, [time cards that I morph into monthly statements for clinets, and monthly + annual gross statements for myself], pretty simple stuff. I have done this by copying and pasting formulas from templates downlaoded off the net. Sort of self eduacted if you will. So what I am missing, and after is a fromula, to link the monthly total from a clients time sheet/statment to my monthly statement. Is there a "hotlinking" formula, [or a template to copy it from], that I ca...