Using concatenate and Indirect to reference a range from a remote workbook

Hi all

I am trying to compare values in two ranges, one from the active
workbook and the other from a different remote workbook. I am using the
Vlookup with the table array field being populated from a range of
concatenated cells. The first one defines the path of the remote
workbook, the second defines the name of the workbook (because the
workbook name changes each week) and the third is the range name.

So the Table array part of the Vlookup looks like ths:-

INDIRECT(CONCATENATE(File_path,"Forecast_Chk_WK_",Cur_Week-1,".xls!FCast_tot"))

Now prior to adding the File_path component, and having the secondary
workbook open, it worked OK. But now I am using it as above - No deal.

Anyone able to offer a solution would be greatfully appreciated.

TIA

John

0
3/26/2006 5:26:43 PM
excel 39879 articles. 2 followers. Follow

5 Replies
477 Views

Similar Articles

[PageSpeed] 8

Sorry - Found a typo - I think - but still does not work

INDIRECT(CONCATENATE("'",File_path,"\Forecast_Chk_WK_",Cur_Week-1,".xls'!FCast_tot"))

The workbook does not recognise the link

0
3/26/2006 7:16:50 PM
Jatclarke999@ukonline.co.uk wrote...
> Sorry - Found a typo - I think - but still does not work
>
>INDIRECT(CONCATENATE("'",File_path,"\Forecast_Chk_WK_",Cur_Week-1,
>".xls'!FCast_tot"))
>
>The workbook does not recognise the link

INDIRECT only works with references into *open* workbooks. The
technical reason is that INDIRECT only returns range references, and
ranges only exist (as far as Excel is concerned) in open workbooks.

The workarounds are described in the following archived article.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

0
hrlngrv (1990)
3/26/2006 9:16:28 PM
Thanks - I found this later after sweating for a while on a solution. I
eventually resorted to referencing the data by simple linking of cells
on a different part of the sheet and re-doing the maths and then
running a VBA find and replace to change the week number in each of the
cells.

Thanks again for the pointer. Perhaps one day MS will solve these basic
issues (well they seem bassic to me )

0
3/28/2006 10:21:47 PM
Thanks - I found this later after sweating for a while on a solution. I
eventually resorted to referencing the data by simple linking of cells
on a different part of the sheet and re-doing the maths and then
running a VBA find and replace to change the week number in each of the
cells.

Thanks again for the pointer. Perhaps one day MS will solve these basic
issues (well they seem bassic to me )

0
3/28/2006 10:21:50 PM
Jatclarke999@ukonline.co.uk wrote...
....
>Thanks again for the pointer. Perhaps one day MS will solve these basic
>issues (well they seem bassic to me )

Unlikely. They made a decision a long time ago to implement external
reference links in such a way that they had to be syntactic constants,
and they made a decision that Excel's INDIRECT function, unlike 123's
@@ function, could only return range references.

When and if MSFT ever gets around to such basics as allowing multiple
files with the same base filename to be open in the same instance at
the same time, then they may address other archaic limitations as well,
but I suspect they'll give us a dancing text theme first.

0
hrlngrv (1990)
3/28/2006 11:13:03 PM
Reply:

Similar Artilces:

creeping data range
Is there some clever trick to tell a set of charts, which use different rows, to now use columns B - M instead of A - L? Our user tracks some monthly figures that are then charted. This user would like to be able to tell this set of several charts to now use a different data range after adding another month's figures, ideally with a single update. -- Greg Stigers, MCSA remember to vote for the answers you like Jon Peltier has instructions for dynamic charts: http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoCht G wrote: > Is there some clever trick to tell a set o...

what is the use of this #3
hi iam chinnu i now that what is the aim of the community ...

Reference every 4th row from a master list of modifiable length
I'm creating an excel database for a clinical study on patients wit migraine. Each patients will treat 4 attacks (episodes). Thi presents a bit of a problem, since there's some information I' collecting that's specific to the patient (demographic information lik sex, height, etc.), and some that is specific to each attack (like th pain level of that attack, the duration of that attack, etc.). T account for this, I decided to give each patient four rows--one fo each attack--and I put their deomographic information in the secon row, leaving the other three blank in those fiel...

Using the windows firewall
I plan to open up one of the interfaces on my Excahnge 2003 box to the internet so that I can use OWA. I plan to use the built in firewall in Windows Server 2003. What ports do I need to leave open to allow OWA to work? HTTPS = 443 Or if no SSL, then HTTTP = 80. Is this the only firewall between your server and the internet? troy wrote: > I plan to open up one of the interfaces on my Excahnge 2003 box to the > internet so that I can use OWA. I plan to use the built in firewall in > Windows Server 2003. What ports do I need to leave open to allow OWA to > work? >...

Using Access for Microsoft Navision Reporting
I am curious if there are companies that have used Microsoft Access to produce reports from Microsoft Navision 2009 (SQL-Server version) data and are willing to share their experiences with this approach. Thanks! Brad -- Brad "Brad" wrote:subed going to jail > I am curious if there are companies that have used Microsoft Access to > produce reports from Microsoft Navision 2009 (SQL-Server version) data and > are willing to share their experiences with this approach. > > Thanks! > > Brad > > > > > -- ...

Using the wildcard with IF
Hi all, I'm trying to set up a message that will generate from an IF formula. At the moment I'm at: =IF(E3="*C3","","Please check that you have chosen") The content of C3 is a part of the content in E3 e.g. E3=161426 oxygen and C3=oxygen. I want to check that what is in C3 is also in E3 and, if not, put up a message. All help appreciated. Try it like this: =IF(COUNTIF(E3,"*"&C3),"","Please check that you have chosen") -- Biff Microsoft Excel MVP "DamienO" <DamienO@discussions.microsoft.com> wr...

I need to use area code to determine time zone
Can't use nested if, too many entries, does anyone have any suggestions? There is a similar request at http://tinyurl.com/77tq7 -- HTH Bob Phillips "Dick" <Dick@discussions.microsoft.com> wrote in message news:3B320AC6-B209-4997-B5A6-6262E523C01E@microsoft.com... > Can't use nested if, too many entries, does anyone have any suggestions? ...

Formula cell references
When you double click on a formula that references other cells you usually see the referenced cells outlined in a color with reference to that same color in the formula. What is this called and is this something you can turn on and off? Where? Thanks RLK It's called "Edit In Cell". You get exactly the same results when you click in the formula bar. If you turn this off, double clicking in a cell containing a formula will then make the focus move to the cell(s) referenced in the formula. <Tools> <Options> <Edit> tab, Check or uncheck "Edit Directly I...

Problem when using send to -> as attachment and having the email NOT use the exchange server
We are using Outlook 2003 with both a POP3 account (the main email address for my library) and an exchange account that is only used for town communications. When we go into an office document and pick send to -> email as attachment the email always sends through the exchange account. We have set the POP3 account as default, gone into tools -> options -> send/recieve -> group accounts and told the exchange account to NOT send emails, and in the email itself we tell it specifically to use the POP3 account and it still sends the email with the exchnage server. Does anyone know how...

Using a parameter
I have a form (FormA) which has a subform(FormB). One of the listboxes on FormB uses a query using a field from FormB as part of the where statement. I've tried various versions of [forms]![FormA]![FormB].Form![PD_Date] in the where statement. The query prompts for the value and if I enter it, the query runs fine. When I try to run it on FormB, it prompts for the value. Can anyone let me know what I'm doing wrong? Likely the name of the subform control that is holding "FormB" as the subform is not named FormB. Open your main form in design view, clic...

Using previous record to perform calculation
Here's my query: CONum PONum POTotal COTotal RevTotal 1 18 $855 $12,500 -$11,645 2 18 -$11,645 $30,000 -$41,645 and so on... I want to know how I can copy the previous record value of RevTotal to POTotal to subtract COTotal and keep this going. This is really simple but I'm having hell of a time getting this right. Any help would be appreciated. could you generate a report based on this query with a group header for PONum create an unbound field for RevTotal in the ...

Graph Question. Values from different pages. Not in same cell ranges.
Hi, I have an excel work book which keeps track of players averages in a dart league. Every week gets a different page. I would like pages for individual players, that will keep track of only 2 columns on a weekly basis, which I will use to make a graph. Because the stats are sorted by high average, the players don't always stay in the same cells. The long way would be for me to reference the necessary cells, week to week. I was wondering if there is a script that can do the following pseudocode. Using C1 in the current individual player's sheet. C1 = Where "Week1.Ce...

BUG in typed dataset generation, when using maxoccurs and Simple Types??
Hi, I've looked all over for any information about this, and either this is a bug that I cannot find reported or I've misunderstood something. Lets say, in the XML Designer in VS.NET 2003, you drag over an element (call it MyElement) and add another element of type string (call it NormalElement) as a member. If you look at the generated typed Dataset code there should be just one table ("MyElementDataTable"), with one column (NormalElement). If you set maxOccurs to something greater than 1, or unbounded, then the dataset has to contain an additional table to allow for more ...

How to find all UDF calls in all workbooks
I've decided to make a few "improvements" to some of the UDFs in my personal add-in module. Now I need to find all of the calls to those UDFs in all of my workbooks, because the syntax has changed some. Is there an easy way to find them other than opening each one? I vaguely recall dealing with this before, but I can't remember if it was code or an add in. I think this will do it for you: http://www.asap-utilities.com/download-asap-utilities.php NYC public library is closing and they're ushering me out, so I can't test it now, but give it a whirl and s...

Which is easier to use for this project?
I currently have a spreadsheet that lists the following fields. · User Name · Date · Number of Request · Total Time · Total Time Per Request With these fields I would like to create a database that will give me an average of the number of request received, times the total time spent on each request, ending with the total time per request. Also, I would like to be able to run reports that will allow me to see the totals and averages by user, date, and by group. Which one of your programs would be easy to use to cre...

Excel VBA
Hi VBAers, I put some code in a workbook that I now want to re-use. My problem is I cannot remember which workbook it's in & thus, it could be in one of 30 workbooks. I was wondering if anyone can suggest a better way to find the code rather than searching for it 30 times please? Also, I think best practice is that I put my favourite code into my personal.xls file - is that correct? kazzy was thinking very hard : > Hi VBAers, > > I put some code in a workbook that I now want to re-use. My problem is > I cannot remember which workbook it's in & thus, it could be ...

Using a combo box in an Excel form
I'm experienced with Excel & VBA, but new to using Excel to create a form. I would like to simply use a cell as a drop down. Not knowing if that is possible, I have added a combo box to the spreasheet, but I am unclear how the data entered/selected in the drop down will be stored. Is it stored as a VB variable associated with the control or do I link the control to a cell to store the data? To define the values in the drop down/combo box, do I have to use cell references or can I write in the values in the properties (writing them in hasn't worked for me)? Finally, what ...

Pivot table from Multiple Consolidation Ranges
Dear All Excel Experts, Creating a Pivot Table from 1 data table in 1 worksheet gives me the Pivot Table the way I want it, meaning as many Page Fields as I defined them at the top left of the Pivot Table. These Page Fields represent the column lables and in the drop-down I can choose the data under that lable of the respective column. Now I wish to have precise the same Pivot Table, but this time from data tables spread over several worksheets, whereby the format of these data tables are exactly the same. But now the drop-down of the Page Fields at the top left of the Pivot Table g...

Remote shut down of Excel
Something that I did realize was that the excel workbooks that are giving problems are the ones where my function forces a user-form to open and it stays open until the workbook closes. So there maybe an event I need to set to clean up the shut down? Any thoughts? > ";-)" <james.holland@comcast.net> wrote in message > news:f4Odnf6dWuBxR8HcRVn-gA@comcast.com... >> This code works for ~20 sets of excel workbooks. ONE of the sets (a > single >> workbook) always crashes the workbook, with a request to contact MS with > the >> error report. The ...

Concatenate cells without specifying/writing cell address individually
Hi, Let's say I have text "we45t" in A1, "yuui6" in A2, "sfdgfd5" in A3 and so on till A45. (basically the text in each of the columns are different/random) Now I have to concatenate all the cells from A1 to A45 (with a single space between any 2 joinees) in to a single cell B1. I can get the job done by using a) = Concatenate (A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",.......) and so till A45 b) =A1&" "&A2&" "&A3&" "&A47" "&A5&...

Concatenate column with leading zeros
I need to create a column with the entire UPC code of each item listed in the item column, but when I concatenate the columns to form the UPC the leading zeros in the UPC item colun are dropped. I sthere a way around this? Thanks in advance begin 666 MAY Boise items.xlsx M4$L#!!0`!@`(````(0#(H\TT=@$```0%```3`-T!6T-O;G1E;G1?5'EP97-= M+GAM;""BV0$HH `"```````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````````````````````````` M```````````````````````````````````````...

VBA in Access or Outlook
I have a table containing email addresses and other data that I need to email out to each recipient in the table. Each email will contain in the body of the message only the data relative to the particular recipient. My question is which application Access or Outlook is the best one to code the module in to do this? I am using Access 2003 and Outlook 2003. Use the SendObject command in Access VBA. "Susan" wrote: > I have a table containing email addresses and other data that I need to email > out to each recipient in the table. Each email will contain in the body of ...

How do I use C# to build COM addin?
Hi, I would like to build COM component add-in using C# instead of VB.NET. Since QSRules.dll is not strong named, I cannot reference it directly in C# ..NET. Here is what I did. 1. Create C# Class library. 2. sn -k AddInTest.snk 3. Add this line [assembly: AssemblyKeyFile("AddInTest.snk")] in AssemblyInfo.cs 4. Declare Entry Point. public bool Process(QSRules.SessionClass session) { MessageBox.Show(session.Cashier.Name.ToString()); return true; } Unfortunately, it cannot be compiled because QSRules is not strong named. So it give...

Using sheetname variable in a formula #2
Yes that WORKS!! Thank you! One minor glitch however. My *desired* sheet name is *A-325* and the like (A-490, etc) Your code does not work for that name, however if I change it to *A325 it works. Any ideas why this is happening -- mm ----------------------------------------------------------------------- mms's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1653 View this thread: http://www.excelforum.com/showthread.php?threadid=31386 Hi should work. Have you included the apostrophes ' in the formula. Post the exact formula you have used -- Regards Fr...

use same cell w/in a function in a entire column.
I want to be able to create a entire column in which every cell in the column pulls a data point from the same cell. If I high light and paste from my initial, lets say B1 to b2 through b100. It changes the A1 data in each cell to the next to a2 a3 a4 a5 a6 a7 a8 ect.. i know this is simple but thanks anyway. Todd Put a $ in front of the row reference (ex: a$1 instead of a1) to keep the row from advancing as you fill down. (Similarly you could put a $ in from of the column reference to keep the column from advancing as you fill right). Check Excel's help on 'cell and range re...