Ask Your Question

Using named ranges to simplify formulae [closed]

asked 2013-08-22 17:43:56 +0200

jc gravatar image

I have a spreadsheet with transactions with column headings

Date, Item, Amount, Who

Date and amount are I hope obvious, Item is just a text description, and Who is somebodies initials.

I want to sum who has paid what so I can use sumif(Who column, person, Amount column) for example =SUMIF(Purchases.D2:D17,"JC",Purchases.C2:C17).

Two things I would like to do but can't see how:

1) Name the list of transactions e.g. purchases, then for the sumif somehow put in the formula "column 4 of purchases" i.e. put a subset of a named range in the fomula. Of course I can simulate this by naming ranges something like 'purchases_date, purchases_item etc., but it is not quite as good.

2) Have the named range extend when I add a row. At the moment my range does not include a header row (just labelling the columns) and a trailer row which provides the total expenditure. I want to be able to insert a row at the bottom and have the named range grow. At the moment I have to insert above the last line to have the range grow.

Item 2 is the most important. I need to allow the range to grow and the defined name to grow with it, so my calculations on the table (on another sheet) don't miss rows. How do others achieve this?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-07 03:53:08.653354

1 Answer

Sort by » oldest newest most voted

answered 2013-08-22 20:55:27 +0200

m.a.riosv gravatar image

First, I think could be good try with a Pivot table (Menu/Data/Pivot Table), much easy than compose your own formulas.

There is an option to use column labels in formulas (Menu/Insert/Names/Labels)

Also there is an option in: Menu/Tools/Options/LibreOffice calc/General - Expand references when new columns/rows are inserted.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-08-22 17:43:56 +0200

Seen: 1,778 times

Last updated: Aug 22 '13