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

Returning Multiple Values from a list

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


Joined: 21 Apr 2009
Posts: 9
Location: Perth, WA, Australia

PostPosted: Wed Apr 22, 2009 5:19 pm    Post subject: Returning Multiple Values from a list Reply with quote

Hey all,

I have a list of employees as part of a leave calendar.
The employees are listed left ot right on the top row, dates listed top to bottom on the left most column. If an employee is on leave the corresponding cell will contain "RnR".

I want to be able to make a simple report sheet, where the date is entered and the names are returned for who is on RnR for that date. I have used VLOOKUP with similar sheets, but this time I want to return a list of employees rather than a single value.

Im not sure how to do this, it may involve arrays, which I have not begun to use yet?

Any help very much appreciated!

-w0lv3n
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Thu Apr 23, 2009 5:20 am    Post subject: Reply with quote

The problem here is that the output is of unknown length.

My approach would be:
1. Use Match to identify the correct row.
2. Use Offset to create a row which contains the column number if it is RnR, otherwise 9999
3. Use Small to sort the column numbers
4. Use Offset to regain the employee names.

The intermediate lines can be hidden on other pages.

With correct referencing all the formulas can be dragged out.
Here it is done:

http://f-eeg.com/images/RnR.ods
Back to top
View user's profile Send private message Send e-mail
w0lv3n
General User
General User


Joined: 21 Apr 2009
Posts: 9
Location: Perth, WA, Australia

PostPosted: Thu Apr 23, 2009 4:35 pm    Post subject: Reply with quote

Thanks mate, ill see about implementing that into my sheets.
Appreciate the reply!
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
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