How do I pull a cell only when populated with user defined value in Calc?

I am trying to get an order writer set up and have a sheet set up as the general sheet. I want to have an extra sheet set up that will only populate the product and amount requested if there is an order for that product.

If I have sell apples, oranges, bananas, and grapes; but the customer only wanted to order apples and bananas, I only want those to populate on the next sheet as a printer friendly view.

I hope this makes sense to you all. I am new to Calc programming and am completely self taught so far.

Basically, what I am looking to do is take the text from A2-5, C2-5, and D2-5 and copy it to another sheet ONLY IF there is a value set in C2-5 for the respective row.

Thank you for your help!


may be you are looking for something like the following sample sheet, which uses a slightly different approach compared to your question:

  • You have a list of products in sheet Products
  • You have a list of customers in sheet Customers*
  • And you have an sheet Order, where you define (and print) the orders


In essence there are 3 input fields (implemented as Date -> Validity-Ranges from the other two sheets), which are highlighted in yellow color for better visibility.

  1. Customer in cell B4
  2. Product in cell C12, C13 and so on.
  3. And of course: quantity of the product ordered in D12,D13

All other entries are evaluated from contents of the other sheets.

Note Please don’t take this as a final solution, but a prototype layout (There is no VAT calculation, no shipping cost included, no company information and no legal information - just to name a few missing items).

That is close to what I am looking for, but the sample I gave is a very simplified problem. I actually have 100+ products and customers in 8 different countries along with all 50 US States. I’m not too sure that drop downs would help much.

I do appreciate the suggestion though.

You don’t need to use the drop downs (This is my simplified solution). The essential idea is to create orders (hopefully changing very often) from more static data as there a customers (which hopefully buy more often than once) and products.

There is an ancient OOo invoice template that is no longer on their template site but might do what you need, Invoice-Easy2.1-OOo.sxc. It was working last I used it and the macros were still working.