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

drop down list in sheet1 for info in sheet2

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


Joined: 01 Dec 2005
Posts: 3

PostPosted: Thu Dec 01, 2005 8:41 pm    Post subject: drop down list in sheet1 for info in sheet2 Reply with quote

I want to know how i can do that.

I have some info in sheet2, here is an example:

Name | Colour | Price
Agrus Kos, Wojek Veteran | Multi | 3.00$
Auratouched Mage | White | 4.00$


Here is what i want.
In cell b2 of sheet one, i would like to have a drop down box with all the name of sheet2. When I select the name, all the infos relevant to the name is displayed in the adjecent cells.

Please, tell me if I can to that.
Back to top
View user's profile Send private message
sstrider
General User
General User


Joined: 30 Nov 2005
Posts: 40
Location: London UK

PostPosted: Fri Dec 02, 2005 11:00 am    Post subject: Dropdown Look up Reply with quote

Yup simplest way is ....

Enter by typing the name in the first field.

For subsequent entries of that name simply right click in the new empty cell and select Selection List - this will give a drop down list of all the items already in the column, select the one you need.

Put lookup formulas in the adjacent columns to return the other data.

There are now a few lookup examples on here but if you cant find any try this...

http://www.ridj.biz/OOOsamples/
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Dec 02, 2005 11:33 am    Post subject: Reply with quote

sstrider's hint works if the Tools >Cell-Contents > auto-entry-feature is on AND if your new text-values are below the existing values.
Try it below any column having text-values. The List is accessible with Ctrl+D.
So you have the option to hide the source-data, as long you don't have to edit them (select the rows, and hide them through context-menu).

Name | Colour | Price
----------<hide>---------------------
Agrus Kos, Wojek Veteran | Multi | 3.00$
Auratouched Mage | White | 4.00$
-----------</hide>-------------------------------
Now you have the name-selection in first column.
Assumed we are below hidden rows in row 100:
B100: =VLOOKUP($A100;$A$2:$C$99;2;0)
C100: =VLOOKUP($A100;$A$2:$C$99;3;0)
Back to top
View user's profile Send private message
Archange
Newbie
Newbie


Joined: 01 Dec 2005
Posts: 3

PostPosted: Fri Dec 02, 2005 8:21 pm    Post subject: Reply with quote

ok, thanks. I tried it and it works.

But it works only if i do it on the sheet where the info are. I want to have access to that info on another sheet.
Back to top
View user's profile Send private message
sstrider
General User
General User


Joined: 30 Nov 2005
Posts: 40
Location: London UK

PostPosted: Sat Dec 03, 2005 1:15 am    Post subject: Linking Data across sheets Reply with quote

Lookups can work across sheets without problems. The easiest way to get the principles of cross sheet referencing is to type an = in a cell and click in a cell in another sheet. Hit enter

You can see how the reference is formed. Its also important that you understand how that works so that you can be confident that your spreadsheet works in the correct way.

As far as the lists are concerned I think what Villeroy is saying you can link that data and hide it. Then it will appear in the list. (Apologies, Villeroy if ihave misunderstood that. )

My suggestion is a quick fix. You should be able to do exactly what you want with a OpenOffice Basic driven form however that is a an advanced exercise that would take a lot more explanation.

You should find something in either the Macro forum or the Code Snippets forum.

sstrider[/b]
Back to top
View user's profile Send private message Visit poster's website
sstrider
General User
General User


Joined: 30 Nov 2005
Posts: 40
Location: London UK

PostPosted: Sat Dec 03, 2005 1:29 am    Post subject: Reply with quote

ooOp's Take a look at data Validity in the Help file. - It appears to describe a way of doing exactly what you want - I dont have time now to explain or check it out but may do later today.

You can find the Data Validity dialogue from the Data menu and select Validity.

sstrider
Back to top
View user's profile Send private message Visit poster's website
Archange
Newbie
Newbie


Joined: 01 Dec 2005
Posts: 3

PostPosted: Sat Dec 03, 2005 5:05 am    Post subject: Reply with quote

Thank you all. I found the way to do what i wanted.

Here is the procedure.

Do the list in the Sheet 2

Return to Sheet 1
Select the cell where i want the drop down list, then go to Data -> Validity
In the allow selection, select Cell range.
In the source section, write Sheet2.A100:A400

Then you have the drop down list.

For the vlookup formula, here it is:
=VLOOKUP(A7; Sheet2.A4:E400;3)

Thank you all for your help. Before posting my first message, i did not even know what was a vlookup formula.
Back to top
View user's profile Send private message
sstrider
General User
General User


Joined: 30 Nov 2005
Posts: 40
Location: London UK

PostPosted: Sat Dec 03, 2005 6:27 am    Post subject: Mission Accomplished. Reply with quote

Archange

Great we got there in the end - just one point you need to make sure the range you enter in the Validity dialogue is anchored - then you can drag in down to replicate the formula.

I have put a sample in http://www.ridj.biz/OOOsamples/

Select the DropDownLookup spreadsheet to see how I set it up. Its set up for 30 entries.

Check the use of IF and ISBLANK to suppress error messages ifthis is new to you.
Back to top
View user's profile Send private message Visit poster's website
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