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 \”&\”
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.
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.
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:
Pick your spreadsheet document and hit [New…]
Add the following code tho the new Basic module.
CODE: SELECT ALL EXPAND VIEW
SHEETLIST = ThisComponent.Sheets.getElementNames()
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.