custom invoice with drop down selections. idiots guide please!

marknorman · May 2, 2018, 4:08pm

I am trying to create an invoice in Calc.
A fairly simple design, whereby I have a drop down box in cell 1 to select a venue. Each venue has a separate costing, which when selected in cell 1, will display the individual cost in cell 2.
Cell 3 will show the number of days , and then cell 4 will show the total.

Stuck on the first bit , I’ve played around with data range and validity, and have gt the drop down, just cant associate it with the cost to go in the next cell.

I’m a real novice so any help is most appreciated. Many thanks

LibertyBelle · May 3, 2018, 12:55am

You’re trying to automate it so that when you select Venue A from a drop-down list in cell 1, cell 2 auto-populates with its associated cost of $____. I don’t know that there’s a way to do that. Macros, maybe. Would be cool to be able to do that.

marknorman · May 4, 2018, 6:54pm

exactly that. someone must have wanted to try it before me! … anyone?

EarnestAl · December 23, 2020, 11:15pm

There is an ancient invoice template from the old OpenOffice org site that I used for quite a while until I had nothing to invoice people for. It is so old that its has the extension .sxc. It was working on my computer last year so presumably still works, if not the macros are there for inspection I believe. No longer available from OOo butfrom the creator’s own web page Invoice-Easy2.1. Cheers, Al

LeroyG · December 23, 2020, 10:45pm

You need a table with your data:

Col1: Venue
Col2: Cost

In other sheet (Invoice) you can select the venue from a drop-down box. To do that:

Select the cell where the venue will be displayed
Choose menu Data - Validity… - Criteria tab
For Allow, select Cell range
Click the Shrink button at right of Source
Click in the Data sheet tab and select the range with venues
Click the (now) Expand button, and OK

With Alt+Down Arrow you can see the list (or clicking in the arrow button).

Help on Validity of Cell Contents

For the Cost column, use VLOOKUP. Total is cost * days.

See sample file.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below. Thanks.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).