use a formula to reference a named range

Say I have the following dynamic named ranges (12 per year, but here are
three):

TERR_2004
CAT_2004
MONTH_2004
TERR_2003
CAT_2003
MONTH_2003
and so on and so on back to 1999


Lets say in cell A2 is Data validation list with the years 1999-2004

Is it possible to put a formula in cell B2 that would be able to reference
the appropriate dynamic range based on the year value in A2?  I did a google
search on the indirect() function, but I am unfamiliar with that function
and was not able to get any of the examples to work.  I am hoping this is
possible as the only other options I see is trying to have IT get the data
in one big chunk, but they have told me it will be painful (due to changes
in systems/platforms/etc?), or manually move the data.

TIA

--
==============================
ryanb.
xl 2002, access 2002, greatplains 7.5
==============================


0
ryanb
7/6/2004 9:12:44 PM
excel 39879 articles. 2 followers. Follow

1 Replies
479 Views

Similar Articles

[PageSpeed] 8

Upon further research, I was using INDIRECT() incorrectly
In case anyone was curious, I initially had the formula:

=INDIRECT("TERR_")&$A$2

which was returning #REF!

by simply moving the end paren, it works

=INDIRECT("TERR_"&$A$2)

sorry if I wasted anyone's time.

Thanks,

--
==============================
ryanb.
xl 2002, access 2002, greatplains 7.5
==============================
"ryanb." <rblazeiN0!!!@$P^Midimn.com> wrote in message
news:#PsM835YEHA.2480@tk2msftngp13.phx.gbl...
> Say I have the following dynamic named ranges (12 per year, but here are
> three):
>
> TERR_2004
> CAT_2004
> MONTH_2004
> TERR_2003
> CAT_2003
> MONTH_2003
> and so on and so on back to 1999
>
>
> Lets say in cell A2 is Data validation list with the years 1999-2004
>
> Is it possible to put a formula in cell B2 that would be able to reference
> the appropriate dynamic range based on the year value in A2?  I did a
google
> search on the indirect() function, but I am unfamiliar with that function
> and was not able to get any of the examples to work.  I am hoping this is
> possible as the only other options I see is trying to have IT get the data
> in one big chunk, but they have told me it will be painful (due to changes
> in systems/platforms/etc?), or manually move the data.
>
> TIA
>
> --
> ==============================
> ryanb.
> xl 2002, access 2002, greatplains 7.5
> ==============================
>
>


0
ryanb
7/6/2004 9:51:59 PM
Reply:

Similar Artilces:

Using ExecuteQuery to get Listing of Cases
I'm attempting to programatically extract a listing of Cases (similar to the Cases listing in the Case Manager of the CRM app) using the Microsoft.Crm.Platform.Proxy.CRMQuery.ExecuteQuery() method. I can successfully extract Account and Contract info using ExecuteQuery, but I've been unsuccessful in locating a sample of a "QueryXML" string for listing Cases. This is my latest attempt, but I'm not even sure if "Incident" is the correct Entity for what I'm attempting. <fetch mapping='logical'> <entity name='Incident'> <order a...

how to arrange list by first or last name
I am trying to make a list with first, middle, last name , street address, and phone numbers. I like to know how you can review the list by any combination of first name, last name, city, state, and or phone number? hi can you give examples of how your list is layed out. i am thinking a helper column with an extraction formula might work. regards FSt1 "Andy" wrote: > I am trying to make a list with first, middle, last name , street address, > and phone numbers. I like to know how you can review the list by any > combination of first name, last name, c...

Dynamic List Box using VBA
I wasn't really sure how to phrase this one -- this isn't your Typical Drop-Down List. I have a SELECT statement which queries SQL Server 2005 and displays the data in Excel. Let's say that a database lists products that have been delivered to 4 different cities, on 3 seperate days -- without knowing (in advance) the delivery dates, how could I create an input/drop-down field to allow the user to specify a particular date? I envision something like this: VBA code produces a list box of delivery dates based on a particular product and city. The user selects a delivery date ...

Can I use a Report expression for queries?
Is it possible to use the following expression that is used in reports on queries? =[Sales]/Sum([Sales])*100 Or some expression to get the result. Thank you. No. In your query, you can refer to text boxes on a form, but not on a report. That's because of the way reports are formatted (sequentially.) Forms have a current record, which identifies which value you want. Reports don't. In the context of a query, there are other ways to get totals, such as DLookup(): http://allenbrowne.com/casu-07.html or a subquery: http://allenbrowne.com/subquery-01.html -- Allen Browne ...

offset formula
hi what is offset formula and how does it work if you can explain me with an example it will be better for me t unerstand i -- Message posted from http://www.ExcelForum.com Hi first: have you lokked at Excel's help :-) Offset returns a range which is shifted and resized according to its parameters: =OFFSET(start_range,row,column,[height],[width]) e.g. =OFFSET($A$1,2,3) shifgts the cell reference from A1 two rows down and three rows to the right. so the above would return $D$3 -- Regards Frank Kabel Frankfurt, Germany > hi > what is offset formula and how does it work > if y...

Excel Formulae #2
Hi, I have a spreadsheet with value in one column & a series of dates in 5 other columns.For each row depending on the value in the first colunm a date may be applicable in one or more of the other columns.e.g 550 in the first column will mean dates will be inserted in the next 2 columns similar to authority levels. My qn. is how can I show what's outstanding if the relevant authority levels have not signed off-basically this is used as a tracker of invoices & I would like to show what is outstanding awaiting approval at anytime. Thanks Raj ----------------------------------...

Prevent formula calculation from overriding validation
I have a list of 4 numbers with a sum at the bottom. One of the 4 numbers is a calculation, the others are direct entry. The calculated number cannot be less than zero. How do I fix this so it doesn't allow this when the number is calculated? >The calculated number cannot be less than zero. You should have posted the formula. Try changing your current formula to something like this: =MAX(0,your_current_formula_here) -- Biff Microsoft Excel MVP "CindyMc" <CindyMc@discussions.microsoft.com> wrote in message news:6211BA9C-94BF-42B1-8128-4B9C...

Using Emit to Return a Value
Hello: I am trying to write a piece of code that can implement an interface's methods simply by returning a specified value. I am trying to see how Mock libraries are written. For instance, I have a piece of code that looks like this: MockFactory.CreateMock<ISomething>().Setup(something => something.Foo()).Returns("abc"); What I want to be able to do is somehow implement ISomething.Foo so that it returns "abc". However, I am having a hard time figuring out the Emit code for taking an arbitrary object and incorporating it. I've never want...

I have SQL Server installed on my workstation, can I not use MSDE
I have SQL Server Developer Edition on my workstation and I don't really want another instance of SQL on my box. Is there anyway for MS CRM 1.2 to use my existing installation? If you are referring to the MSDE datastore for the SFO client, then no, there isn't. It will install a new instance, but many of the underlying files are shared amongst the instances. Matt Parks ---------------------------------------- ---------------------------------------- On 23 Apr 2004 10:07:41 -0700, ramirez2sma@hotmail.com (Shawn Ramirez) wrote: I have SQL Server Developer Edition on my workstation ...

Using a Button to increment numbers in a database among other things.
I know that it is difficult to assess and solve a problem without being able to look at it physically. Therefore I have included a download link to the database I am working on. http://rapidshare.com/files/41280411/logbook1.accdb.html I have been have some trouble with this database what I would like to do is set up the logdb form to increment the lab number field with the click of a button following a format of the last two digits of the year "-" "0000", otherwise I would just like the field to be entered with a format of "00"-"0000". I tried using a ...

Last name, first name
I have a list with lastname, firstname in a cell. I would like to change it to firsname lastname. Any clues? officexp. Thank you. Mich - Insert a column after your name column, highlight the name column, go to Data, Text to Columns, Delimited, select comma, finish. This will give you two columns, lastname, firstname. Insert a column before your lastname column. Move the firstname column to the new column. Your third column should now be blank. Enter in the formula =CONCATENATE(,A1," ",B1)in the third column. This will put your firstname space lastname in the third ...

How to total cells in a range with data input
Greetings to all members I am running Excel 97. An office colleague presented me with an issue that might be of interest to any Excel whiz and seasoned programmers. I would certainly appreciate any pointers in solving it. So here goes. The set of values includes 33 cells ranging from B4:D14. The data type is numeric. Data is only input in a few cells. QUESTION What would be the function to enter in, say, cell A15 to indicate the total number of cells in range B4:D14 that have received data input? Or does it involve some behind the scene VBA programming? EXAMPLE B6 = 9 C9 = 4 D11 ...

How can i use DAO Application
Hello freinds I have a software which is made in MFC and connected with MS Access 97 through DAO(Direct Access Object via MS jet Engine). Now i want to use same software for Postgresql and wanted to connected it through ODBC. Problem is that Old software has been developed for DAO,all main file like ,recordset,view doc r made for DAO. If i want to make it in ODBC,my classes like Set ,view Doc and other would be generated for ODBC. I dont know any option to use same software, Should i write whole software from the begining? I need ur help. thanks Sheikh ...

Linking CRM references when creating Workflow Activities
I have created a Workflow Process that creates a followup phone call and letter whenever a CRM Opportunity status changes from "Qualify" to "Quote". For the Activity Name/ Subject I would like to reference the Opportunity Topic (Name). Is there an easy way of doing this? NOTE: For the Opportunity Topic we are using the estimate number. ...

Formula #34
I need a formula that for every equivalent of $1280 in one cell another will equal 350. i.e. a1 b1 1280 3840 a12 b12 350 1050 I hope that makes sense. A. Hi =INT(A1/1280)*350 -- Regards Frank Kabel Frankfurt, Germany "Andre" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:79be01c4845f$f8529de0$a401280a@phx.gbl... > I need a formula that for every equivalent of $1280 in one > cell another will equal 350. > i.e. > > a1 b1 > 1280 3840 > > a12 b12 > 350 1050 > > I hope that makes sense....

Question on LABEL Range
In a LABEL Range(say that it has 12 elements row-wise), how do one refer to the 4th elements? I have this ideas that in a worksheet, I have 12 rows single column LABELED range called MONTHS, where 1st elements is JAN(that is C6), and so on, and last element would be DEC(C17). How do I get the 4th element which will be APR, and put that value in the different cell using the LABEL reference instead of the absolute cell location(C9)? Thanks I am not sure I understand your question? You refer to the labels with their names, so if you want to sum the April row you use =SUM(Apr) Otherwis...

Naming new server
Cana anyone tell me if there are any implication with Exchange 2003 with having the server physically named "xyz.company.com" and having a cname in the internal dns that point "mail.company.com" to "xyz.company.com"? I'd sooner not have to have users needing to know the specific name of the server they need (whether using pop3/imap/smtp/webmail) but I don't know if using an alias is likely to cause problems? TIA, Paul On Fri, 27 Jan 2006 02:56:43 -0800, Paul Hutchings <paul@spamcop.net> wrote: >Cana anyone tell me if there are any implicat...

Problems with Using Word as Email Editor
We use Hummingbird DM 5.1.0.5 for our document and record management. When using this program, it has a problem with Outlook using Word as an Email Editor. When this is turned on, it seems that Outlook opens a different instance of Word. When you try to open or create a word document, you lose the ability to save or print. These features return to normal when you uncheck Word as an Email Editor in Outlook. I have heard that Microsoft was taking this up with Hummingbird to have them fix it. I am just wondering if this was to be resolved, or if it is Hummingbird's problem to ...

Windows 2008
Hi, I am an admin in windows 2008 server. However, I am not able to access the c:\Users\<user name>\Local Settings\History Anyone knows how to change the settings so I can access the folder? Thank you, "Jack Black" <gwklocker01@gmail.com> said this in news item news:60b578d9-ce73-4ab8-851b-9039ea6cfee5@n16g2000yqm.googlegroups.com... > Hi, > > I am an admin in windows 2008 server. However, I am not able to > access the > c:\Users\<user name>\Local Settings\History > > Anyone knows how to change the settings so I can...

Formula to Convert Minutes:Seconds to just seconds
Hi Gang, I will explain best way i can. If Cell A1 contains the time 01:30, thats 1 minute & 30 Seconds, Then need cell B1 to display the value as just seconds so that it looks lik this: 90. Again if a user types 01:00 in Cell A1 then Cell B1 should convert an display this as 120. I know the basic maths behind this, using the 01:30 as an example First excel should take what ever is before the : and multiply by 6 (to convert the minutes into seconds, then it should add the result t the number AFTER the : (which are seconds anyway and the end result i 90, and 90 is what should be displ...

Percentile: Different Result Using VBA?
If I populate some cells on an Excel spreadsheet with, say, 1, 2, 3, 4; and then set another cell to =Percentile([cell range], .9) I get a result of 4. OTOH, if I'm in VBA and feed the those numbers to gExcelApp.WorksheetFunction.PercentRank, I get 3.7. Only diff I can see is the use of a VBA array of double to pass the numbers 1-4. Does this sound familiar to anybody? -- PeteCresswell Try formatting the cell containing the percentile function to display some decimal places: then you'll get 3.7 as expected. "(PeteCresswell)" <x@y.Invalid> wrote in message news...

automatic capitalize first letter of payee names
I just transferred from Quicken. I had it set to automatically capitalize payee names as I typed them. Is there a way to do it in Money 2004? No. "GB" <garyburkhardt@centurybt.com> wrote in message news:OZT4Yz1jDHA.3256@tk2msftngp13.phx.gbl... > I just transferred from Quicken. I had it set to automatically capitalize > payee names as I typed them. Is there a way to do it in Money 2004? > > Type them correctly once. The next time you start to type, the field will autofill. When you tab to the next field cap and all will be as when you originally typed the...

Inability to connect with exchange server using entourage
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: Exchange I have a 2008 version of entourage that I connect with my exchange server. I added a ms office home edition to my computer, and now I am unable to connect to my server. The error message now says that I must upgrade to a std edition of office in order to connect to an exchange server. If my previous version of entourage would connect and it is still on the computer, why won't it do it now with office installed? Any advice? <br> thanks What version of Microsoft Office 2008 entourage did you down...

Email naming standards
I'd like to take a survey of email address naming standards and which ones you use. I've looking online for a "naming conventions" FAQ but had very little luck. At my company, it's currently lastnamefirstinitial@company.com, or smithj@company.com. I would like to lobby to change it simply because it's so out of step with the rest of the world. The way I see it, when it's jsmith@company.com there's a logical progression from left to right. We are changing the company name (and domain name), so the way I see it this is the perfect time to do it. Opinions? ...

Using OUTPUT TO in VBA, then how to modify the XLS from Access?
I'm using the simple "OUTPUTTO" command to send a query over to Excel, however we will have many users and we would like to programmatically add the header rows, date printed, turn on the auto filter and freeze panes, all of which I can do within a Macro in Excel, but how do I call that macro to run from Access and How to run it on the file that they just created? Current Code: DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True "ThriftyFinanceGirl" wrote: > I'm using the simple "OUTPUTTO" command to send...