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.

bash script with sql to get the number of records from multiple tables.

Here is the bash script:

#!/bin/sh
names[1]=errorlog
names[2]=amit1log
names[3]=amit2log
names[4]=amit3log
names[5]=amit4log
j=1
echo $1
for i in $( sqlplus amit/passwd@tns @get_count.sql |sed  -n \’/COUNT/,/Disconnected/p\’|sed \’/COUNT/ d\’|sed \’/—/ d\’|sed \’/Disconnected/ d\’|tr \’n\’ \’ \’  )
do
temp=${names[$j]}
let count=30-${#temp}
for ((I=1; I <= $count ; I++))
do
printf \” \”
done
echo  \”${names[$j]}    : $i\”
# echo $j
let j=j+1
done
echo
echo

and the required sql script:

select count(*) from errorlog;
select count(*) from amit1log;
select count(*) from amit2log;
select count(*) from amit3log;
select count(*) from amit4log;
quit;

I have verified this to be working on Solaris and Oracle. Might need some changes for mysql, but should not be much.

\"\"

Built-in lists in vim

Vimscript provides excellent support for operating on collections of data, a cornerstone of programming. In this third article in the series, learn how to use Vimscript\’s built-in lists to ease everyday operations such as reformatting lists, filtering sequences of filenames, and sorting sets of line numbers. You\’ll also walk through examples that demonstrate the power of lists to extend and enhance two common uses of Vim: creating a user-defined function to align assignment operators, and improving the built-in text completions mechanism.

URL: http://www.ibm.com/developerworks/linux/library/l-vim-script-3/index.html?ca=drs-

\"Reblog