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:

That\’s cool and easy.

2) Indirect reference with some constant values.

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

Again example:

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:

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

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:

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

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.

## 2 thoughts to “Openoffice.org and/or Excel useful tips.”

You must log in to post a comment.