Named range question

Hello all,

I have a question for the Excel gurus which read these groups.  I have had 
a look through the archives and as far as I can tell, there is no way to 
do what I want so I thought I'd ask here.

Take the simple case of calculating forecast sales; if the market for widgets 
is x million per year and your forecast market share in that year is y%, 
then forecast sales for a given year = x * y

Obviously it is possible to set up, for example, a named cell "market_share" 
and another "market_size" and then write the formula in a third cell for 
sales which has the formula
 "=market_share*market_size" to calculate sales.  This is clearly a very 
readable formula which someone else looking at the worksheet would easily 
understand.  The drawback of this is that it would only work for one year. 
 Changing the names (i.e. to add the year,so that market_share for 2005 would 
be market_share_2005) would be unworkable for any large sheet (and you could 
not drag the formulas across into a new column to add another year).

Is there some way to make named ranges behave like arrays in programming 
languages?  It would be great to have a series of named columns (i.e. 2004, 
2005, 2006) and then the formula could look like:
sales(year) = market_share(year) * market_size(year).  The formulas would 
be human readable and the formulas should still be able to be copied to new 
columns.

As far as I know this is impossible but I'd be happy to hear any suggestions 
on how this could be done.

Cheers,
Shane


0
4/7/2006 12:45:05 AM
excel 39879 articles. 2 followers. Follow

1 Replies
456 Views

Similar Articles

[PageSpeed] 52

If I had a named range JMB2005 and JMB2006

E1 = 2005
F1 = 2006

in E2 enter 
=INDIRECT("JMB"&E1)

then copy to F1



"Shane Henderson" wrote:

> Hello all,
> 
> I have a question for the Excel gurus which read these groups.  I have had 
> a look through the archives and as far as I can tell, there is no way to 
> do what I want so I thought I'd ask here.
> 
> Take the simple case of calculating forecast sales; if the market for widgets 
> is x million per year and your forecast market share in that year is y%, 
> then forecast sales for a given year = x * y
> 
> Obviously it is possible to set up, for example, a named cell "market_share" 
> and another "market_size" and then write the formula in a third cell for 
> sales which has the formula
>  "=market_share*market_size" to calculate sales.  This is clearly a very 
> readable formula which someone else looking at the worksheet would easily 
> understand.  The drawback of this is that it would only work for one year. 
>  Changing the names (i.e. to add the year,so that market_share for 2005 would 
> be market_share_2005) would be unworkable for any large sheet (and you could 
> not drag the formulas across into a new column to add another year).
> 
> Is there some way to make named ranges behave like arrays in programming 
> languages?  It would be great to have a series of named columns (i.e. 2004, 
> 2005, 2006) and then the formula could look like:
> sales(year) = market_share(year) * market_size(year).  The formulas would 
> be human readable and the formulas should still be able to be copied to new 
> columns.
> 
> As far as I know this is impossible but I'd be happy to hear any suggestions 
> on how this could be done.
> 
> Cheers,
> Shane
> 
> 
> 
0
jmb (270)
4/7/2006 2:38:02 AM
Reply:

Similar Artilces:

Office XP Exel
Scenario: User's A,B, C, and D all have access to an excel XLS on a 2003 server. Users A & B have modify rights, users C&D only have read rights. If user A updates the file, the general tab in properties reflect the exact time the file was modified. After user A saves and closes the file, user D goes in. The changes are there, but in the properties general tab, the modify date in an old date (probably the actual creation date). Is this normal ? Any idea's ? ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Newbie question
'hello, can someone explain in plain English what this formula is actually saying: =IF(C32="FOB",(B28:C28/'Board Pricing and conversion'!B19)-B28,(B28/'Board Pricing and conversion'!B20)-B28) I understand the C32="FOB", but the rest I don't. I know it is referencing another spreadsheet but don't know what it is looking at?? Thanks bassman Double-click on the cell with the formula and you will see the syntax IF(logical_test, [value_if_true], [value_if_false]) You have a logical text for "FOB" on C32 of active sheet. If True the...

Simple Question-How to create more than one transaction on the Acc
If there is a question already posted let me know. The question is: I created a bank account information on the Account list icon and want to have more than (one)transactions listed and see each payee displayed separately on each page so i could have all the months posted with due dates and total listed. Thank you. In microsoft.public.money, a.j. wrote: >If there is a question already posted let me know. The question is: I created >a bank account information on the Account list icon and want to have more >than (one)transactions listed and see each payee displayed separately ...

Suggest Names While Completing .....
Is there anyway to remove a "suggested name" short of actually turning this feature off? Our CEO's PA has returned from leave but her home email address keeps popping up as an option. Ta Ian When Outlook suggests the name, highlight it with the arrow keys and then press the delete key. "Ian Kynaston" <IKynaston@nospam.propertyoz.com.au> wrote in message news:554f01c4743a$a34b6910$a601280a@phx.gbl... > Is there anyway to remove a "suggested name" short of > actually turning this feature off? > > Our CEO's PA has returned from leave b...

Question about clip art, etc
Whenever I use clip art in Publisher or Word (2000) it says to insert the CD (#2) that has it on it. Which I do. Before I reinstalled WINDOWS a few weeks ago, and had to put everything in again, I had the clip art on my computer. So, if I needed something it was right there and I didn't need to keep putting in the CD. I have tried this several times, in Word (mainly, though I use clip art in Publisher too and have that function, using clip art, enabled- after it asked if I wanted to) The CD brings up the install and I click ADD OR REMOVE FEATURES and click on the clip art and "...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Scheduling formula question
I know both are the same equation. Which one is by definition ? "Duration = Work / Units" or "Work = Duration x Units". I have a three day task with a resource assigned (Max. Units 100%, Units:100%). All calendars are the default Standard base calendar; Hours per day is 9 hours. How do we build the equation to calculate 27 hours of work ? TBol -- To be technically correct, the Duration Equation formula is written as: Duration = Work/(Hours Per Day x Units) You find the Hours Per Day value on the Calendar page of the Options dialog, accessed by clic...

Winfax question before purchase
i dont know where else to place this and there have been alot of posts in here relating to winfax, so here goes: I run a business and all of the time we get customers telling us they faxed something, and we have no record of it. now sometimes they never sent it...sometimes they sent it upside down and we received it blank..sometimes we just lost it. I am thinking that Winfax might be a solution to my problems, but I need to know a few things. A. can I set up winfax so that I plug my fax line into my comp, it receives the fax, and automatically prints it out to my printer (functioning jus...

exchange 5.5 will not keep new server name.
We have exchange 5.5 in enviroment where windows server 2003 is setup along with active directory and active direcitory intergrated zone which is replicated to other servers. Problem: I enter the Exchange Server Name followed by the mailbox name then I select the button [CHECK NAME] In the exchange server name field after entering the new exchange server name the old exchange server name is displayed instead. This only happens in office 2003 professional and not in office 2000 professional "old exchange server name" How did you go about changing the name of the server...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

outlook in sub-domain to set use root-domain question!!!
Dear Sir Please see below more details,(We are using special railway line between Head office in Taipei and branch office in Tao-Yuan) Head office in Taipei: aaa.com(Root domain) Dc server * 2(One of it is GC Server), Front-End Exchange 2003 *1, Back-End Exchange 2003 * 2(One is named mail1, another is named mail2 ) Branch office in Tao-Yuan: bbb.aaa.com(sub-domain) Dc Server *1(No GC Server,No Exchange Server) After using ADMT v3 Tool, when I transfer an account from root named aaa.com(ou) to bbb.aaa.com. After I ins...

Can I show server name instead of drive letter?
When using the =Cell("filename") function, I would like to show the actual server instead of a drive letter because the letter is dependent on where the server is mapped and not all users have the same mapping. Is this possible? Try using: \\ServerName\drive\... where ServerName is the name of the server and drive is the appropriate drive, and then find the file on the drive from that point. "bfant" wrote: > When using the =Cell("filename") function, I would like to show the actual > server instead of a drive letter because the letter is dependent on...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

Input mask & Format question
Greetings I use the input mask \(999") "999\-9999 in the Phone field of my table. I wanted the brackets, space and dash stored in the table because the data is imported into other applications. 1234567890 is stored as (123) 456-7890. On one of my forms there is a combobox with fields CustID, CustName, Phone. My problem is how to format the phone number in the cbo. It shows up as 1234567890. Thanks in advance Becky Hi - Set the input mask to \(999") "999\-9999:0 (adding semicolon - zero). This forces the literals to be stored along with the other characters...

Altering the range that is plotted by a chart via VBA
Good morning all. I haven't really done a huge amount on charting, so I'm sure you'll think that these questions are a bit simplistic, but here goes! I plot a chart from a worksheet that contains three columns of data - the X axis contains week numbers, and is in B29:B54. Actual hours are entered in F29:F24 and cumualtive hours in G29:G24. The cumulative formula is of the form: =SUM($F$29:F29) So, each week, in order to plot another week's data, I have to copy this cumulative formula down by one cell, which, considering I have a lot of these reports, is a bit time cons...

Sales for Outlook Web Site question
Does CRM 1.2 Sales for Outlook client utilize Cassini? If not, is it supposed to copy files to \inetpub\wwwroot\ or repoint the Default Web Site to another directory? I am getting errors clicking on the Activities, Accounts, etc. and the "Promote E-mail to CRM Activity" button... Yes, it uses Cassini on port 2525. No, files don't need to be copied to inetpub. Mike "Jim Scavuzzo" <NOSPAM-scavuzzoj@ecg-inc.com> wrote in message news:eTbk8jW9DHA.2044@TK2MSFTNGP10.phx.gbl... > Does CRM 1.2 Sales for Outlook client utilize Cassini? If not, is it > suppose...

Graph question #2
Hi Folks, Quick query with regards to how data is read by a graph. I have a sheet featuring the number of staff assigned to the call centre at 1/2 hour points throughout the day. This data is stored in a sheet called 'Data' and in cells D17, E17, F17 etc etc. The data within this page is not very well presented as it is run automatically from the system we use here so I have made a more presentable table and graph. For the table for each 1/2 i simply reference the cell in the data page so that all i have to do to update the sheet week by week is paste fresh data in. Using =Data!D17 t...

Question to a MSDN-Article (WordML)
Hi all, in ref. to the MSDN-Article "New XML Features of the Microsoft Office Word 2003 Object Model" (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_wd2003_ta/html/odc_Wdnew2k3XMLOM.asp) I've got a few questions: 1.) They say, the first step to work with XML in a word document is to add the XML-Schema to the Application-Object. As I want to work with the WordML (Word Object Model), I suppose I need to load this Schema. But obviously I've got no xsd-file available. How can I manage to work with the WordML then? 2.) To extract text from a word documen...

Conditional formatting a date range
If I have a column of dates that are manual entered what is the formula to conditionally format them based on a date range of three months before the current date to the current date and another three months after the current date to the current date? Assume the dates are in column A, starting with A1. Highlight all the dates, with A1 as the active cell, and click on Format | Conditional Formatting. In the dialogue box you should select Formula Is rather than Cell Value Is and then enter this formula: =3DAND(A1>=3DTODAY()-91,A1<=3DTODAY()) Click on the Format button and choose the ...

Wrong sender name when forwarding meeting request....
Hello... need help. ! When I (user A) send a meeting request to user B and user B forwards the request to user C. User C will see me (User A) as sender. I thought it should be user B, because he forwards the request. Does anybody know this ?? Thanks Marc On Fri, 28 Jan 2005 03:25:03 -0800, "Marc" <Marc@discussions.microsoft.com> wrote: >Hello... need help. ! > >When I (user A) send a meeting request to user B and user B forwards the >request to user C. User C will see me (User A) as sender. I thought it should >be user B, because he forwards the request....

Want to modify this procedure to have a range of periods rather than a single period
This is a multi-part message in MIME format. ------=_NextPart_000_00BF_01CB206B.89E3AC60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have the procedure below I want simply modify the periods procedure below to be -- perpost >=3D = '201001' and <=3D '201012' so I want a range of periods rather than a = single period as in the example below I would really appreciate it if someone can help me out on this. Thanks=20 Sammy USE DEMOAPP Declare @perpost nvarchar(8) /* Do not edit Above=20 ...

Return address of first item in a range
I'm sure there must be a simple solution for this I cannot think of. I am looking for a formula that will return the value of the first item in an input range. For example, I want to put a formula in cell A10 that will return the first item that is input in the range Range A1:A9 (not nesessarily the first cell). If A4=200, A7=150 and all other cells are empty, the formula should return 200 (the first item in the column). Thanks for your assistance. Ashley Ashley, One way is to use the array formula (entered with Ctrl-Shift-Enter): =INDIRECT("A"&MIN(IF(A1:A9<...

Save single worksheet from workbook using the sheet name?
Gday, I have had a search through some of the previous questions and I cant quite find what I am looking for, I have a master sheet which I split into seperate sheets using one of Debra Dalgleishs macros all works fine. However my next step is to save each of these sheets into seperate folders using the individual sheet name. As ever I hope this isnt too muddled a question! Any help would be greatly appreciated TIA Ajay Ajay, You need to copy it to its own workbook and save that Worksheets("Sheet1").Copy ActiveWorkbook.SaveAs Filename:= "myfile.xls" -- H...

Question about CListCtrl extended style
I have set the style: m_ListCtrl.SetExtendedStyle( LVS_EX_GRIDLINES | LVS_EX_CHECKBOXES ); What I want to get is: 1. when one item is selected, the check box should be checked; If one item is not selected, it should be unchecked. 2. If item is checked, it should be selected; if not checked, it should be unselected. How to do that? You will have to do this all yourself since that is not the default beheavor of a CListCtrl. The selection and checkboxs are normally independent of each other. But you can simply use a multi-select list control, and as the user checks or unchecks the checkb...