OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

DGET and issue with non-contiguous cells

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Kazevil
Newbie
Newbie


Joined: 15 Nov 2010
Posts: 2

PostPosted: Mon Nov 15, 2010 11:48 am    Post subject: DGET and issue with non-contiguous cells Reply with quote

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]
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Nov 15, 2010 11:52 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Kazevil
Newbie
Newbie


Joined: 15 Nov 2010
Posts: 2

PostPosted: Thu Nov 18, 2010 5:47 am    Post subject: Reply with quote

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:

Code:
=DGET(CustBase,SLAContract,C1-C4)


but I didn't found a way to give it the last parameter...

Regards

Kaz
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Nov 18, 2010 1:27 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group