Worksheet names in Excel Cells
If you are working in Excel, and you want to show the worksheet name in a Cell on that worksheet, you can use the CELL function to do so.
By default the CELL function will return the current document name, if used with the filename info_type:
=CELL("filename")
This provides a full path to the spreadsheet, with the worksheet of the current Cell at the end, e.g:
C:\folder\[myfile.xls]Sheet1
You can easily get just the worksheet name by using the FIND and MID functions to do the hard work. You need to find the location of the last square bracket, and find achieves this as shown:
=FIND("]",CELL("filename"))
This would return the position of the last bracket. In this case it is at position 22 of the text that CELL(”filename”) returns. The MID function can extract text starting at a location for n length, where n is an arbitrary number. So we would combine MID, FIND and CELL functions to return just the worksheet name like this:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
The reason we add a +1 is because we want to start extracting the text one character AFTER the right square bracket, e.g. at the start of the Worksheet name. Our result is:
Sheet1
Worksheet names from another Worksheet
So far so good, and how is this any different than any other blog post or forum post on the net explaining this? So far it’s not, but here comes the fun part.
What if you have multiple Worksheets, and you do this:
- Have a cell with content, Sheet1!B2
- Sheet1!B2 displays the content of OtherSheet!H5, i.e.:
=OtherSheet!H5
- You want Sheet1!B1 to display the worksheet name where the CONTENT of Sheet1!B2 comes from.
You could try using the MID/FIND/CELL function combination to try this. In Sheet1!B1 you would enter:
=MID(CELL("filename",B2),FIND("]",CELL("filename",B2))+1,255)
However this would yield the worksheet name of B2 itself, not the worksheet where you are taking your content from:
Sheet1
Not what we wanted. Somehow you need to get the Value of the formula used =OtherSheet!H5 and look up the worksheet name for OtherSheet!H5
The Solution
To do this you ware going to need to do two things:
- Make a new function to display the formula, sans the equal sign
- Make your CELL function use the result of your function to lookup the filename info_type
We can use the Excel VB Editor to create a new function, and call it GetLocation:
Function GetLocation(Cell As Range) As String
GetLocation = Mid(Cell.Formula, 2)
End Function
But we can’t just use GetLocation to directly feed the CELL function. We need to use another handy function INDIRECT. This allows us to return the result of the GetLocation function as a Reference. This then allows the CELL function to evaluate the filename/Worksheet details for the destination cell in the other worksheet:
=MID(CELL("filename",INDIRECT(GetLocation(B2))),FIND("]",CELL("filename",INDIRECT(GetLocation(B2))))+1,256)
This now provides the Worksheet name of the cell that Sheet1!B1 is using to get it’s content from which is OtherSheet!H5:
OtherSheet
This is very handy when you need to show on a master worksheet which other worksheet your data is actually coming from. Windows Excel only, not Mac I’m afraid – until they bring back VB. Enjoy!
September 23, 2009 at 18:00 · Filed under apps, unix
If you use Unix, and need to migrate your Business objects CMS from one database to another database, you will probably use the cmsdbsetup.sh script. This script migrates and manages your database connection in a Unix environment using Business Objects Enterprise (BOE).
In my case I am Using Solaris 9, and have Oracle 10g databases and client files for use by BOE.
When running the cmsdbsetup.sh script you get the following error pertaining to clntsh:
Business Objects
Current CMS Data Source: DBNAME
err: Error: Failed to get cluster name.
err: Error description: Unable to load clntsh
select (Select a Data Source)
reinitialize (Recreate the current Data Source)
copy (Copy data from another Data Source)
changecluster (Change current cluster name)
selectaudit (Select an Auditing Data Source)
[select(6)/reinitialize(5)/copy(4)/changecluster(3)/selectaudit(2)/back(1)/quit(0)]
----------------------------------------------------------
This error “Unable to load clntsh” refers to the libclntsh.so library used by the Oracle client. Since BOE runs as 32bit, the 32bit Oracle client libraries should be accessible by the user running BOE.
If you are running a 64 bit Unix and a 64bit Oracle install check that the environment for the user running BOE (user that will run the CMS) has the 32bit libraries in the path:
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
Then check that either the user is a member of the Oracle dba Unix group or everyone has permissions to access the 32bit libraries under Oracle 10g:
su - oracle
chmod o+rx $ORACLE_HOME/lib32/*
Feel free to leave any comments if you need help with this.
Apple released Bootcamp 2.1 with OS 10.5, which allows you to dual boot to Windows XP/Vista on your Mac.
Subsequenet to that release, there was a driver update for the Multitouch trackpad which was suppose to improve souble tapping, etc. However it caused a Blue Screen of Death (BSOD) the moment you double tapped, courtesy of applemtp.sys.
Many people sent suggestions to Apple to fix this, but it seemed to be falling on deaf ears.
Little did we know that Apple were releasing a new version of the touchpad driver included with Bootcamp 3.0 on the Snow Leopard install DVD.
I’ve updated my Bootcamp to 3.0, and indeed the applemtp.sys driver version has increased to 2.1.2.112. The FAQ for the Snow Leopard update states that Bootcamp 3.0 has:
“Improved tap-to-click support – The ability to tap the track pad to click the mouse button is now supported on all Mac portables that run Boot Camp.”
In my tests, so far, there has been no more crashes. I get to use double finger right click tap, and the track pad responsiveness that I got in the older buggy driver is back as well.
Thanks Apple. No more BSOD.
This is the same issue I’ve posted on a couple of forums. It’s seemed detailed enough to be a blog post, so here it is. Under 10.5.8 (Leopard) I was successfully using my Merlin XU870 ExpressCard (supplied by ISP) with Three Broadband (3) in Australia. I did not use the 3 drivers, but used the Leopard WWAN built-in to connect. There was never an issue as Leopard detected the card as a “Novatel Wireless HSDPA Modem”, and I configured the Network Advanced settings for 3 postpaid services as:
- Vendor: Novatel Wireless Inc.
- Model: GSM
- APN: 3netaccess
- CID: 1
I installed Snow Leopard as an Upgrade (not a fresh install) after having a CCC backup and a Time machine backup in place.
When I plugged in my 3 expresscard, 10.6 detects the card, initialises and populates the WWAN icon with “Telstra 3G: Not Configured”.
It detects the card as a “Novatel Wireless HSDPA Modem”, and automatically configures the Network Advanced settings of the card, as shown:
The question is: Why is the APN telstra.datapack setup as default?
I am able to change the APN to 3netaccess, which results in a successful connection to 3:
Wed Sep 2 15:38:25 2009 : Initializing phone: ATE0V1&F&D2&C1S0=0
Wed Sep 2 15:38:25 2009 : Initializing PDP context: AT+CGDCONT=1,"IP","3netaccess"
Wed Sep 2 15:38:25 2009 : Initializing with secondary command: AT$NWPDN=0
Wed Sep 2 15:38:25 2009 : Dialing: ATD*99***1
Wed Sep 2 15:38:25 2009 : Waiting for connection
Wed Sep 2 15:38:25 2009 : Connection established
Wed Sep 2 15:38:28 2009 : Serial connection established.
Wed Sep 2 15:38:28 2009 : Using interface ppp0
Wed Sep 2 15:38:28 2009 : Connect: ppp0 <--> /dev/cu.wwan
Wed Sep 2 15:38:32 2009 : Could not determine remote IP address: defaulting to 10.64.***.***
Wed Sep 2 15:38:32 2009 : local IP address 115.130.***.***
Wed Sep 2 15:38:32 2009 : remote IP address 10.64.***.***
Wed Sep 2 15:38:32 2009 : primary DNS address 202.124.68.182
Wed Sep 2 15:38:32 2009 : secondary DNS address 202.124.65.22
The annoying thing is the WWAN menu still shows Telstra when connected.
Looking at the WWAN system file:
cd /System/Library/Extensions/IOSerialFamily.kext/\
Contents/PlugIns/AppleWWANSupport.kext/Contents/\
Resources/
less countryCodes.plist
it has only Telstra, Optus and Vodafone carriers listed. I think this could be the cause of the default APN being populated in the Advanced settings. I can’t find where the “Telstra 3G” in the WWAN menu item is coming from though.
I’d appreciate if anyone can give their thoughts on how to fix this.
This is a cheat sheet on getting PHP5 on OpenBSD to have zip support. I needed this to get CiviCRM to work with Joomla.
First off install some require packages, including the zziplib package:
export PKG_PATH=http://mirror.aarnet.edu.au/pub/OpenBSD/4.5/packages/i386/
pkg_add -v zziplib
pkg_add -v autoconf-2.62
Now download and extract the PECL zip package:
mkdir /usr/local/src/
cd /usr/local/src/
wget http://pecl.php.net/get/zip
tar zxvf zip
cd zip-1.10.2/
Compile PECL zip, making sure you set your correct autoconf to use:
export AUTOCONF_VERSION=2.62
phpize
./configure
make
make install
Finally setup php5 and restart httpd:
cat << EOF >> /var/www/conf/php.ini
extension=zip.so
EOF
sudo apachectl stop
sudo apachectl start
Of course, this will go stale over time as new releases and versions come out, so YMMV.
Next entries »
