![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
plonqOne of the guys back at the head office sent out a link to the spreadsheet showing early-shift duty roster for the next couple of months. I was sifting through it, trying to spot my name on the crowded calendar and thinking, "this would be way easier if my name was highlighted." I selected the whole sheet and was about to do a conditional format when it occurred to me that it would be easier for everyone else if their name was highlighted as well.
It would have been very easy to do in VBA because I knew where to find the system environment variable that stored the name of the person who was signed in. I wanted it to be seamless for the others though. If they suddenly had to start clicking through requesters asking them to enable VBA, it would have triggered alarms with them. I figured that somewhere in amongst all of its hooks, Excel must have a way of telling you who was logged into the computer on which it was running.
After a few minutes of digging, I stumbled onto =INFO("directory"), which returned "C:\Documents and Settings\{redacted}\My Documents\". Perfect! I set up a little translation table matching up the home directories with the names, and then used a VLOOKUP() against what INFO() gave me to get the active name from the list. Finally I did a conditional format on the sheet based on the contents of that cell. I saved the spreadsheet back out to the network share and gave the matter no more thought, other than idly wondering how long it would take anyone to notice my little change.
A couple of hours later I got an IM from the guy who had created the spreadsheet...
By the time he contacted me, they had pretty much figured out that it was something that I had done. He had opened the spreadsheet to make a small change in the roster, and immediately noticed that his name was showing up as bright orange all through the spreadsheet. His initial thought was that he had acquired a stalker, so he called one of the other guys in the department and said, "Hey, when you were in the spreadsheet earlier today, you didn't notice if my name was all done up in orange, did you?" The other guy hadn't noticed anything amiss when he had been in there first thing this morning, so he called it up at his own desk for a look. The moment he did so, the two of them quickly realized that there were some shenanigans afoot.
I knew that they would eventually trace it back to me, but I was impressed by how quickly they did.
It would have been very easy to do in VBA because I knew where to find the system environment variable that stored the name of the person who was signed in. I wanted it to be seamless for the others though. If they suddenly had to start clicking through requesters asking them to enable VBA, it would have triggered alarms with them. I figured that somewhere in amongst all of its hooks, Excel must have a way of telling you who was logged into the computer on which it was running.
After a few minutes of digging, I stumbled onto =INFO("directory"), which returned "C:\Documents and Settings\{redacted}\My Documents\". Perfect! I set up a little translation table matching up the home directories with the names, and then used a VLOOKUP() against what INFO() gave me to get the active name from the list. Finally I did a conditional format on the sheet based on the contents of that cell. I saved the spreadsheet back out to the network share and gave the matter no more thought, other than idly wondering how long it would take anyone to notice my little change.
A couple of hours later I got an IM from the guy who had created the spreadsheet...
By the time he contacted me, they had pretty much figured out that it was something that I had done. He had opened the spreadsheet to make a small change in the roster, and immediately noticed that his name was showing up as bright orange all through the spreadsheet. His initial thought was that he had acquired a stalker, so he called one of the other guys in the department and said, "Hey, when you were in the spreadsheet earlier today, you didn't notice if my name was all done up in orange, did you?" The other guy hadn't noticed anything amiss when he had been in there first thing this morning, so he called it up at his own desk for a look. The moment he did so, the two of them quickly realized that there were some shenanigans afoot.
I knew that they would eventually trace it back to me, but I was impressed by how quickly they did.
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2009-04-30 09:35 pm (UTC)no subject
Date: 2009-04-30 10:22 pm (UTC)I was poking around trying to find an alternate way to do it when I found the INFO() function. Naturally that one will break if somebody changes their default folder, but none of the people in the list of names struck me as anyone who would bother.
no subject
Date: 2009-04-30 10:27 pm (UTC)Because no one here would change their stuff either :)
no subject
Date: 2009-04-30 10:56 pm (UTC)no subject
Date: 2009-04-30 11:03 pm (UTC)no subject
Date: 2009-05-01 02:56 am (UTC)no subject
Date: 2009-05-01 07:58 am (UTC)You don't get in trouble or anything, do you? I could imagine doing something like that, myself, if I get an office job, but I'd like to know beforehand how it's taken in different workplaces.
no subject
Date: 2009-05-01 12:57 pm (UTC)no subject
Date: 2009-05-01 01:00 pm (UTC)The problem I have at work is that the demand for reports largely outstrips the normal capability of the tools we have, so I spend a good part of my day figuring out how to coax more out of these programs without actually breaking them.