list cell, and its output


what I have done:
I created a product table in base, with name and price among the other fields
I created a "bill" layout in calc, which will print on a pre-printed sheet

is it possible to have a drop-down list in one of the cells, from which to select an item, and consequently have its price display, and print in another cell?

If yes, please explain how.

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can do this with Data Validation and Vlookup.

Say the data of product and price is on Sheet2 with Column A having the product names in rows 1 to 5 and the corresponding prices are in column B (rows 1 to 5)

You can select the Cells to have the drop down.   On the menu choose Data | Validity
[Lets say you picked Sheet1 Cell D1]
On the Validity window in Allow choose Cell Range
Decide if they can select blank
in Source Type enter the cell range (Sheet2.A1:A5 for our example)
Then in the where you want the price [Say E1] type
=VLOOKUP(lookupcell; array; column to return in array)
for our exampe: =VLOOKUP(D1; Sheet2.A1:Sheet2.B5; 2)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenesoAuthor Commented:

A couple of problems:

1 - language, I have to find out which term corresponds to LOOKUP, can you please tell me in which category to look for it, so I don't have to try them all.
I tried in "table", and put one that could have been and it returns ERR.504, which obviously mean I got it wrong; when putting LOOKUP it returns the #NAME error.

2 - I think I wasn't very clear in the original post, I have a database (base) table from which I'd like to call the product name and its price, I'd guess I have to put different stuff as source and instead of lookup, which I don't know (so what's new;))
VLookup is listed in the spreadsheet category in mine.

On linking to the Base data, I don't know how to do that part.  I know how to create a combo box that has the data linked to base, but I don't know how to update a cell with data based on that selection.  One idea would be to copy the base data into a spreadsheet and then use Data validation mentioned above from that sheet.  

I am trying to find some more info and run some test.  It may take a little while.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

kenesoAuthor Commented:
Thanks, meanwhile I'll try to find out the vlookup.
Was able to finally find the answer faster than I thought - -

OK to link your data to the base:

In base create a query that pulls the two columns of data that you need  Item & Price.

Then in the spreadsheet view the data source (View | Data Source).  If your database is not in the list, then it needs to be registered.  Right click in the explorer view and select registered databases and follow the prompts.

Expand out the tree structure to find your query.  Left click and drag your query to your spreadsheet.  
This is now imported as a named range  usually import1

To refresh the data:
Data | Select Range | Choose the named Range
Data | Refresh Range
This will pull in any changes from the database.

You can put this in a macro also:
      sub Refresh
            rem ----------------------------------------------------------------------
            rem define variables
            dim document   as object
            dim dispatcher as object
            rem ----------------------------------------------------------------------
            rem get access to the document
            document   = ThisComponent.CurrentController.Frame
            dispatcher = createUnoService("")

            rem ----------------------------------------------------------------------
            dim args1(0) as new
            args1(0).Name = "DbName"
            args1(0).Value = "Import1"

            dispatcher.executeDispatch(document, ".uno:SelectDB", "", 0, args1())

            rem ----------------------------------------------------------------------
            dispatcher.executeDispatch(document, ".uno:DataAreaRefresh", "", 0, Array())
      end sub

Now you can use the Data Validity function.  Still choose Cell Range, but in source put in the named range (like import1)

For Vlookup, try going to insert | Function and look for one that has a description similar to Vertical search and reference to indicated cells.
kenesoAuthor Commented:
I had found the vlookup, and it works fine.
I am having trouble using the db suggestion.

Do you mind if I drop the db linking in this question, and go on to use your spreadsheet solution, and when I'll have more time I'll get back to the db approach.

If the above is ok with you please follow below.

So, what you proposed here
works fine, but for one thing:
in the cell where the drop down list is to show, I would like to be able to select a blank choice (no product showing), with a consequent blank "price" cell. As it is now it displays one of the products, and its price all the time, which is to be avoided.
Going ahead with the straight spreadsheet solution is fine.  Just post the question when you are ready to link to the Database.

Your question on the blank row can be done a few different ways.  It depends on what you want for the user experience

Option1 (Recommended)
Have a blank row at the top of your data (Using the example from above A1 & B1 would both be Blank).  Then make sure "Allow Blank Cells" is checked.
This will present the user with a blank option at the top of the list

Extend your data cell range to a blank row at the bottom of you data (Using the example from above your range would now be Sheet2.A1:A6)  
This will present the user with a blank option at the bottom of the list

When setting up the Data Validity, Make sure Allow Blank Cells is Checked
This will allow the user to not select any item

If you use Option1 or Option2 you will need to adjust your Vlookup formula to the following:
=IF(D1="";"";VLOOKUP(D1; Sheet2.A1:Sheet2.B5; 2))

This will allow the blank price.  Otherwise you will get a #N/A message.

kenesoAuthor Commented:
>>Option1 (Recommended)

That's what I had done, but the first time I put the formula, it would let me select the blank cell, and once I would select another cell with data in it, it wouldn't lewt me select the blank one back, so to have a blank cell again; the old data would stay in the cell, till I change it with another selection.

You may like to take a look here too, I sure would appreciate it.
On the blank cell in your list (like cell a1 in our example) put a space in the cell (Type the apostrophe - ' - and then the space key)

Now give it a try.

Or you can just backspace in the cell to remove the data.
kenesoAuthor Commented:
Thank you, that ^ made it.

Points 400 > 500
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.