DGET and issue with non-contiguous cells

Kazevil · Newbie Joined: 15 Nov 2010 Posts: 2

Dearl all,

I'm trying to do a new spreadsheet with some small formulas, but get blocked on one issue.

I have a table with about 8 columns, That I want my team members to use for time reporting.
I have one column Customer that I configured with a listbox based on a list in a different sheet. Now I would like that the next column, Contract Number, gets automatically filled once a Customer has been chosen. I tried to do that with a DGET using my list in the second sheet as database and the customer column as Search criteria.
It works fine...................for the first line Sad

In fact on next lines the search criteria would look like C1-C3, which doesn't work.

Does anybody have an idea, how I could make this?

Thanks

Kaz[/img]

Villeroy · Posted: Mon Nov 15, 2010 11:52 am Post subject:

Use a database instead of database functions.
If you can not work with databases, Calc has a wide range of tools under the Data menu. Usually this requires that you organize your data in plain simple lists with fields and records. Then you can do all kinds of filtering, sorting, cross tables (data pilots) and stuff.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Kazevil · Newbie Joined: 15 Nov 2010 Posts: 2

Hi,

Thanks a lot for your answer, but unfortunately I haven't neither the time and resources to start such a solution. In addition as my team members are travelling often I need them to be able to fill the Time claiming sheet locally on their laptops. So a "simple ( Smile

) looked like the easiest solution.

In fact DGET is perfect, but the own thing I can't get it doing is to take a Search Criteria on non-contiguous cells. I tried something like:

Villeroy · Posted: Thu Nov 18, 2010 1:27 pm Post subject:

Any type of filter together with function SUBTOTAL will do. A data pilot with criteria in a page field can do as well.

Like the vast majority in this forum you try to do typical database tasks in a spreadsheet wondering why the calculator is not perfectly prepared for row sets (text tables, input forms, filters, cross table relations).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org