Amit Agarwal

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:

[caption id="attachment_959" align="alignnone" width="354" caption="indirect"][/caption]

That’s cool and easy.

2) Indirect reference with some constant values.

You can add constant and cell id with “&”

Again example:

[caption id="attachment_960" align="alignnone" width="187" caption="indirect with constant"][/caption]

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:

[caption id="attachment_961" align="alignnone" width="98" caption="copy paste"][/caption]

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

[caption id="attachment_962" align="alignnone" width="120" caption="copy and paste - 2"][/caption]

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:

[caption id="attachment_963" align="alignnone" width="168" caption="Drag Formulae"][/caption]

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

[caption id="attachment_964" align="alignnone" width="196" caption="Drag Formulae 2"][/caption]

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.

Openoffice.org and/or Excel useful tips. Openoffice.org and/or Excel useful tips.