Using Labels in Place of Cell Ref in Formulas

[sudburydave]

I have some lengthy formulas and would like to substitute labels for cell references but have been unable to do so. For example:

1 A B C D E
2 Year Income Expenses IncLessExp BalAccount
3 2007 1000 750 250 250
4 2008 1150 950 200
5 2009 1100 1200 (100)
6 2010 1350 1250 100
7 2011 1150 1200 (50)

In cell E4 I have =E3+D4 but would like to be able to use labels such as
=BalAccount + IncLessExp or, =BalAccount 2007 + IncLessExp 2008.

I believe I want to use labels rather than Names since I'll be referring to relative addresses. Once I get a formula correctly written for one year I'd like to be able to copy it to all other years. However, either I haven't defined the labels properly or I'm using an incorrect syntax since I receive the error message: The formula you typed contains an error".
I'm using Excel 2003.
Any help, or link to a source which provides examples of using labels in formulas, would be greatly appreciated.
P.S. When I previewed this post I saw that the formatting of my sample sheet
is not as I entered it. If it's not understandable, I'll try again.

[protonLeah]

You must set Excel to allow labels in formulas: Tools > Options > Calculation > Accept labels in formulas. Using your formula =BalAccount 2007+IncLessExp 2008, gives the result: 450

If you wrap the data pasted from Excel the wrapper will preserve your spacing:

Please Login or Register  to view this content.

[sudburydave]

Thank you for your help. I allowed labels in formulas and I'm able to use labels in my test sheet. Now I'll see if I can use them successfully in my long formulas.

[sudburydave]

I've set up names for columns and rows and assigned them in formulas. Since I have 25 rows (i.e. years) in the sheet I'd like to be able to copy a formula to subsequent rows. However, when I do that it is copied using an absolute reference so the result is the same as the previous row. [Year=Sheet1!$B$8:$G$28]
Should I define the Year row as Sheet1!B8:G28 instead? The absolute reference syntax was created when I used Name Define.
I've attached a file in .jpg format but it's not displayed. Also, how do I wrap the data pasted from Excel? Should the data actually be pasted rather than as an attachment?

[protonLeah]

When you take out the $'s the formulas will increment the rows as you want. In order to wrap code, use the # sign. As far as attaching a zipped file vs. posting wrapped samples, it depends on the problem you're having. In this case, posting a copy of your data was all that was needed (so far ). In other cases the actual workbook is needed. Then you would attach it by right clicking on the file in Windows Explorer, selecting "send to... compressed (zipped) folder" and then attaching the zipped file to your post.

[sudburydave]

Thanks for your response.
I hate to be so dense, but I don't understand your response: "In order to wrap code, use the # sign". Where/how do I use the # sign?
I'll remove the $ and try copying the formulas. Should I be able to create names for all the rows (which are years from 2000 to 2025) which are in the "Year" column as a range by selecting the column (including the column title)?
I've had this sheet for several years and I'm trying to improve it so it can perform more sophisticated calculations and so it can be easier (even for me!) to understand (hence the interest in substituting names for difficult to remember cell references).
I'm looking for a good Excel book to use as a ready reference. Any suggestions? I took out Excel 2003 Bible (Walkenbach) from the library and that seems to be quite comprehensive although the section on substituting names for cell references was a bit abbreviated.

[protonLeah]

See the attachment for the location of the code wrappers. There should be three: "#", "<>" & "php" at the end of the second row of the menu bar. Since your columns have titles (labels) you don't need named ranges for this worksheet.

[sudburydave]

I removed the $ (even though they were included when I used Name/Define) but when I copy a formula which is calculating correctly it copies the formula exactly thereby creating the same result. I must not be defining the names correctly. I have attached a zip of the workbook and hope you'll be able to see what I'm doing wrong.
This is a bit discouraging: I thought that substituting names for cell references would be the easy part of the enhancement of my sheet

[protonLeah]

I have made slight mods to your wb and added some notes indicating some problems I found.

[sudburydave]

Your notes were very helpful. I think the use of labels in formulas is a lot simpler than the use of names, so I'll use that approach. A couple questions:
1. Is there any need for me to keep the names I've defined?
2. When using labels in formulas, are they case sensitive?
3. Most of the labels in my sheet encompass two or three rows (i.e. Bond Fund Account would be written with the word Bond in A1, Fund in B1 and Account in C1). Should I convert all the labels to one row?

When I use labels in my formulas some of the formulas work properly but for many of them I receive the NULL error even though there is a value in the cell which is the intersection of my column label and my row label. I've tried deleting and reentering the labels but to no avail.

Since my sheet contains 42 columns I've repeated the first column (which contains the years) three times in other parts of the sheet to make it easier to read and understand the data. That way I don't need to follow the row all the way to the first column to see which year I'm looking at. However, I've received an error saying that a date is used more than once and I need to specify which usage I want. Will that error be eliminated if I change the dates to text (1.e. '2006)?

I really appreciate your help but please let me know when I've used up my quota of questions. I realize I'm not the only one who needs help.

[protonLeah]

Some answers:

1. From the sample sheet you posted, I would say that you do not need the named ranges, all the calc's can be made with labels in the formulas.
2. Once you create your label, Excel will convert the case of any instance used in formulas to match the label itself.
3. Each label must be in a single cell with no spaces since the space is used by Excel to intersect the row and column label on each side of the space
4. I suspect that you got the NULL error when trying to use a label that spanned two or more rows.
5. You should only have one column of YEAR. The "...used more than once..." message is caused by the multiple (4?) columns of YEAR and maybe because you have a named range of YEAR also.
   Since your sheet is wide and needs to be scrolled, select cell B1 and choose Freeze Panes from the Window Menu. That will freeze Column A so that it will stay in place as the sheet is scrolled.

[sudburydave]

Thank you for all your help.
Unfortunately, I've been unable to get range names or labels to work. I continue to get NAME or NULL errors.
I'm going to take a break from trying to enhance my large sheet.
I'll probably get motivated later and will try again.