How put list-box on each cell, select data from next sheet?

[onidarbe]

I'm sorry but I can't seem to understand how to put some pul-down-list on a cell.

I like to be able to pick a date on a prespecified weekday from a list of 53-weeknumbers showing also there corresponding date. Then with cell-format I only will see the weeknumber though having a date behind it. This way I don't have to look up the weeknumber on the wall-calendar

Is there a way to have a list-box on all the cells in 1 column, so I don't have to copy and put it in each cell?
Is there a way to have a list-box triggered when entering a cell?
Is there a way to have the list been filled with data from sheet 2?

Thanks,
Michel

[bobban]

Is there a way to have a list-box on all the cells in 1 column, so I don't have to copy and put it in each cell?

I don't know a way to automate this (apart from a complicated macro).

Is there a way to have a list-box triggered when entering a cell?

Not sure on that one either but I cannot see a way.

Is there a way to have the list been filled with data from sheet 2?

Yes. After you create the listbox do not a choose a data source as this only allows for linking to a database. Now right click on the listbox and select control. In 'Source cell range' enter Sheet2.F3:F12 if you want the cells in that range as the data. You may also want to link it to the cell it is currently in (this means that cells gets its value from the chosen option).

[Villeroy]

All those form controls are made for database forms in the first place, some of them can be bound to spreadsheet cells(because Excel can do that, I believe) and some of the specialized controls can not be bound to sheet cells since sheet cells don't provide data types matching the assumed data type of the control. Bobban outlined how to use so called cell validation to turn the sheet cell itself into a list box which can be copied across ranges. However, he overlooked one thing: like ordinary formulas in cells and conditions in conditional formatting you are always entering formulas as validation criteria. Always thing of a "=" in front of whatever yo type in conditional formatting and cell validation. So his example should read: $Sheet2.$F$3:$F$12 (the same with a single row works horizontally as well). Since version 2.3 we can do some database-like magic with those formulae. They allow *calculated positions* of validation lists (because Excel can do that, I believe). Check out http://user.services.openoffice.org/en/ ... php?id=333

[onidarbe]

Thanks, but it seems that I'm looking for something else then a listbox. I don't want to draw a listdox on top of the sheet, but transfer a cell in a drop-down-selection.
I just want to enable all cells in a column to be able to select a date from a list of weeknumbers showing the date's of a particular day like for thursday: 1=1/1/2009, 2=8/1/2009, 3=15/1/2009, ... coming from a range on an other sheet. But still be able to just type in a date, that with the cell-format is being displayed as a weeknumber, though displaying the full date in the formula-bar.

[Villeroy]

Like the attachment?

Edit: 17th Jun 09: I apologize for the faulty and unnecessarily complex stuff. I replaced that shit with a simplified version weeknums2.ods.

[onidarbe]

YEAH, I like it

It looks like something I could use after some tweeking. It does give an error here with me: columns D "#Name?", but I'm shure it's something minor.
Now I only need to figure out how you did this! I can't even seem to unhide column C !?

How do you make those pull-downs that show up when the cell is active? Please explain...

Thanks!

[Villeroy]

I'm really bothered about the #NAME errors. This should not happen.

Hit Ctrl+F8.
Grey cells indicate locked cells on the protected sheet.
Green font indicates formula results.
Blue font indicates numeric values.
Black font indicates text values.

The unlocked input cells are B1 for the year number and B3:C115 for the week number (1-53) and the weekday name (Mon-Sun).
Turn off menu:Tools>Protection>Sheet.
Cells are have a lock by default. You can remove the lock in the formatting dialog. When you protect a sheet, all remaining locks get closed.

Select some range which includes column C, for instance B1:D1 and unhide column C.

See menu:Data>Validation for the "list boxes" in B3:C115. This is not as good as a database form. You can paste anything into those validated ranges and modify as you like. It's just against casual input errors.

I prepared the formulas until row #100, so you don't need to adjust (drag down) the formulas with the growing list until this point. I unlocked A101:B115 by mistake.

[onidarbe]

What are you talking about? Ctrl+F8 doesn't do anything, I don't see any Grey cells nor green or blue fonts?!
The function "Tools>Protection>Sheet" is grey and therefor not able to unlock the sheet?! Can't unhide C!

But I did found data>Validity. Didn't notice this before

I'll have a better look at it.... Thanks!

[Villeroy]

Ctrl+F8 is the usual shortcut to toggle menu:View>Value Highlighting
You do not tell us anything about ypur office version nor operating system. Anyhow, the spreadsheet should simply work without errors and I can unprotect the sheets even if the file has been loaded read-only.
There could be something wrong with your user profile, which is the most common reason for strange appearance and missing commands. Not installing the whole suite could be the other reason I'm aware of. [Tutorial] The OOo user profile. Close OOo, rename the profile folder, start again with a fresh setup, copy some of the files back (dictionaries, templates, macros, but not registry and packages) and redo your customizations in Tools>Options...
Faulty installations may occur under Windows if you did not test for the integrity of the downloaded package.

[Villeroy]

Let me guess: You are not using OpenOffice.org at all. This is why you get the #NAME error for the OOo specific STYLE function in column D, why you can't easily unprotect the sheet and why the "highlight values" feature is not availlable.

[onidarbe]

Villeroy, Let me first thank you again helping us all with your knowlege!
You don't need to bother anymore. seems I was still working under 2.4.1. Now your example works just fine with 3.0.1

Further more: I did experiment with those nice-looking pull downs from the validity-funtion, though they are not providing me a solution to my problem.

I'm trying to make me a sheet with besides the other text-values i need to see, shows me more then 30 date's that provide me with information when and if some action to a dossier has been taken or should be taken coloring those that are out of date. In this case all the steps we need to take to finish installing the kitchens we sell in the firm I'm working for. The software they provide us kitchen-sellers is terrible and we forget many things over the months it takes between selling it and getting it installed.
I can't use the Macro's because I may not use them from the firm-policy, seems they are scared to have viruses bean build in


|Name|Number|Sold-date|Planning-week|MeasurementAsked|MeasurementReceived|PaymentOne|FactoryOrder-date|FactoryDelivering-week|...

Because there are to many dates to make it clearly visible and synoptic, I thought on displaying only weeknumbers. But because sometimes I get a date and then a weeknumber I wanted to be able to type in the date and the other time just the weeknumber. The weeknumber should then be translated to the date on the in row2 set weekday within the timeperiod 4 week ago to the next future. Preferable showing me the exact date in the formulabar.

I was thinking on cell-format [<54][red]#;[>53][blue]ww but these don't show me the date of the week I provided, and it needs very big formula's in the conditional formating because I first need to find out if it's a number or a real date.
Then I thought about a pull-down showing me every 53 weeknumbers with the corresponding date on the weekday provided on row2. After picking one out I would see the weeknumber in the cell and the full date in the formula-bar just like I would use the cell-format WW though providing me a way to give in the weeknumber. The validity-funtion could not show a weeknumber with a date because the width of it is to short if the cell only is width enough to display the weeknumber.
So I still didn't find a solution for it