Powered by LiveJournal.com
You are viewing the most recent 10 entries
August 1st, 2011
Num Lock suddenly stops working on your Mac
It took me quite a bit of searching (and I never did find the answer online) - rather, it was when I accidentally held the '4' down, I noticed my mouse started to move to the left. That was my clue that perhaps Universal Access was to blame.
If you go to use your keypad, and suddenly find the numbers no longer work, it might not be a num lock issue. To test, try holding down the 4 or the 6. If the mouse moves left or right, this is fixed by simply tapping option 5 times in a row. Sounds stupid, but trust me. This is the command to turn on and off Universal Access.
Universal Access was something I made good use of when playing Life & Death, when you wanted the surgery to have perfectly straight diagonal lines. Just enable, and hold down 5 for clicking, and 3. The computer would move the mouse in a perfect -45 degree angle and the game would believe you had the most steady hands ever to hold a scalpel. I have no idea why it would be useful today - if you can use a keypad, why not just use your mouse? Perhaps if one's mouse died.. but who'd remember to tap option 5 times?
Anyway, I hope this helps someone else before they're driven insane trying to fix it.Tags: num lock numlock mac option keypad disab
July 2nd, 2011
In which I made Excel figure out if it is a leap year or not, and how to self correct
In today's example, I was asked to make a workbook with 12 sheets (one per month) with an entry for each day. I made the first cell 7/1/11 (the year starting date in this example) and every other cell simply was (cell above)+1. I then locked all the cells, leaving on the starting day unlocked. This enables the user to simply "save as" and change the first date and voila - the next year's spread sheet, she-is-a done. (Oh, I should mention that this DOES NOT allow you to pick a random month - you decide what your year will be and make the workbook accordingly. My first two sheets, for example, have 31 entries. I'm not making a dynamic workbook that you can start on any date - this is about only having to change the year and your yearly ledger/calendar/ect will automatically be correct.)
"Uh oh," you say. "What about February during a leap year? How can you make sure that March's spreadsheet starts on March 1st and not February 29th every four years?" I couldn't find an official method for doing this, so I used a "quick and dirty" method. Let me show you how it works.
(Sorry this will be long, but I want to ensure the Google-bot will eventually find it.)
We start at February's sheet, since you've already got January's finished. There's some stuff in the header, so the dates start at A5. A5's cell looks like this: =Jan!A35+1
This, of course, means it's looking at the date in January's sheet, 35th, which is January 31, 2012 and adding a day. So A5 on our February sheet will be February 1, 2012. A6 is A5+1, and so on all the way down to A32, which is February 28. Now, I want this to be self correcting, so there's no way I can use that +1 junk for the (possible) 29th of February. I skipped down to A37 (made the text white and protected the cell so it looks blank) and put this in: =A32+1
So, A37 is February 29, 2012 this upcoming year, but it's March 1, 2013 the year after. The user doesn't need to see this random date, which is why I've hidden it. So keep in mind, if it's a leap year, it's the 29th, and if it's not, it's the 1st.
Now we head back up to A33, the cell that would either be blank on a non-leap year, or Feb 29th on a leap year. I put in this formula: =IF(DAY(A37)=29,A32+1,"")
This is saying "If cell A37's day of the month is the 29th, this cell will be the previous cell's date plus one. Otherwise, make it blank." So now on leap years, this cell will have the proper date, Feb 29th, and on non leap years, it'll just be a blank cell.
"But wait," you say. "What about March? You still need to make sure the rest of the calendar is correct. How do you do that, smarty pants?"
I answer with this formula (in March's A5 cell): =IF(Feb!A33="",Feb!A32+1,Feb!A32+2)
What does that mean? Sheesh - learn to read formulas. Ok... it says "If the cell where Feb 29th goes is blank, look to the cell where the 28th is, and add one to it. Otherwise, look to the cell where the 28th goes and add 2."
Any questions? Be sure to leave a comment!Tags: excel leap year if statements day formul
October 20th, 2010
Microsoft Excel - faking column wrap
Again, posting this in case someone's going mad trying to figure out how to do a column wrap (or multiple columns) so instead of printing two half sheets, it'll print left and right on the same page.
This is especially useful if you're updating the lists, and sorting them. First thing to do is make your column(s). I was using A and B, with a user ID in the first, and the corresponding name in the second. I wanted to be able to add IDs any time, and resort the whole list so it was in ascending order.
A B C D
1 12 Fred
2 13 June
3 15 Larry
Let's say my page is so tiny, it can only fit two lines per page. I can easily copy and paste 15 Larry up to columns C and D.
A B C D
1 12 Fred 15 Larry
2 13 June
But what happens when I try to add 10 Sally? If I add her to the bottom of the list, then resort, I'll have to manually move 15 Larry down to 2C and D and manually add 13 June to 1C and D.
However, there's a nice way around this.
I start by putting all the entries into column A and B.
A B C D
1 10 Sally
2 12 Fred
3 13 June
4 15 Larry
Using the "Print Preview", I see how many lines will fit onto a single page. In this example, only two lines fit. Instead of moving June and Larry to the next set of columns, I put this formula into 1C. =IF(A3=0,"",A3). Into 1D, I put =IF(B3=0,"",B3). Then I can highlight both, and drag down.
Here's the important part - under View, select Page Break Preview, and make sure you deselect page 2. The information is still there, all in that first set of columns, but only the first page will print. The second set of columns duplicates the now hidden information. IDs and names can be added to the bottom of the list, and sorted, and both columns will resort.
Comment if you have any questions, and I'll do my best to explain.Tags: column wrap
, wrap column
, wrap column in excel
June 23rd, 2010
It took quite a while and some persistence, but I finally got Google Voice to verify my Whistle Phone number. It wouldn't ring on my iPad, but it did ring to the desktop application. No mic was hooked up for calls, but Google Voice just wants you to press two digits to verify. The landline and cell were simple - the VOIP phone, not so much.
So as of now, 1-XXX-81POUSH [I'd rather not post the area code where googlebot will find it] will ring my cell phone, my home phone, and my Whistle Phone on my computer, or my iPod, or my iPad. But just ask, and I'll let you know the area code. So now I can just give out that phone number... of course, now I have to actually memorize what digits go with POUSH for non-friends-and-family use.)
The great thing is now it doesn't matter if I switch cell phones - no one will be calling that cell directly anyway.
If I'm really ambitious, I could redo all my contacts and put my Google Voice number, some pauses, a 2 and more pauses, and then the person I want to call. This would make their caller ID display my Google Voice number instead of whatever device I happen to be using.Tags: 81poush
, google voice
, whistle phone
June 6th, 2010
Super Mario Bros. Crossover
This game might interest some of you guys... assuming anyone else actually reads this. I'm posting a direct link basically so I can right-click and save as to my computer. It seems to be the only way to save a flash file. (If it's .flv, however, you can (on your Mac) open Activities and double click it. Then it'll automatically download.)http://uploads.ungrounded.net/534000/534416_SmbcPreloader.swf
February 8th, 2010
Another Nice Little Excel Trick
Good day boys and girls!
Today we're going to learn about the Excel function called "Row()".
Have you ever wanted to reference another cell to find it later, but then had to insert or delete rows and suddenly your marker doesn't point where you wanted it to?
For example, let's say you have an entry in A1. Down in A10, you put the note "See row 1 for more details". But then you have to insert a row at the start, and your entry that had been in row 1 is now in row 2. A11's info will now point you to the wrong row for the info. (Trust me, this sort of thing has bugged me in an actual excel sheet for work.)
No worries! You know how you can say [ =A1 ] (of course, without the brackets) as your function, and it doesn't matter if you move A1 to another cell, that function will update itself. So we simply link the cell via a function so even if it moves, the reference will also change.
So, back to that entry you put in A10. Instead of putting the text "See row 1 for more details" you'd instead put ="See row "&ROW(A1)&" for more details". So it'll still say "See row 1 for more details" BUT should you insert a row at the very start, it'll change the formula to ="See row "&ROW(A2)&" for more details." so the output now reads "See row 2 for more details". This is a great way to be able to add notes about entries in other rows without worrying that subsequent changes will render your notes useless.
I thought it'd be easy to add in the COLUMN() function at the end, but it turns out it isn't easy at all. For example, in order for A1 to say "This is A1", you have to write this formula: ='This is "&SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),Row(A
Then the cell A1 will say "This is A1" and no matter where you move it, it'll always tell you what its reference is. You might never see a need for this ability, but it's still nice. You could also not have it reference itself. If you should put that same function in B1, then moved A1 to a random spot on the spreadsheet, B1 will tell you exactly where the old A1 went.
This is another in the "Thanks Google ... NOT!" series. Btw, it's not a slam against Google, but against other people who clearly know what they're doing in Excel, but don't share it in a way that's easy to find when searching in Google.
how do you link cells in excel?
how do you dynamically link cells excel?
insert row changes reference
function to find what row a cell is in
how do you make a cell show where it is?
If you should notice I'm missing some search stuff, feel free to add it in the comments. I think Google will see it all as one page, and that'll make stuff like this easier to find.
I love when websites try to figure out where you live. According to LiveJournal's autodetect Location, I'm in Sweden. My iPod believes that I'm in Texas. Although I suppose that's the correct time zone...
Tags: excel function dynamic link two cells
Current Music: Get Ready 2 Rokk by Freezepop
June 16th, 2009
I hate when Googling something turns up nothing
Nothing is more annoying than trying to figure out how to do something, and all Google can find are people mentioning that something without saying how it's done. It has happened to me in the past with regards to Excel, but recently it's Unreal Tournament Game of the Year Edition (GOTY). So I'm going to make a post and hopefully the Googlebot will eventually find this and questions will be answered.
For example - "Get the jump boots, but then manually disable them for the first jump." <- (semi-helpful, but would it have been that much more difficult to say HOW to disable them?!)
Jump boots give you three extra high jumps. If you want to save these and make a normal jump, just hold the walk button before jumping. Thanks for nothing, everyone else on the internet.
How do I command a bot?
"How do I command a bot?" unreal <- this quote doesn't appear anywhere on the internets, according to Google.... yet.
It's in the "voice commands" section - if you haven't messed with the preferences, just hold V. I'd recommend, on assault games, to order the bots to assault the base. That way they don't run away when you die. In Mazon, I'd recommend using "hold this position" in front of Chain 1. This way you can still defend the entrance even if Chain 2 is destroyed. It's really annoying for the bots to fall back and defend the front door and allow easy access through the cavern.
I hope I haven't forgotten other words someone might search for. Hopefully this'll save someone a headache trying to figure it out.
How do you manually disable jump boots?
How do you use jump boots?
Help jump boots
How do you deactivate jump boots?
save waste jumps jumpboots
to manually disable jump boots
to deactivate jump boots
to use jump boots
help with jump boots
How do I command a bot in unreal?
order bots on unreal
to order a bot
to command a bot
How do you control a bot in unreal
How do you control bots in unreal
How do you give orders to your bots unrealTags: bots
, jump boots
November 28th, 2008
My love/hate relationship with iTunes
I really have to rant just to get it out of my system.
Anyway, I started out using MacAmp, and thought it was pretty nifty. It played the couple of songs I had HD space for on my computer about 9 years ago. Then along came iTunes and completely changed the way I listened to music. I later convinced my wife to stop using WinAmp and switch over to iTunes.
Anyway, I'm using it to download podcasts these days, and I'm getting extremely frustrated with it - especially since I know that Apple is aware of this issue and chooses to do nothing about it.
It's the "We notice you're not actively listening to these podcasts, so we'll go ahead and stop downloading them for you" ... well, I'd call it a "bug" except it was works exactly how it was programmed. For some reason Apple decided that they knew best, and didn't even make it a preference you could turn off. That's my first peeve.
Second peeve - it's a memory hog. I like to have it open at all times for quick access to my music (and so it doesn't fail to download the latest podcasts - but that's my last peeve.) I've been finding it's just easier to only open it when I want to listen to something, and quit it when I'm done.
Third peeve - if I forget to open it for a few days and miss some podcasts, I can't easily add them back in. Sure, I can find the .mp3 and download it, but apparently for it to be recognized as an episode for that specific podcast, I'd have to do some fancy mp3 tag editing with a 3rd party program. It's really annoying.
That said, I'm not exactly holding my breath for a new version that addresses these issues.
March 31st, 2008
If anyone still reads this, and thinks maybe I've fallen off the planet, here's some quick info.
I just turned 27, Madeline's about 15 1/2 months old. I won't tell how old Misty is :) We still live in Grinnell. For pictures, http://flickr.com/photos/poushes/
. If you use AIM, look me up as Poushmeister. Email is email@example.com .
Macros in Excel
I've been helping my wife write some macros for her work, and during the process, have learned quite a bit. The two items I'm going to write about are things I had a hard time finding on Google, so at least the people who read my LJ won't have to worry - should they ever decide they need these two pieces of info.
The spreadsheet we need to work with is generated from Access once or twice a month, and is just raw data. Her work needs it nicely formated, and have it crunch numbers. The initial macro we were editing was created by someone else, and didn't quite work right. It must have been created using a similar spreadsheet, because some of the formulas were off a couple columns. That was an easy fix.
First multi-day item that made the macro snazzy: having the macro use a variable that's defined in the spreadsheet, based on how many rows the sheet has. I have to admit, I didn't create the idea to count how many rows the spreadsheet has.
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:="=Edited spreadsheet name!R11C1:R500C21"
ActiveWorkbook.Names.Add Name:="Publisher", RefersToR1C1:="=Edited spreadsheet name!R11C1"
ActiveWorkbook.Names.Add Name:="Criteria", RefersToR1C1:="=Edited spreadsheet name!R9C1:R10C1"
//// later in the macro
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=DCOUNT(Database,Publisher,Criteria)"
I don't really know how this works, just that it does. It currently makes E1 show 203. The original macro displays the number 203 in the stats area (specifically cell E1), but makes no use of the number. In a later section, a formula is defined in a specific cell, and then the next 500 cells are selected, and that formula is pasted in. So you can guess how ugly that looks. And what happens if they end up with 501 publishers in the spreadsheet? So I decided to make the macro dynamic, and to use that formula to define how many lines should be selected.
Long story short, the formula's very simple, but it took DAYS to figure out. Anyway:
Dim TotalLines As Integer
ActiveCell.FormulaR1C1 = "=DCOUNT(Database,Publisher,Criteria)"
TotalLines = ActiveCell.Value
I have no idea what Dim means.. but I'm assuming it's something that has to be done to declare a new variable. All the cool macros use it, anyway.
The last line there, MyVariable = ActiveCell.Value was the part that annoyed the heck out of me. See.. I couldn't just use the actual cell, or it'd try to enter the string "=DCOUNT(Database,Publisher,Criteria)", which doesn't exactly define an integer. Seriously, try googling how to have a macro's variable be defined by a function in the spreadsheet. Good luck.
*edit* I forgot to show how the variable is used later. So here goes:
ActiveCell.FormulaR1C1 = "=IF((RC[-2]<=TODAY()-14),""Y"",""N"")"
Range(Cells(13, 15), Cells(TotalLines, 15)).Select
Previously, it had Range("O13:O500).Select
Part two to follow. I haven't posted in 57 weeks, apparently, so writing's something I'll have to ease into. But basically my wife wanted me to have a macro set cell colors based on the color of a different cell. So if you want your highlighted cells in red, you set a specific cell to be red. If I want blue, I set that same cell blue. All in the name of making the two macros user friendly.
Oh right, the first macro (the original one) just starts the formating. Then the user gets to decide how many days old makes a file delinquent, and what color to highlight the offending cells. I could have made it one huge macro, but then it wouldn't be user friendly. And I'm all about user friendliness.
Tags: excel Current Mood:
, excel macro
, variable in macro