Using named data ranges to have a more readable reference to items in formulas, in OpenOffice.org Calc spreadsheets

Who doesn't love a formula like this?

=(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and I just made it up to look extra complex, but just looking at it, you're not sure what it's referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

=(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

Ahh.....that's a little clearer. (Since it's a random made-up example, it might not make total sense from a business standpoint, but you're at least in the ballpark now.)

That's all very well, but how to do you add the names?

You can assign any name you want by selecting one or more cells and choosing Insert > Names > Define.

Type the name, click Add, and click OK.

Then you can create formulas like this.

Note that you can't do this:
=Ement+Admit

because those names refer to ranges. You can't do  =Ement+Admit because you can't do =B17:B20+E17:E20

You'd need to create more names to refer to just the totals. You'd create another name for the total field where the =SUM(Ement) total is, so that then you can type =TotEment+TotAdmit  (total Entertainment Expenses plus Total Admission Expenses).

 

There's a quicker way to get all your columns named. Let's say you've got this data.

Select all the data and choose Insert > Names > Create. In this case it makes sense to select the checkmarks for top row and left column since that's where the labels are. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals, for instance, like this. Note that when you're in a formula and you start to type a name, it will appear in the Formula field as Prepaid_Taxes does here. Press Enter to accept it so you don't have to type the whole name.

As mentioned before, you'll probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let's say that you've got all these amounts, Bus exp and Salary etc all over on different sheets or farther apart, so you would have to do the + instead. )

and get a result.

Tip: If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

---

---

Comments

[Brian G.]

Isn't it about time that spreadsheets allow users to actually redefine the tyranny of A, B, C and 1, 2, 3? Lotus Improv allowed that, and as it turns out, is the only spreadsheet I've ever understood how to use.

[Solveig]

Brian, you're absolutely right.

[BillinDetroit]

Is there a book somewhere dealing with OO Calc? I used to be pretty good with Quattro Pro, but didn't use a spreadsheet for years and my skills fell off the map. Yup, the last few are clinging for dear life from an ice mountain in Antarctica. Little help, please?

[Cesáreo García Rodicio]

Hi!

A one more easy way to define names is:
- Select cell (or range)
- Click on Top-Left form (you see name of cells, for example E6:E10 on figure 1)
- Write the name you want

And that's all!

[Toner]

Thank you. Nice presentation.

[wuyazi]

href=http://www.louisvuittonfr.com Louis Vuitton is luxury gifts, French fashion, the replica href=http://www.louisvuittonfr.com Louis Vuitton Handbag is woman best friend.
Offers href=http://www.louisvuittonfr.com Discount Louis Vuitton handbags and Louis Vuitton bags and all other designer handbags,free global fast shipping,low price and top quality.

[CABL]

Very interesting. Any idea about how to use Named Ranges in Charts? Chart Wizard seems to not allow named ranges.
Thank you.

[Solveig]

Hi CABL,
That would be cool, wouldn't it? I'm afraid it's just not available now.
Solveig

[shoes]

That would be precooled, wouldn't it? I'm browbeaten it's rightful not open now.
Solveig

louis vuitton Antigua
louis vuitton Bequia Leather

[shoes]

louis vuitton outlet
outlet louis vuitton
luis vuitton outlet
designer outlet online
handbag outlet
burberry online outlet
louis vuitton factory outlet
gucci outlet stores

[mo3ty]

thanks
www.mo3ty.com
www.f5ry.com
www.f5ry.com/dlil
www.f5ry.com/islam

[Louis vuitton]

Wow!Hello Everyone，let me think！Can someone Give me some good advice about louis vuitton outlet?
http://www.louisvuitton2.com

[Life Insurance]

Do you want to get a low life insurance offer?Well,Here are some good advice in http://www.lifeinsurancegroups.com.
Just For You

[amandafan]

if you plan to buy cheap LV handbags ,you can never miss king me to enter www.louisvuitton4sale.com and www.louisvuitton2.com to buy your favorites. www.tiffanycosite.com

[wow power leveling]

http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/

[cocoyu]

lace wigs
lace front wigs
wedding invitations

\

[GracetR]

The perfect stuff close to this good post should be open for people, just because they want thesis writing and professional dissertation writing service or just thesis title.

[essay service]

Hi,
Really nice work! Your article is unique, informative, interesting and is captivating attention of the readers. You have emphasized on a good point.

[Clasamente Fotbal]

well worth the read.I found it very informative as I have been researching a lot lately on practical matters such as you talk about...