Relative References in OFFSET in a Conditional Formatting Formula

So, like many nerds, I am a little anal when it comes to making things work exactly like I want. I ran into a problem today in Excel that made me dig a little bit to find a different method than what is normally used. 

Here’s the problem. I don’t like to use gridlines in my excel sheets because they are ugly and can’t be changed in a way I like. It’s bad enough that turning them off doesn’t work for freeze panes. The trouble comes in when you use it for a log book. You either need to select X number of lines and rows and format them how you want ahead of time and have the look of gridlines being on, or you can do it one at a time, which is a pain in the ass.

So my plan was simple: Use Conditional Formatting to accomplish the task for me. It should have been simple, but it was not.

This is what I wanted the basic layout of the log to be. Date and time in light gray, activities and comments in white, all with a border. The idea here is that there needs to be an empty line, and people can see that they can type and not have to manually change the formatting. What I did was this:

Obviously, this is not good enough. This just made one cell automatically format AFTER a date was put in. The whole row needs to populate with the correct formatting. Not only that, but the next row needs to do the same thing.

Here is how it should look. When I type a date into the open date box and tab over to the time column, a new row is automatically created. Now, the method I chose was to format a cell if the row above it was not blank. That’s the first problem, you aren’t allowed to use a relative reference like SELF with offset, which is how you specify the cell above. So, the trick is to use the correct cell R1C1. For this, I made the conditional formatting formula for the Date column say the following:

=NOT(ISBLANK(OFFSET(A2,-1,0)))

That made it so the cell looks one position up, checks to see if it’s not blank, and then formats it how I want. Trouble is that I can’t use it for every column since there are two different formats. Plus, I didn’t want the next row popping up formatted one column at a time. So, in the next three columns on the 2nd row, I used this for the conditional formatting formula:

=NOT(ISBLANK(OFFSET(B2,-1,-1)))

=NOT(ISBLANK(OFFSET(C2,-1,-2)))

=NOT(ISBLANK(OFFSET(D2,-1,-3)))

This results in the whole next row populating correctly once an entry is made in the Date column. Now, you obviously can’t just click the corner and drag down to repeat the formula to the cells below, and that’s a pain in the ass anyway.

So, click on each cell in the 2nd row individually and manage the rule. In the “Applies to” column, change them to the following:

Tab!$A$2:$A$5000

Tab!$B$2:$B$5000

Tab!$C$2:$C$5000

Tab!$D$2:$D$5000

And that’s it. It works like it should. This shouldn’t be as big a headache as it is, but I got to use my brain for the first time in a while today.

For a single column without the headache, read this:

http://stackoverflow.com/questions/507253/excel-formula-to-reference-cell-to-the-left

Tags: excel

A few days ago, there was a link on Hackaday (http://phzero.net/?q=whistler) to a guy who hacked his CAN bus to allow a Whistler radar detector to display its info on his Mazda’s built-in screen. There is an incredible amount of work that went into it. I’d be satisfied with being able to put the detection status on the odometer LCD if I could actually pull something like this off. CAN bus hacking is becoming of great interest to me since realizing I could essentially control anything in the vehicle that I want with an iPad Mini mounted in the dash. There’s a great build log over at the F150 Forums (http://www.f150forum.com/f38/ipad-mini-dash-install-184699/) about installing an iPad Mini to look 99% stock in a 2009+ F150 Dash. A few members there were looking for help with doing this in an F150 Limited trim, but didn’t want to lose control of their HVAC and other systems which are built into the Navigation/Control screen module. An iPad app would really be the best way to go about this, but an arduino hosting a small web server may be able to pull it off as well. It would require a bluetooth to CAN bus adaptation that I’m not sure I’ve seen yet. There are arduino shields for both, so I’m sure it would only take a moderate amount of work to get it communicating correctly. This combined with Dash Command to display virtual gauges and other ECU info would be excellent.

One other thing. If I was to end up buying an early Bronco, I’d consider a no-hassle wiring harness. That means I wouldn’t be able to do any CAN Bus hacking at all unless I installed one myself. At that point, it would probably be easier just to use all i2c sensors. After all this stuff about vehicle attacks like the Prius that got completely owned, I figure a hard-wired custom implementation would be safer. Then again, I may just be overestimating my ability to properly secure it.

Why is there no tree?

So today I needed to do something that I’ve never had to do before on a Mac since I bought my first back in 2001. I needed to replicate the Windows “tree” command. Unfortunately, there didn’t seem to be an easy way to do it. To save you time, here’s what you need to have in a shell script to get it done.

__________________________________________

#!/bin/bash

find . -print | sed -e ‘s;[^/]*/;|____;g;s;____|; |;g’

__________________________________________

1. From terminal, do (sudo touch /usr/bin/tree)

2. (sudo nano /usr/bin/tree)

3. Paste those two lines into the nano editor and then hit control+X, you will be prompted to save the file, so type Y and then hit enter to save and close the script in nano.

4. do (sudo chmod o+x tree)

Now, just go into whatever directory you need in the terminal and type tree to get the recursive directory listing. You can also redirect the output into a text file with (tree > tree.txt)

Tags: terminal mac

I don’t know how to program yet. That is a problem.

The last post was a link to MIT OpenCourseware’s 600 course, Introduction to Computer Programming. It’s a very basic level programming course that is based on Python. Lucky for me, I downloaded it before I came out to Afghanistan. I started watching it today, and it is pretty good so far. Immediately I started thinking about how to achieve some things I want to do in Python and in the Arduino IDE as well. Once I am through it, I will post some code examples and solutions to problems I will have in designing this system. To say the least, my design so far is extremely complex for someone who doesn’t know how to program. I have done scripting before with KiX on Windows as well as AppleScript and Automator on the Mac. I have some experience with design in HTML and CSS, but none of that is enough to do anything near what I want to.

Further considerations

The automation project was originally planned to use Insteon devices like switches and outlets, etc. This was to be controlled by Indigo by Perceptive Automation for the Mac, hosted on a Mac Mini that would be on battery backup, either a pre-built or custom solution. Indigo Touch is an iOS native app that would allow me to control the system while connected at home or through a VPN. I mentioned in the last post that I wanted to use an old Pix 501 for the VPN end of it, but that plan is now changing to OpenVPN due to support for a higher key modulus and the fact that it’s Open Source Software. OpenVPN also has a native iOS app that allows this to happen, and I use TunnelBlick on the Mac for my VPN needs. Along with this will also be a firewall of some type. At first, it was of course the same Pix 501, but I am thinking about using pf or ipchains/iptables on an embedded linux system or raspberry pi for this. The pi falters in that it only has one ethernet jack, but another usb adapter could be used. There are plenty of linux machines now that are available for less than $100 that would suit the purpose well. I don’t need a shitload of throughput, so power isn’t too big an issue. Integrating arduino sensor packages would help tremendously as I can use XBee/Zigbee radios to do everything wirelessly and not have to use an ethernet or wifi shield to do it. The problem with that is security, however. Also on the list is Software Defined Radio and home security. Window and door monitors are easy to implement, but things like PIR sensors that detect movement and turn on CCTV style cameras for archiving are not as easy.

For control I have also just backed the Kreyos Smartwatch, which has an integrated microphone and speaker so I can send voice commands to Siri from my wrist. I have been waiting for a product like this for a long time. I considered trying to build something that could do the job, but obviously a smartwatch is always with you and serves multiple functions.

Next post will be about ideas I’ve had for moving home automation to my car. Sensors and remote control everywhere.

Tags: automation

The endless project

A while back, I had an idea for a simple home automation system that was remotely accessible through a VPN. Originally, it was to use a Cisco PIX 501 firewall with VPN capability which can be had on eBay for less than $10. Slowly it branched out and more intended features kept creeping in, creating a monster of an idea. I’ve bookmarked dozens of pages of DIY home automation solutions and software. Wading through all that information is now where I find myself. Turns out I’m not a very good project planner. Anyway, the following posts will be in reference to a home automation system being controlled by iOS apps, Siri, and old fashioned hardware.

Tags: automation