Creating Formula with Embedded Variable ID

I need to create a single formula for my workbook that references cells from various tabs (over 20) in another workbook. 
I want to copy this formula into multiple tabs within my workbook. 
My workbook and the external workbook have the same number of and identically named tabs. 
I want to embed a variable into the formula that will take on the name of the tab that the formula is copied to.

I imagine the formula would look something like this: 
='K:\Sales Reports\[Ohio Team, Mar-21-2004.xls]variable_identifying_the_tab_goes_here'!D1

I know I can use the following formula to derive the file name 
(thanks to Frank Kabel of this discussion group for that tip

                  =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1),1)

but I'm having problems plugging it into the formula properly. 

Second issue, I also would like to embed text into this formula that comes from a column title. 

Any help is graciously appreciated.
Chris
0
anonymous (74722)
5/12/2004 10:41:02 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
263 Views

Similar Articles

[PageSpeed] 41

Reply:

Similar Artilces:

create a boot disk when I have no floppy drive?
I have the XP Upgrade CD. How do I create a boot disk when I have no floppy drive? Thanks for any help. "Bob Tyrka" <rtyrka@cavtel.net> wrote in message news:PPedndBCv5CT7ZjWnZ2dnUVZ_gqdnZ2d@cavtel.net... > I have the XP Upgrade CD. How do I create a boot disk when I have no > floppy drive? > > Thanks for any help. > What do you need a floppy for? The XP CD is bootable in it's own right... "Bob Tyrka" wrote: > I have the XP Upgrade CD. How do I create a boot disk when I have no floppy > drive? > ...

Create Expression from Func? (3.5 SP1)
Hello: Is there are way to create an Expression from a Func? I don't like cluttering up my method signatures with the whole Expression<Func<TResult, T>> type. I would prefer for callers to simply see Func<TResult, T> instead. I'd like to take a Func and create an Expression from it. Is that possible? Thanks, Travis Parks Jehu Galeahsa wrote: > Hello: > > Is there are way to create an Expression from a Func? I don't like > cluttering up my method signatures with the whole > Expression<Func<TResult, T>> type. I would ...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

Create Process
if i call fork() / CreateProcess what is/are duplicated? i know that data space is duplicated for sure how about code space? the code space can be shared to save space.... but the new process may need to do rebasing... so...does the code space also shared? thx Action wrote: >if i call fork() / CreateProcess >what is/are duplicated? >i know that data space is duplicated for sure >how about code space? >the code space can be shared to save space.... >but the new process may need to do rebasing... >so...does the code space also shared? There is no fork in Windows. Creat...

HOWTO create a Mail Merge Template in MSCRM with multiple child records
Hi, I have created successfully a basic Mail Merge template in MS Word in Dynamics CRM 4.0 for Order Entity. My challenge now is how to design a template that retrieves the order details (product and prices) related to the order record? This sounds like a parent-child template. Any idea? On Jun 2, 1:26=A0pm, "Benjie Fallar III" <bfall...@hotmail.com> wrote: > Hi, > I have created successfully a basic Mail Merge template in MS Word in > Dynamics CRM 4.0 for Order Entity. > My challenge now is how to design a template that retrieves the order > details (prod...

Creating a CFormView application produces errors
Using Visual Studio.Net 2003 I have created a C++ MFC application with Single Document Interface and Document/View support, and have changed the base class for the derived View class to CFormView. All other settings are their defaults. When I compile the source code generated, I get an error stating that the base class CRichEditCntrItem is undefined in the declaration of the CRichEditCntrItem derived class. According to the Help, CRichEditCntrItem requires the header afxrich.h If I add this include into the stdafx.H file and recompile, I get a syntax error in the constructor for the CRic...

Looking for a quick way to create a bunch of labels for annual mailing to diverse contacts
Using Office 2004 (11.2), I have numerous contacts that are part of diverse categories (some have not been categorized yet) I would like to create mailing labels for the annual calendar mailing. As of now I figured out how to select a large number of contacts & mark them as part of the annual card category (Command & click) and go to Word's Data Merge Manager where select the main document & then the address book as a data source. I went to Query Options and selected the category that fits (annual card). Now what? ...

What could keep formulas from recalculating?
I have a workbook that I received from a company with which I do business. On it are several worksheets (tabs). On one sheet, if I go to enter a formula in a cell, it displays it as text instead of calculating a value. For example, if I type =A1+A2 it will display that exact text in the cell as opposed to displaying the sum of cells A1 and A2. On other worksheets in the same workbook, entering formulas seems to work just fine. I have checked in Options, and auto-calc is checked (pressing F9 also has no effect). I can only assume that there is some setting of which I am unaware that is prev...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

Term ID Error
Ok, for the last week, I have been trying to get my PinPad (Verifone 1000se) to work. I have done all of the test and the issue seems to be when it goes out and gets the authorization, I get the Status as FAILED and Result 3: Term ID Error. I have contacted both Paymentech and Vital and they both tell me that things are correct on their end. Paymentech is the one that sent me the PinPad and was supposed to be injected. What else could be the issue? Bill, What did Vital say when you told them that error message? Sounds like one of the two of you have the wrong Terminal ID #. -- = ...

Possible to create a rule to alert on BCC messages?
Using Outlook 2007/Exchange Server 2007 Hello: I sometimes miss that I'm receiving an email as a blind copy. I was hoping to create a rule to alert me when I'm in the BCC field. However, when using the rules wizard, I only see the options regarding my name in the To or CC field. Is there a way to create this that I may be missing? Thank you. In article <#pJLIK1ALHA.4704@TK2MSFTNGP06.phx.gbl>, victoria@dig.com says... > > Using Outlook 2007/Exchange Server 2007 > > Hello: > > I sometimes miss that I'm receiving an email as ...

How to create an XML document with XmlTextWriter?
Hi, I'm trying to create an XML document with XMLTextWriter but I want to store it in memory (not write it to a file like so many examples do). I tried the following but with no luck. Should I not user this object for this purpose? Thanks, Dave MemoryStream stm = new MemoryStream() XmlTextWriter writer = new XmlTextWriter(stm, System.Text.Encoding.UTF8) writer.WriteStartDocument() writer.WriteComment("This Is A List of My Books") writer.WriteStartElement("MyBooks") writer.WriteStartElement("Book") writer.WriteAttributeString("ISBN", "1861005...

Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web
Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web Support over 147 countries and ALL languages (unicode encoding) MSG.to provides a unique personal SMS URL for you. It is easy for friends sending text message to you by web. For example, if your name is Lisa, you can register: http://MSG.to/Lisa (you can use this link to see demo) You can also register your mobile phone number as SMS URL like: http://MSG.to/+886913123456 (you can use this link to see demo) Please visit http://msg.to/ for more information. Related keywords: Short Message Serv...

R-squared formula
I was just wondering if anybody knows the formula for the r-squared value for a power curve. Excel states that it uses a transformed r-squared value. I am trying to calculate the value by doing the mathematical calculations in the spreadsheet. I have gotten the correct equation that the chart shows, but I can't seem to get the correct r-squared value. The formulas that they give are: R^2 = 1-(SSE/SST) SSE = E(Yi-Yi^)^2 SST = (EYi^2)-(EYi)^2/n E is the best I can get to a sigma in this. Sigma is the sign to sum up all the indicated values. Yi is the original Y values. Yi^ ...

How do I view my calendar by a label I've created?
I would like to assign label colors to items in my calendars (for home use) and then be able to view my monthly calendar by one label at a time as I choose. Is there anyway to do this? I have gone through all the help menus and can't find anything. I found instructions on how to create a filtered view, but under the instructions, I have no "Add Filtered View" as one of my options. Any suggestions? View | (Arrange By) | Current View | Customize Current VIew | Filter --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtlefloc...

Excel formula #9
I need some help Please. not sure how to write a formula? Description of what I want.: if cell is equal to 48 or less then I want to add 3 to the cell but if cell is greater the 49 then I want to add 6 to cell Can some one help Please. I am new with excel and am not sure how to do this.. Thanks Sun, 3 Feb 2008 19:04:06 -0800 from David franklin <DavidF@discussions.microsoft.com>: > I need some help Please. not sure how to write a formula? > > if cell is equal to 48 or less then I want to add 3 to the cell but > if cell is greater the 49 then I want to add 6 to cell ...

Increasing Month only in formula
Kindly i need help on below: i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" in Cell "A2" and "=Mar!G$2" in Cell "A3" and so on by simple dragging. is that possible? On Thu, 11 Feb 2010 12:53:01 -0800, Malla <Malla@discussions.microsoft.com> wrote: >Kindly i need help on below: > >i have a formula as "=Jan!G$2" in Cell "A1", i'm looking to have "=Feb!G$2" >in Cell "A2" and "=Mar!G$2" in Cell "A3" and so o...

Help: Seting the value of another cell with a formula
I know that there's a way to make a cell a particular value based on the entries of a range, or array of cells, but is it possible to do the reverse, using only one formula in a cell. Here's what I'm trying to do: What I would like to do is set one of a range of cell to have a value based on the value in A2. Example: Cell A1 has a value of 2, A2 has a value of 2007. I want A10 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2008. I want A11 to equal A1, i.e.:2 Cell A1 has a value of 2, A2 has a value of 2009. I want A12 to equal...

Is batch ID is required in payables transaction entry form?
Is batch ID is required in payables transaction entry form? How about if I just have 1 invoice and do not want to create a batch for it? can I still process the invoice? Hi Bab, A batch i.d. number is not required. You have to post your invoice both in the Payables module and also in the Financial module. I check weekly for any "series batches" that have not been posted in my A/P, A/R, P/R, and Financial modules. If they go unposted it can create havoc. I hope this has been helpful to you. -- Diana "Bab" wrote: > Is batch ID is required in payables transa...

can I create a new resume?
I would like to create a new resume. Can you help? This is the Microsoft Excel newsgroup. I am unaware of any newsgroup that provides help with resumes, but perhaps you intended to post in the Microsoft Word newsgroup, in which case, this tutorial may help you: http://www.officearticles.com/tutorials/write_your_resume_in_microsoft_word.htm ************ Anne Troy www.OfficeArticles.com "in need of help." <in need of help.@discussions.microsoft.com> wrote in message news:4245D9F3-C5CF-4C64-A70F-E8930F899954@microsoft.com... >I would like to create a new resume. Can you he...

Creating an OWA access only user.
Hello chaps/ladies, The pointy hairs have asked that some one external to our organisation be given access to an email account within our organisation. So my thoughts were to create a user account and lock it down to prevent interactive access to any workstation machines in any way and only allow OWA access, this has prroven trickier than I first assumed. Does anyone have a quick guide going over best practices for this type of situation? TIA. -- Alex Griffin On Tue, 14 Jun 2005 16:26:11 +0100, Alex Griffin <nntpuser@hotmail.com> wrote: >Hello chaps/ladies, > >The pointy...

How Many Days Since... Formula -Help
Hi, can anyone help? I have a spreadsheet tracking several different Departments in our company and how often they have an accident. I need to track how many days they go without an accident. So if Dept A had their latest accident yesterday. And if someone opens the spreadsheet in 3 days, it should say "4 days w/o an accident for Dept A. I'm sure this can be done, I just have no idea how :roll: Thanks in advance, Scotty Assuming 8/5/2003 is the start date... =TODAY()-"8/8/2003"&" Days w/o an accident for Dept A" Lance >-----Original Message----- &...

How do I keep a formula from changing if a row is added or deleted
I have a formulas referencing cells in another worksheet. When rows are added or deleted from the worksheet the formulas are either changed to skip the new row or receive an #REF! error when a row is deleted. Regardless of what happens in the worksheet I want the formulas to remain the same. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany cwolt wrote: > I have a formulas referencing cells in another worksheet. When rows > are added or deleted from the worksheet the formulas are either > changed to skip the new row or receive an #REF! error when a row is >...