Openoffice.org and/or Excel useful tips.

Today I was working on Excel sheets and had to do quite a lot of manipulations. Here are some of those:

1) Find the value of the cell whose number is stored in another cell.

You can use the indirect function.

Here is example:

\"indirect\"

That\’s cool and easy.

2) Indirect reference with some constant values.

You can add constant and cell id with \”&\”

Again example:

\"indirect

3) Data Validity.

Though I knew this but needed something to put on the post. I am quite lazy in writing so here\’s something that is already available elsewhere. Here.

4) Avoid increment in cell id when doing copy paste.

Example again:

First I inserted \”=a1\” in C1 and copied the same. Then selected C2 through C4 and did paste. Here is what will happen:

\"copy

Now I inserted \”=$a$1\” in C1 and copied the same. Then selected C2 through C4 and did paste. Here is what will happen:

\"copy

Why would you want that.. your choice, I need in case of indirect function and for dragging the cells down.

Example:

I enter \”=INDIRECT(A1)\” in C1 and then drag the same below and select formulae and here is the result:

\"Drag

enter \”=INDIRECT($A$1)\” in C1 and then drag the same below and select formulae and here is the result:

\"Drag

See the difference.

And the last trick is to insert all the sheet names in the current sheet:

Hit Alt+F11
Pick your spreadsheet document and hit [New…]
Add the following code tho the new Basic module.
CODE: SELECT ALL EXPAND VIEW

Function SHEETLIST()
SHEETLIST = ThisComponent.Sheets.getElementNames()
End Function

Array function =SHEETLIST() [Ctrl+Shift+Enter] puts a horizontal list of sheet names.
=TRANSPOSE(SHEETLIST()) does the trick vertically.

The above is taken from here and provided by Villeroy.

About Amit Agarwal

2 Trackbacks on “Openoffice.org and/or Excel useful tips.”