Helllooo Boyyss! III’mm Baaacckk!

[greenusmarine53]

Bruhahhahaha! Okay, I have no idea who you people are, you know the ones that
take the time to answer a bunch of questions from people like me. I mean of
all the places to be, you are here answering questions about Excel! Thank
you. So very very much.

As you can see, I’m back again with another couple’a questions. For those
familiar with me, yes, it’s the same spreadsheet. This thing is constantly
morphing.

NOTE: Before I post questions here, I do do (hehe, he said dodo) my best to
find the answers elsewhere before bothering you fine people but when it comes
to Excel, I don’t even know WHAT to ask.

Question #1; The guy who created this originally did this nifty little thing
that I can’t seem to replicate. Each separate worksheet (of which, there are
now 12) has 51 columns, which are identical to each other except for the
number of names in each worksheet. That is superfluous information. What is
important is the first three columns. They don’t move! As I scroll left and
right, those first three columns stay right where they are which is VERY
convenient as they contain da names. Now, how in the name of Picard do I do
that!

Not enough info? Well there is a bold line separating columns ‘C’ and ‘D’
and as I scroll right, it’ll go from ‘C’ to ‘E’ all the way to ‘C’ and ‘AY’.
I thought this might be called a partition but I guess for Excel, partition
means something else.

Question #2; The Boss’s Boss, Da Man, or as we like to call him, the
Commanding Officer, really liked what you guys helped me do so far and has
asked me to do some other nifty Excel magic. In this all consuming
spreadsheet is a worksheet dedicated to pistol qualification. It’s pretty
straight forward. Four columns containing rank, name, qual date and score. A
couple little functions at the bottom calculating how many experts, how many
sharpshooters and so on.

These scores are good for one year. At the end of that year, the individual
needs to shoot again. What The Man, and by extension myself, would like to
know is if there is a way to alert us when that year is up. Now I’ve had info
overload these last few days in learning just how Excel calculates time, (Jan
01, 1900 I believe) but that really hasn’t helped me.

So what I envision is tweeking the column of dates, once the year is up, the
box turns red. Or something. Is this, or something roughly similar, possible?

Once again, I thank for your time and your patience and your understanding.
And most of all for being here to answer these questions. IMMEDIATE HELP!!!!
Hahahahaha!

 

[greenusmarine53]

Wait! It's freeze pane isn't it? I shall try that. I just had to go back a
couple dozen pages in the forum to find a similar problem. Guess I didn't
look enough before.

 

[Per Jessen]

Hi

Q1: The secret is to freeze/unfreeze panes on the Window menu.

Select B2 and goto Window > Freeze Panes. Now row 1 and column A will always
show, as you scroll down and right in the worksheet.

Q2: Conditional formatting is just what you need.

Suppose you have qual date in column C2 and down, select cell C2 to last
cell in column and goto Format > Conditonal Formatting > Condition 1:
Formula is > Enter this formula in the next field: =$C2+365<=TODAY()
Click Format and format as desired.

Now the cell will turn red or whatever format you decide to use, when it has
been a year since laste qual. To get the warning 30 days before change
reduce the number in the formula (365) with 30...

Hopes this helps.

 

[Don Guillett]

Question 1
window>freeze panes>unfreeze>place cursor where desired. All rows above and
columns to the left will be frozen.
Q2
google
excel:conditional formatting

 

[greenusmarine53]

That worked beautifully. You guys make me look GOOD!

But (isn’t there always) while it worked as desired, exactly as desired in
fact, that still leaves me clueless (Nothing new).

So, I ask for your patience just a bit longer. Please explain what you did.

What does the dollar sign mean ($)?

C3 describes the first box, but does highlighting all of the ones below tell
Excel the range of boxes? I ask because generally I have to do something like
C3:C73.

What do the parentheses ‘( )’ at the end do?

And finally, this worked great for an annual requirement. What about a
semi-annual requirement, which is thankfully fixed at Oct-Mar and Apr-Sept?
Rather than tell me straight out, if you could walk me through it, you know,
teach a man to fish and all that. That way I don’t keep bothering you with
annoying questions.

 

[Don Guillett]

Time to invest in a good book

 

[greenusmarine53]

Care to recommend one?

 

[Gord Dibben]

Teach a man to fish...................

Some tutorial sites for basics of Excel........

http://www.usd.edu/trio/tut/excel/index.html

http://www.baycongroup.com/el0.htm

http://office.microsoft.com/en-us/training/CR061831141033.aspx


Gord Dibben MS Excel MVP

 

[JLatham]

It's actually hard to beat the "...For Dummies" series.

John Walkenbach's "The Excel 2007 Bible" would probably be a good bet also.

For sites with really great help and loads of Excel tips, 'tricks' and good
"how-to"s try
http://www.contextures.com
http://www.cpearson.com
http://www.decisionmodels.com
http://RonDeBruin.nl
http://spreadsheetpage.com
http://www.jkp-ads.com
And when the CO gets around to telling you to chart and graph it all, be
sure to visit
http://www.peltiertech.com

There's a good list of Excel MVP websites here:
http://www.mvps.org/links.html#Excel

Good luck.

 

[FloMM2]

greenusmarine53,
Here is what I came up with to solve at least part of your problem:
In cell A1, type the formula "=NOW()" without the "".
This will put the current date on your form everytime it is opened.

Then I just did conditional formatting using that cell (A1).

Condition 1
Formula Is "=((Year($A$1)-YEAR($C$3))*12+MONTH($A$1)-MONTH($C$3))<=-6"

Select the format button, select Pattern tab and pick a color. I chose yellow.

Condition 2
Formula Is "=((Year($A$1)-YEAR($C$3))*12+MONTH($A$1)-MONTH($C$3))<=-3"

Select the format button, select Pattern tab and pick a color. I chose orange.

Condition 3
Formula Is "=((Year($A$1)-YEAR($C$3))*12+MONTH($A$1)-MONTH($C$3))<=-1"

Select the format button, select Pattern tab and pick a color. I chose red.

Setting it up this way, everytime the spreadsheet is open, the cells will
automatically change color.

Hope this helps.
One ex-sailor to a marine. (LOL)