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:

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0022.jpe"><img class="size-full wp-image-959" title="indirect" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0022.jpe" alt="indirect" width="354" height="98" />

That&#8217;s cool and easy.

2. Indirect reference with some constant values.

You can add constant and cell id with &#8221;&&#8221;

Again example:

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0032.jpe"><img class="size-full wp-image-960" title="indirect with constant" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0032.jpe" alt="indirect with constant" width="187" height="92" />

3. Data Validity.

Though I knew this but needed something to put on the post. I am quite lazy in writing so here&#8217;s something that is already available elsewhere. <a href="Herehttp://openoffice.blogs.com/openoffice/2007/08/creating-a-drop.html" target="_blank">Here.

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

Example again:

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

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0041.jpe"><img class="size-full wp-image-961" title="copy paste" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0041.jpe" alt="copy paste" width="98" height="117" />

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

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_005.jpe"><img class="size-full wp-image-962" title="copy and paste - 2" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_005.jpe" alt="copy and paste - 2" width="120" height="118" />

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

Example:

I enter &#8221;=INDIRECT(A1)&#8221; in C1 and then drag the same below and select formulae and here is the result:

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0071.jpe"><img class="size-full wp-image-963" title="Drag Formulae" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0071.jpe" alt="Drag Formulae" width="168" height="186" />

enter &#8221;=INDIRECT($A$1)&#8221; in C1 and then drag the same below and select formulae and here is the result:

<a href="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0081.jpe"><img class="size-full wp-image-964" title="Drag Formulae 2" src="http://blog.amit-agarwal.co.in/wp-content/uploads/2010/08/screenshot_0081.jpe" alt="Drag Formulae 2" width="196" height="192" />

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 <a href="herehttp://user.services.openoffice.org/en/forum/viewtopic.php?f=9&p=68895" target="_blank">here and provided by Villeroy.