Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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, Im back again with another couplea questions. For those familiar with me, yes, its 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 dont even know WHAT to ask. Question #1; The guy who created this originally did this nifty little thing that I cant 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 dont 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, itll 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 Bosss 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. Its 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 Ive had info overload these last few days in learning just how Excel calculates time, (Jan 01, 1900 I believe) but that really hasnt 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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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. --- Per "greenusmarine53" skrev i meddelelsen ... 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, Im back again with another couplea questions. For those familiar with me, yes, its 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 dont even know WHAT to ask. Question #1; The guy who created this originally did this nifty little thing that I cant 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 dont 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, itll 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 Bosss 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. Its 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 Ive had info overload these last few days in learning just how Excel calculates time, (Jan 01, 1900 I believe) but that really hasnt 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! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
That worked beautifully. You guys make me look GOOD! But (isnt 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 dont keep bothering you with annoying questions. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
Care to recommend one?
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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/tr...831141033.aspx Gord Dibben MS Excel MVP On Wed, 24 Jun 2009 13:01:02 -0700, greenusmarine53 wrote: That worked beautifully. You guys make me look GOOD! But (isnt 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 dont keep bothering you with annoying questions. |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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 he http://www.mvps.org/links.html#Excel Good luck. "greenusmarine53" wrote: Care to recommend one? |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Helllooo Boyyss! IIImm Baaacckk!
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) "greenusmarine53" wrote: 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, Im back again with another couplea questions. For those familiar with me, yes, its 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 dont even know WHAT to ask. Question #1; The guy who created this originally did this nifty little thing that I cant 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 dont 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, itll 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 Bosss 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. Its 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 Ive had info overload these last few days in learning just how Excel calculates time, (Jan 01, 1900 I believe) but that really hasnt 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|