« Taking Your Microsoft Office Templates, and Making Them Available in OpenOffice.org or StarOffice | Main | Making Illustrated Callouts With Clip Art From the Gallery, and the OpenOffice Callout Drawing Tools »

August 30, 2007

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a00d8341cdb1753ef00e54ee18ab88834

Listed below are links to weblogs that reference Creating a Dropdown List in Calc That References a Range of Values Elsewhere in the Spreadsheet:

Comments

Simple, elegant and right before my eyes the whole time. I've just put this to good use in my current project. You've saved me quite a bit of work. Thank you.

I just started using this sort of validation and want to ask a question. What is the difference between the name from "define name" and the name from "define range".

Hi Elaine,

Define Name, under Insert > Names > Define, is used with calculations, so that you can use more meaningful names in formulas. If you define a range as Income and another range as Expenses, then you can type the formula =Income-Expenses and get the same result as for instance A5-A6.

Define Range is more for printing or for any reference.

Solveig

Thanks for the tip!

Is it possible to use this to do one of the following things?

1. Have a multi-column drop down box. For instance, maybe having a project leader column in addition to the project name column.

2. Have a single column drop down box with adjacent cells on the entry form referring to adjacent cells on the "list" sheet. That way, if you select, say, the project name from the drop down box, the adjacent cell would automatically fill in the project leader.

I know that this kind of thing can be done with a database, but I was hoping that I would not have to set up a whole database just for a little bit of information.

Thanks,
Nathan

Thanks a lot! Was very helpful and very handy.

Hi. I was going crazy why my ranges did not work. I used the range directly like A1:A100, and the next row would pick the range A2:A101 and the next A3:A102. Thanks to your article I discovered that the absence of $ was the problem. Entering the range in $A$1:$A$100 format fixed the problem. Thanks a bunch!

In Microsoft Excel you can change the range of cells you want to appear in your drop down box and apply that change to all other validated cells by checking the box "Apply these changes to all cells with the same setting." Can this be done in Open Office without going through and changing each cell individually?

Nice and simple tutorial. Much appreciated.

Can you refer to a named range for validity rather than a cell reference?

Good tutorial!

And helpfull.
I just want to ask, if there is any way to select a range of sell like: $Shift2.$A$1:$A$100 and in the list to occur only non empty lines? If on the Sheet2 I have only 10 records, in the cell with dropdown menu to appear on ly this 10 choises, not 100, 90 of which are empty.

There's an example of cities and countries at http://user.services.openoffice.org/en/forum/download/file.php?id=333

everytime i reopen my document, the drop down list dissapears and it says N/A. Any suggestions? Thanks so much!

Hi
How do the other Data>Validity functions apart from List work? Im creating a household budget spreadsheet and have created a List of Expenses, i.e. Electricity, Gas, Phone. Im putting each month on a seperate sheet, and for each month I would like a drop down calendar-month so that I can select the date the bill arrived. Ive tried working with the Data>Validity>Date function but do not understand how to program it to show a calendar.
Can you help please?
Thanks

Hi,

Just recently received an excel spreadsheet for use in Calc. The dropdown list provides a selection of O or X. The 'X' is red and if 'O' is selected changes to green with a yellow background colour. Any idea how this is done?

Thanks

Hi Jimmy,

That's probably done with conditional formatting. Select the cell in OpenOffice and choose Format > Conditional Formatting. You'll need to set up cell styles to specify the formatting. (Format > Styles and Formatting.)

Hi Dermot,

There's a dropdown calendar function in the Date widget for forms, but I'm not sure how to do it for the validity. To use the widget, choose View > Toolbars > Form Controls, click More Controls, then on that toolbar click on the Date Field icon and draw a date field. Right-click on that date field, choose Control, scroll down and where you see Dropdown as an option, select Yes instead of No.

Hi,

This is a great tutorial. Thanks for putting the pictures too so it is much easier to understand.
Is there a way to populate the dropdown list from a database created in base rather than from other sheet in the same file?
Thanks!

Hi, I have a question about a project I am working on.

This is what I want the main sheet to look like:

Column A: Drop box in A2
A2 Drop box has company names

Column B: Drop box in B2
What Drop box B2 uses I would like to be dependent on which company is selected in A2; so that if you pick a certain company, you then have a drop box of that company's products.

Column C: Commission percentage of particular Product in cell c2; So, if I select a certain product in b2, the commission for that product will automatically show up is cell c2.

Column D: Same concept as Column C, except a different value (weighting percentage for that product instead of commission percentage)

Thank you. I will be very greatful if you're able to provide an answer to how I go about doing this.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Comments are moderated, and will not appear until the author has approved them.

GetOpenOffice Consulting

Get Book Resources

Search This Blog