Random navigation between cells in *MY* order

Hi Dean

I would love to know how to do this as well. What I can say, is when the xls file is saved as an ods, the tab order is retained.

Tony

Hi :)

Surely this sort of thing is easier and more stable through a database program which should allow much better placement of the various elements on the form. Spreadsheets are very restrictive about this sort of thing. Fortunately most database programs can read/write to data stored in spreadsheets but it might be better to import all the data into a database too, because it keeps the data safer.

The Windows Office "Access" is somewhat quirky but the OpenOffice "Base" conforms to SQL standards much more readily and is almost user-friendly enough to be quite considered only slightly unfriendly. Well worth getting to grips with.

Good luck and regards from
Tom :)

Hi again :)

In linux-land we tend to be able to read most formats so posting that as non-ods was not a problem.

It opened & worked in OpenOffice just fine. Which suggests 1 'obvious' work-around the 3 of us hadn't considered which is to setup the form in excel and open it in OpenOffice but keep saving the spreadsheet 'workbook' (ie all the tabs) as a ".xls"

I must admit i tend to go 1 step further and switch all the defaults for OpenOffice to the much less safe MS standards. The only advantage of sticking with OpenOffice standards is security, lack of worry about documents getting compromised by viruses/malware & peace-of-mind. However, to communicate with Windows users it really needs you to use the many times proven unsafe formats that they like to use.

Note that many people & companies make a lot of money from solving security issues in Windows-land. Switching to formats that are tooo difficult to compromise would significantly reduce their earnings.

Since we are using linux even using the unsafe formats is probably still safe for us and it is unlikely that documents would become infected from our end. So documents created on a Windows system may well become infected but that would be extremely unlikely to harm or propagate on a linux machine. At worst we might pass on an infection to someone else unwittingly, but only through a document that had been infected by someone else on a Windows machine before they sent it to anyone else.

To switch the default format to MicroSquish standards go up to the menu bar and click on

Tools - Options

in the pop-up box click on the little + sign beside "+Load/Save", then "General". At the bottom are 2 drop-down lists. With the first on "Text document" change the second drop-down back up the list 1 place to "Microsoft Word 97/2000/Xp" change the 1st drop-down to "Speadsheet" and the 2nd drop-down back up 2 places to "Microsoft Excel 97/2000/Xp". Then same again for "Presentation". The important thing to notice is that you do NOT want to change the format to anything that has "Template" in the line unless you want to create real problems for yourself later (or in certain unique and highly unlikely circumstances when you really know what you are doing and rarely use Office at all)

Back to the Load/Save thing on the left hand side. Click on VBA properties and tick them all. Again with Microsoft Office page, just tick them all.

Errr i assume you have already installed "msttcorefonts" so that you don't confuse Windows users by using decent fonts too.

Good luck and regards from
Tom :)

The issue for me is that the data being entered is done so to check the figures, not to store the data. It's a worksheet that is used many times per day and needs to have a specific order. Access offers good navigation, but it's flexibility in math issues has never been good. The formulae are not hard in a spreadsheet, but YOY in Access are a pain.

Databases are good at manipulating format, but spreadsheets are still the winner on math.

So I'm still trying to find the way to get cell to cell in MY order, not the linear order that is the norm. I cannot redesign the source of the data, so I have to find a way to make this work, even if I have to do it in Excel.

OK - here is a starting point (i.e I have only done a tiny bit of research)

Go to View > Toolbars and select Form Controls. This lets you put Text Boxes (or other sorts of Controls, if they are of use) onto the spreadsheet. If you right click a control (with Design Mode On, of course) and select Control to get its properties dialogue, you will see that you can designate a Tab Order on the General tab. On the Data tab, you can designate a Linked Cell. Any entry you make in the Control updates the linked cell. Got to Help and look for Linked Cells in the Calc index to get full details of how different Controls and their Linked Cells interact.

This is as far as I have got. I assume that you will be able to do Math in the linked cells and show results in the controls.

I hope this helps. You say that you cannot redesign the source of your data, but I don't quite know what you mean by that, so this might all br a blind alley.

Tony

Hi :)

Has this question been resolved yet? I am just curious if it worked

Regards from
Tom :)