How to create a dropdown menu to fill a row

Discuss the spreadsheet application

How to create a dropdown menu to fill a row

Postby jackcubano » Thu May 09, 2019 9:04 pm

Hello all,

I have tried searching for help through the forums and google and was not able to find anything to help me create a dropdown menu that when I select the food item, it will auto-fill the row.

I have a table of items that I would like to use in my meal plans on the same spreadsheet or a new one. In my picture ideally, I would like to have all of the meat/fish items in a dropdown menu in A13 and once selected, will fill B13 to G13 with the item information. Is this possible?

So far all I can create is a dropdown menu by selecting A2:A8:G2:G8 and then Data>Validity>All Values and then selecting A13:A25:G13:G25 then Data>Validity>Cell Range>Source $Sheet9.$A$2:$G$8. This method requires me to manually select all the information IE amount, cal, etc.

I am really tired and hope that I am making sense hahah. I appreciate any help on how I can improve my meal spreadsheet.

Kind regards,

JK
Attachments
food list.PNG
OpenOffice 4.1.6 LibreOffice 6.0.1.1 (x64) on Windows 10
jackcubano
 
Posts: 2
Joined: Thu May 09, 2019 7:43 pm

Re: How to create a dropdown menu to fill a row

Postby RusselB » Thu May 09, 2019 9:18 pm

Welcome to the forums.
I'd suggest changing your dropdown list so that it just uses the information in A2:A8
Then use VLOOKUP with the search criteria being the entry in the dropdown box and the range being A2:G8.

Off topic, I suggest you correct your forum signature, as OpenOffice does not (yet) have a version 6.0.1.1, thus either the version number is incorrect or you are using something other than OpenOffice...I suspect LibreOffice.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5282
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to create a dropdown menu to fill a row

Postby Villeroy » Thu May 09, 2019 10:04 pm

The array function =INDEX(rectangle ; MATCH(validated_cell ; column ; 0)) [Ctrl+Shift+Enter] returns the first row from the rectangle where the validated cell matches exactly the specified column.

based on your screenshot: =INDEX($B$1:$G$8 ; MATCH( A25 ; $A$1:$A$8 ; 0)) [Ctrl+Shift+Enter]
The source of the validated cell should be $A$1:$A$8
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to create a dropdown menu to fill a row

Postby jackcubano » Fri May 10, 2019 4:44 pm

Hello,

Yes, sorry I am using librecalc. I'm sleep deprived at the moment and clearly not paying attention. I will update my signature and try out your advice out during the weekend and will post back as soon as i've figured it out.

Thanks again for your help.

Kind regards,


J
OpenOffice 4.1.6 LibreOffice 6.0.1.1 (x64) on Windows 10
jackcubano
 
Posts: 2
Joined: Thu May 09, 2019 7:43 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 8 guests