| View previous topic :: View next topic |
| Author |
Message |
wibble
General User
Joined: 29 Aug 2007
Posts: 6
|
 Posted: Wed Aug 29, 2007 8:39 pm Post subject: Solved - Open Office equivalent to "Goto" |
 |
|
In the 'Edit' menu of Excel there is a 'Goto' command (I used it so automatically that I'm not 100% sure of the command name, but it was in the 'find & replace' group) which lets you select cells according to certain criteria.
I always used this command to find cells containing formulae and consequently accentuate in red, whilst developing my spreadsheets.
Can anybody please tell me if this is possible in OO? i.e. selecting all the cells on a sheet which contain formulae.
Many many thanks.
Last edited by wibble on Thu Aug 30, 2007 4:50 pm; edited 1 time in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
wibble
General User
Joined: 29 Aug 2007
Posts: 6
|
| Posted: Thu Aug 30, 2007 1:35 am Post subject: |
|
|
Villeroy please forgive me but which file?
I love macros and will go back to that page for a couple of things though. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
wibble
General User
Joined: 29 Aug 2007
Posts: 6
|
| Posted: Thu Aug 30, 2007 2:01 am Post subject: |
|
|
| Villeroy, I'm sorry man. I got the file ok but in the version of OO I am using, I don't have any Tools>Extensions... to be able to add downloaded file |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Aug 30, 2007 2:25 am Post subject: |
|
|
| So your version is ... which one? The menu item may be "Tools>Packages". Makes no difference. The zip contains a file with more detailed instructions. |
|
| Back to top |
|
|
wibble
General User
Joined: 29 Aug 2007
Posts: 6
|
| Posted: Thu Aug 30, 2007 2:23 pm Post subject: |
|
|
All right Sir!
Thank you, I finally got everything sorted. It was very late last night when I posted the question and I eventually flaked out on you, sorry for that.
So this is the OO way of doing things. Very interesting.
Where might one search for other such macros? |
|
| Back to top |
|
|
TerryE
Super User
Joined: 16 Jul 2006
Posts: 550
Location: UK
|
| Posted: Thu Aug 30, 2007 2:50 pm Post subject: |
|
|
The shortcut for Goto in Excel is F5. F5 in Calc takes you to Navigator — which basically does everything that Excel Goto does but with a different name.
In Excel, the range selection box (immediately above and to the left of cell A1 can be used to type in a cell reference or range and Excel will take you there — just the same box and function in Calc.
Have I just missed the point of what?
Oops: quick edit — missed the point about searching for formulae, which navigator doesn't do. However OOo supports Regexp searches and searching for ^= does the trick here. //T
_________________
Terry
WinXPSP3, OOo 2.4.1, Ubunto 8.04 for development
Also try the Official OOo Community Forum where I mainly post now. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Aug 30, 2007 4:01 pm Post subject: |
|
|
| TerryE wrote: | The shortcut for Goto in Excel is F5. F5 in Calc takes you to Navigator — which basically does everything that Excel Goto does but with a different name.
In Excel, the range selection box (immediately above and to the left of cell A1 can be used to type in a cell reference or range and Excel will take you there — just the same box and function in Calc.
Have I just missed the point of what?
Oops: quick edit — missed the point about searching for formulae, which navigator doesn't do. However OOo supports Regexp searches and searching for ^= does the trick here. //T |
The "SpecialCells" add-on has much more to offer than Excel's "go to special cells":
Dialogue "Contents" can select combinations of cell types:
blank ranges
const. numbers
const. text
const. date/time
formula results number
formula results text
formula results error
cells with annotation
visible cells
For instance, you can select all cells having some number (constant or formula result) and/or annotation and/or beeing visible.
Dialogue "Formatting" selects uniquely formatted ranges, grouped or sequential.
You can not only select ranges, you can also navigate. Both dialogues provide list boxes with addresses of all detected ranges of some type. When you pick an address, the current sheet is scrolled to the range in question.
The add-on is useful if you need to export consistant data to csv or database. It can help you to find inconsistant or bloated formatting (thousands of differently formatted ranges)
Things in Excel's "go to special cells", not in add-on "SpecialCells":
Select dependents/precedents. The corresponding API-call disregards references to other sheets. It's pointless.
Select column/row-differences. The corresponding API-call disregards relative references. So =A1 in B1 is different from =A2 in B2. It's pointless. |
|
| Back to top |
|
|
wibble
General User
Joined: 29 Aug 2007
Posts: 6
|
| Posted: Thu Aug 30, 2007 4:45 pm Post subject: |
|
|
Very superior function ......... absolutely.
Nice to be able to combine criteria.
Thanks again Villeroy.   |
|
| Back to top |
|
|
|
