Defining a name

Whats up everyone.  Can I have a table array that is dynamic based on a
input sheet.

For example I have a lookup table that 

=VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE)

I would like CompanyDataQ3 to change based on the input sheet.

On the input sheet I have
Quarter


so if quarter on the input sheet is Q3 the lookup table will have 
=VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) 

if the quarter on the input sheet is Q4 than the lookup table will hav
=VLOOKUP(Reference!$E6,CompanyDataQ4,Reference!$F6,FALSE) 

Please let me know if this is possible and how to best achieve thi
goal.  I have some vb in the project so if I need to add more its n
problem.  Thank you all in advance

--
Message posted from http://www.ExcelForum.com

0
8/30/2004 6:41:42 PM
excel 39879 articles. 2 followers. Follow

3 Replies
738 Views

Similar Articles

[PageSpeed] 0

Hi
if your qurater in the format Qx is in cell A1 try:
=VLOOKUP(Reference!$E6,INDIRECT("CompanyData" &
A1),Reference!$F6,FALSE)


--
Regards
Frank Kabel
Frankfurt, Germany


> Whats up everyone.  Can I have a table array that is dynamic based on
> an input sheet.
>
> For example I have a lookup table that
>
> =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE)
>
> I would like CompanyDataQ3 to change based on the input sheet.
>
> On the input sheet I have
> Quarter
>
>
> so if quarter on the input sheet is Q3 the lookup table will have
> =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE)
>
> if the quarter on the input sheet is Q4 than the lookup table will
> have =VLOOKUP(Reference!$E6,CompanyDataQ4,Reference!$F6,FALSE)
>
> Please let me know if this is possible and how to best achieve this
> goal.  I have some vb in the project so if I need to add more its no
> problem.  Thank you all in advance.
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
8/30/2004 6:54:11 PM
THe only issue is that indirect requires the workbooks to be opened.
The results document has links to 25 workbooks.  Any advice?


Thanks for helping me. I appreciate it a lot

--
Message posted from http://www.ExcelForum.com

0
8/30/2004 7:17:36 PM
Hi
no chance with INDIRECT. Also most other alternatives (as shown in this
link: http://tinyurl.com/2c62u) won't work. You may use Harlan Grove's
PULL function in this case (also described in this link)

--
Regards
Frank Kabel
Frankfurt, Germany


> THe only issue is that indirect requires the workbooks to be opened.
> The results document has links to 25 workbooks.  Any advice?
>
>
> Thanks for helping me. I appreciate it a lot.
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
8/30/2004 7:24:27 PM
Reply:

Similar Artilces:

Sorting Names i the Contact View
Is it possible to sort contacts by last name, first name instead of full name? The fullname contains the first and last name as specified in the organization settings. Go to Settings / System Settings. There is a combobox defining how the fullname is calculated. If it is "First Last", change it to "Last, First". After you changed this setting, the fullname will contain the desired format and your views are sorted the way you want. The problem is that the full names of existing records are not updated automatically. You will get the old "First Last" until yo...

Add Check Name to Safe Pay export
We need the capability within Safe Pay to export (Output Field) the Vendor's Check Name listed in the vendor card as additional information to provide the bank. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/Ne...

Change size of name box
This is a multi-part message in MIME format. ------=_NextPart_000_003C_01C3D233.5A184190 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is it possible to change the size of the name box ? And how do you do it = ? Some of my range's names are to long to fit in the actual name box. Thanks in Advance, and may all of you have a healthy 2004 Gilbert ------=_NextPart_000_003C_01C3D233.5A184190 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HT...

Check Name in a Specific Customer Record
Hi, Have to use the after update event of a combox rather than the notinlist event to allow for a third option. Combobox is Combo111 which has a control source [Contact Name]. The combo has a list of names etc which have already been filtered to a company. tblCustContacts has records with fields of Last_Name, First_Name, RecordID, and CUSTID. RecordID is unique. User enters a name "Fred Jones" Jones is filtered out. Now I need to check if Jones is in the list associated with that particular CUSTID. If not - user can choose to add to the list adding details ...

Leader with Style name
I am creating a document (Word 2003) with styles A and B that toggle to each other. My problem: how to lead the paragraph with the style name? Outline Numbering doesn't quite handle it and there's no Field with the style name included (apparently). Anyone..? Eric You mean you want the paragraphs to look like this? Question: blah-blah (in Question style). Answer: blah-bah (in Answer style). If so, see http://sbarnhill.mvps.org/WordFAQs/QandA.htm (Word 2003 and earlier) or http://sbarnhill.mvps.org/WordFAQs/QandA2007.htm, as appropriate. -- Suzanne S. Ba...

Set Named Range with Last Row
I'm trying to create a Named Range called "DataRange" based on my values as listed in LISTING 1. For example, "DataRange" would currently be B2:B10. The trick to my problem is I will be inserting a row after Row 1 with code, thus shifting "DataRange" to B3:B11 for example. The other issue is I need the Named Range starting at B2 to the last row in Column B where data exists in Column A. To clarify, in LISTING 1 there is no data in B10, but there is data in A10. I will always need to get the last data row in Column A and then create the Named Range from ...

Insert Contact Name and E-mail address into Subject When Creating A Phone Call
Hi guys, I am looking to insert the name of the sender\contact and e-mail address into the phone call subject line. I have figured out how to get the name of the sender inserted into the subject line but cannot figure out how to get the e-mail address in there. The code I used is below. Can anyone outline what code I need to use to get at the e-mail address, maybe it's not actually possible? Cheers, Mark var lookupItem = new Array(); //Get the lookup for the from attribute on the phone call form, this is the person calling lookupItem = crmForm.all.from.DataValue; //Set the current sub...

Naming a formfield
I am using Word 2003, I have added a formfield with code as shown below, declared the ffield as a formfield but the code is debugging with object variable not set for some reason on the '.Name'. I have successfully used this bit of code in other templates I have created. Can anyone offer me an alternative or solution please? Set ffield = _ ActiveDocument.FormFields.Add(Range:=Selection.Range, _ Type:=wdFieldFormTextInput) With ffield .Name = "contaminant" With .Text...

Dynamic range does not appear in name box
I have defined a dynamic range using the offset and counta functions in the define name box. The name works fine in my formulas, but does not appear in the drop-down name box on the formula bar. Is there a way to get this name to appear there? Thanks. Dave Nope. But you could use Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp DaveNadler wrote: > > I have defined a dynamic range using the offset and counta functions in the > define name box. The name works fine in my ...

Defining X axis in Charts
I am using Office XP's Excel and this is probably a stupid question but I cannot find the answer on my own. I am creating a line chart from data in Excel where the X axis is a series of dates. Yet using the Chart Wizard all I can get the X to show is consecutive numbers (1.3.5.7.9.etc.) Is there a way to make the X axis show the data in a corresponding column of data that I have within Excel? There seems to be no place I can assign this date data to the X axis so it shows up there instead of consecutive numbers Excel creates for the X axis. Hope I am clear. Jeff In the ch...

Excel Range Names
I am trouble shooting a worksheet for a colleague. She receives a work sheet with formulas that are created by using range names. She wants me to delete the range names and supplement the cell addresses. One of the formulas is a SUMIF. The value in the criteria field changes with each cell. When I try to copy the formula using the cell addresses it will not work. Is it necessary to do each cell by hand or is it possible to recalculate a range at a time. I have tried the manual calculation as well and it is not working. Any help would be appreciated. -- Tech Trainer Tech Trainer, IF t...

adding a domain name to my 6.5 server
We have acquired a second domain name and will be transitioning to the new domain. I have set the mx records for the new domain name. Can someone tell me what I need to do set my users to receive mail from both the old domain and the new domain? Currently, when I send a test message, it states that relaying is not allowed. Thanks in advance, Steve Add the new domain to your default recipient policy (Exchange will not accept mail for a domain unless it is listed in the recipient policy). This will update all users and add the new e-mail domain. -- Ben Winzenz Exchange MVP MessageOn...

Incrementing named cells
Merry Christmas everyone!!! Was wondering if any of you can help me with this. I have a financial model that currently has numerous named cells in the format of XXX_03 ( the 03 is to denote 2003 ) but I am making 2004 projection and therefore am wondering if there was an easy way to sort of copy and paste the names into a new column and automically renaming cells by an increment of 1 to XXX_04. If anyone can help, it will be greatly appreciated! Thank you Justin See one answer 1 minute later -- Don Guillett SalesAid Software donaldb@281.com "Justin" <jsim_hba2003@hot...

GAL names vs. Display Names
details: windows 2003 exchange 2003 windows xp outlook 2007 Is it possible to have the global address list sorted as "lastname, firstname" but still have outgoing emails shown as coming from "firstname lastname <email@address.com>"? I have found info on using ADSI to change the display names, but that changes what the outgoing display is set as On 20 Nov 2006 09:43:40 -0800, csawatsky@steinbach.ca wrote: >details: >windows 2003 >exchange 2003 > >windows xp >outlook 2007 > > >Is it possible to have the global address list sorted as &q...

Limitation on inf and cat file name length?
Hi Driver Experts: I have a simple question: Must the inf and cat file names be in 8.3 format? Can they be longer than 8 chars? Any difference on 64-bit OS? Thanks in advance. Polaris there are no file name length restrictions in general. 64 bit XP (which is really server 2003 64 bit under the covers) has some restrictions on cat file name length, I think around 28 chars d -- This posting is provided "AS IS" with no warranties, and confers no rights. "Polaris" <etpolaris@hotmail.com> wrote in message news:uo#FMe5fKHA.6096@...

Invalid object name when I try to create a scaler udf in a stored procedure.
I am trying to create a report that shows a company name, all the advisors in that company, then the company's stats (there should be one row for each company). Each of these come from a different table. The advisors are currently being listed as one per line, which means the company name and the stats are all being repeated unneccesarily. I want to concatinate the advisors, so that all the ones that belong to the same company show in the same cell. I wrote this UDF which is located right before my SP: CREATE FUNCTION [dbo].[ConcatAdvisorNames](@Company as varchar(20)) RETURNS VA...

Changing Sign-in Name on Opening Screen
I recently changed my Passport account to reflect my new email address. When opening Money 2004, it still fills in my old email address as my sign-in name. In Password Manager, it shows Passport emabled for my new ID correctly (my new email address). How can I get Money to fill that in on the sign-in screen rather than my old ID ? I suppose you could remove the passport association, run up money a couple of times and then reassociate it? -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To s...

Payee Name Woes
In trying to figure out why I had so many payee name mis-matches I deleted all my payees and started over. However all the old "User Altered Payee Name" to "Payee Name Variations" still seem to be there though I have no way to see what they are. For instance I transactions as "Subway" in the register, but no longer listed as a Payee. If I look at the "Preferred Payee Name" screen that shows the known matchups from the "Payee Rules Manager", nothing is listed. I then download and accept a transaction for "Subway". 1st, off, i...

How do I setup a 500 name address list?
A local lawyer has recently given me the job of setting-up a 500 name address list which will be used to send out newsletters. I was wondering if anyone out there had any ideas as to a very professional and crisp template that I could use to make this thing look spectacular. I will be using Microsoft Excel. No specail version, just excel. I'm sort of starting from the bottom here. Thank you. Why excel instead of Access? However, if you do it in excel set it up so it is easily transferable to a database like Access. Think of all the uses it might/could be used for. Plannin...

Name could not be resolved. The connection to Microsoft Server is
How do I solve this? I tried deleting the account from: control panel > mail Outlook will not start in safe mode either. Name could not be resolved. The connection to Microsoft Server is unavailable. Outlook must be online or connected to complete this action I couldn't delete email or data accounts but after deletign the default profile and allowing Outlook to configure automatically the issue resolved itself. "brainstewn" wrote: > How do I solve this? > > I tried deleting the account from: control panel > mail > Outlook will not start ...

application could not be opened the file name was invalid or too long
My iTunes Program could not open I keep getting the following messages every time i open the program. "application could not be opened the file name was invalid or too long" I tried to repair the program with the repair option from a new iTune download, this did not fix it. I then removed the iTune Program and reinstalled a newer version. The new installation found and removed files from my original installation. I am running Vista on a Tosiba laptop. Note I am not trying to move or copy music files all I am trying to do is to open a freshly installed iTune prog...

Consolidate mutiple names in Column
Hi, I have a column that has mutliple names in it like shown in the example below. M Arque J Amati J Amati M Arque T Edwards J Hall J Hall M Desai M Desai Is it possible to consolidate the name in another column to count the names as one entry? http://www.contextures.com/xladvfilter01.html#FilterUR -- Regards, Peo Sjoblom "Greg" <Greg@discussions.microsoft.com> wrote in message news:52EEAE0E-B50A-4A70-AF8B-385B00D59117@microsoft.com... > Hi, I have a column that has mutliple names in it like shown in the > example > below. > > M Arque > J Am...

macro to change the names and delete closed books
Hi, In a folder located in C:\Documents and Settings\Tim\My Documents I have a number of workbooks and I need to create a code which will change the names of the workbooks automatically. To be more specific the names of the workbooks are: 1,2,3,4 and 5, and the code should change the names like this: book "5" becomes 4, 4 becomes 3, 3 becomes 2, and the name of book "2" becomes 1, book 1-delete. Any help is highly appreciated. how about: Option Explicit Sub testme() Dim myFolder As String Dim iCtr As Long myFolder = "C:\Documents and Setting...

Remove Cache Name
Dear all I need a way to erase the Outlook cache which is used insert the name in the, To or CC, before writing the full name (ex Khaled Abdeen is suggested when wrinting Kh). Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your arrow key to migrate to the entry) and then hitting your Delete key before you tab out of the field. To clear the entire cache, follow the instructions here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q287623 -- Russ Valentine [MVP-Outlook] "alexship7@hotmail.c...

In MS OIutlook, how do I remove primary acct.name from my outgoin.
...