Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Actuarial Jobs by State

New York  New Jersey  Connecticut  Massachusetts 
California  Florida  Texas  Illinois  Colorado


Reply
 
Thread Tools Display Modes
  #41  
Old 05-01-2009, 11:54 AM
C8 Guy C8 Guy is offline
Member
 
Join Date: Oct 2001
Posts: 1,160
Default

Quote:
Originally Posted by Phil View Post
Similarly to hiding / unhiding rows, select some rows (or a range of cells over more than one row) and try
SHIFT ALT RightArrow

This "groups" the rows (puts a little [-] sign in the left margin; click it to roll it up and hide them.)

I prefer this method to ordinary "hide rows" because the [+] sign tells you that there are hidden rows lurking. Without that, it can be hard to notice the gap in row numbers or the one-pixel-thicker separator.

To ungroup the rows ("unhide"), use SHIFT ALT LeftArrow.
This appears to work with only rows or columns that are adjacent. Are there any slick ways to hide/unhide several columns that are not adjacent?
Reply With Quote
  #42  
Old 05-11-2009, 04:41 PM
BG5150's Avatar
BG5150 BG5150 is offline
Member
Non-Actuary
 
Join Date: Jan 2009
Favorite beer: the one you're buying me
Posts: 19,255
Default

Is there any shortcut to make a cell reference an anchor cell?

For example, I have 500 in A1.

In my formula, I have "+A1 * C1" I'd like the A1 to read $A$1. Is there a way, when I click A1 to add it to my formula to make it that way? It becomes a pain to create my formula, hit F2, edit it and then copy it.
Reply With Quote
  #43  
Old 05-11-2009, 04:45 PM
Ron Weasley's Avatar
Ron Weasley Ron Weasley is offline
Member
CAS AAA
 
Join Date: Oct 2001
Studying for naught.
Favorite beer: Butterbeer
Posts: 8,540
Default

Press F4
Reply With Quote
  #44  
Old 05-11-2009, 05:06 PM
BG5150's Avatar
BG5150 BG5150 is offline
Member
Non-Actuary
 
Join Date: Jan 2009
Favorite beer: the one you're buying me
Posts: 19,255
Default

Quote:
Originally Posted by Ron Weasley View Post
Press F4
That doesn't help me with the original formula.

I Have a number in A1. I also have a list of other numbers in A2, A3, A4...

I need to multiply A1 * A2 and put it into B2, A1*A3 in B3, A1*A4 in B4, etc.

I also have a number in D1 and I need to multiply it by D2, D3, D4, etc and put the result next to it.

So, my first formula, in B2, I hit "+", click A1, *, click A2, enter. Then F2, edit the A1 to $A$1. Then copy the formula down all the rows.

I do the same thing for the second formula with D1.

Is there anyway to, when I click A1, to make it $A$1 in my formula?
Reply With Quote
  #45  
Old 05-11-2009, 05:11 PM
Ron Weasley's Avatar
Ron Weasley Ron Weasley is offline
Member
CAS AAA
 
Join Date: Oct 2001
Studying for naught.
Favorite beer: Butterbeer
Posts: 8,540
Default

Quote:
Originally Posted by BG5150 View Post
So, my first formula, in B2, I hit "+", click A1, click F4, *, click A2, enter.
Does this help?
Reply With Quote
  #46  
Old 05-12-2009, 10:38 AM
BG5150's Avatar
BG5150 BG5150 is offline
Member
Non-Actuary
 
Join Date: Jan 2009
Favorite beer: the one you're buying me
Posts: 19,255
Default

Quote:
Originally Posted by Ron Weasley View Post
Does this help?
Worked perfectly.

I've only ever used F4 as a repeat command.

Thanks!
Reply With Quote
  #47  
Old 05-12-2009, 02:56 PM
E's Avatar
E E is online now
Eddie Smith
SOA AAA
 
Join Date: May 2003
College: UGA
Posts: 8,817
Default

Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.
__________________


Learn how FSA exams are different from the prelims

Registration is open for all of our spring 2018 FSA exam online seminars:

ILA-LP | ILA-LFV-U | ILA-LFV-C | ILA-LRM | QFI Core | QFI Adv | QFI IRM | G&H Core | G&H Adv | G&H Specialty

Check out our Technical Skills Course: Excel, VBA, Access, R, and more!

Follow us on Twitter, Facebook, and LinkedIn
Reply With Quote
  #48  
Old 06-04-2009, 09:12 AM
Noddy's Avatar
Noddy Noddy is offline
Member
 
Join Date: May 2007
Posts: 2,450
Default

Quote:
Originally Posted by E View Post
Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.
This made me think to look at the ALT key way to do it and 'alt+dff' works a treat. Thanks!

(ie. works when active cell is anywhere withtin a contiguous range and it toggles. I didnt realise you didn't have to select the column headers)
Reply With Quote
  #49  
Old 06-04-2009, 11:45 AM
GatesIsAntichrist's Avatar
GatesIsAntichrist GatesIsAntichrist is offline
Member
SOA AAA
 
Join Date: Mar 2005
Location: Hell (on temporary assignment on Earth)
Favorite beer: New(fave)! Black'n'Tan
Posts: 206
Default

Quote:
Originally Posted by Noddy View Post
'alt+dff' works a treat. Thanks!
and 'alt+dfa Enter' for advanced, while sitting anywhere in the criteria range or database range. (Advanced filtering is not that difficult and has some fine advantages, though often eschewed. It's easier when you define range Criteria and Database. http://www.contextures.com/xladvfilter01.html is good primer. Add additional keystrokes for the garbage-interfaced XL2007.)

If it hasn't been mentioned already, with autofiltering you can nicely capitalize on alt-down arrow and first letter recognition. [Edit: I just checked: first letter recognition seems to have been assassinated in the 2007 interface. Those j/o's.]

Last edited by GatesIsAntichrist; 06-04-2009 at 11:50 AM.. Reason: correction for Excel version difference in results
Reply With Quote
  #50  
Old 06-10-2009, 01:05 AM
Brad Gile's Avatar
Brad Gile Brad Gile is offline
Member
CAS SOA AAA
 
Join Date: Sep 2001
Studying for whatever I feel like
College: Alumnus of Brown and UW-Madison
Posts: 11,341
Default

Quote:
Originally Posted by E View Post
Found a new one by accident today in Excel 2007. Pressing CTRL+SHIFT+L while you are anywhere within a contiguous range puts autofilters at the top of each column in that range. Pressing it again removes autofilters. Doesn't work in Excel 2003. I also can't find it documented online.
There are several features in every version of MS Office that are undocumented, and this sounds like one of them. Typically, a developer in the Office Team creates a little feature for his/her own use and, for whatever reason, is never removed. You must remain aware that such undocumented "features" may disappear in future versions or service packs.
__________________
Brad Gile, FSA, MAAA
Affiliate Member of the CAS
Dedicated Retired Actuary

Spoiler:
Obama sucks and we all know it-TDA


Spoiler:

That's been the funniest subplot of this whole thing, the people on the left attacking this bill for not being even more of a steaming pile. - erosewater
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 10:04 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.60329 seconds with 10 queries