add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
| Article | Body |
| 1501 | I'm trying to set up a form to use for "what if" scenario's to enter info and try different combinations of figures (a lot like a loan payment calculator). Is it possible to protect the formula so I don't overwrite it when I go back into the worksheet. I'm new to this, obviously. Thanks |
| 1512 | Cells can be locked or unlocked. You can set this option for range of cells through the menubar: Format|Cells|Protection Tab. But setting the cells locked property has no effect until the worksheet is protected. That can be done via the menubar: Tools|protection|Protect sheet (with or without a password). By default, all the cells are locked. You have to unlock the cells that you want to be able to change. (But you can select all the cells (ctrl-A) and lock/unlock all of them. This might be useful if the number of locked cells is small, but the number of locked cells is large.) Be aware that if you protect the sheet there are a lot of things that can't be done (the list is different with different versions of excel). But even formatting (bolding/font color/fill color) might not be available on a protected worksheet. And (this is not part of your question), the protection scheme isn't really used as a security measure. It's used as a "don't do anything stupid to this cell" measure. There have been many posts on how to remove protection from a password protected worksheet. In fact, since my memory is terrible, I usually protect the sheet w/o a password. It stops the stupid stuff, but allows me to make changes later without finding that small postit note with the password scribbled on it. wj wrote: I'm trying to set up a form to use for "what if" scenario's to enter info and try different combinations of figures (a lot like a loan payment calculator). Is it possible to protect the formula so I don't overwrite it when I go back into the worksheet. I'm new to this, obviously. Thanks -- Dave Peterson ec35720@msn.com |
| 2648 | After you've sorted it, you can impress him with this: Select your bottom right cell to the top left cell (last row of headers) and do a Data|Filter|Autofilter. You'll see little dropdowns on each of the columns that allow you to filter your data. It's really easy and very useful. To see all of the info after you've filtered by multiple columns, just do Data|filter|showall. Sometimes people as for sorts, but like this better. (just a thought...) Melanie Wells wrote: I work for a mortgage company, and they have asked me to create a simple spreadsheet with Borrower name, addy, loan amount, settlement date.. etc. I have done this, but one of our loan officers wants to be able to have it flip from being in order by Borrower last name, to being in order by settlement date. I am unsure of how to do this wihtout messing up my file... if anyone can help, I would appreciatte it. Thanks, Melanie -- Dave Peterson ec35720@msn.com |
| 4759 | Thank you for the reply. I followed your detailed instructions, yet I'm still printing ALL cells. Any other suggestions? -----Original Message----- Non-VBA method. Select your worksheet (I'll call mine Sheet1.) Pick a column that will always have data if that row has anything showing. I'm gonna use column B. Then Insert|Name|Define In the Names in Workbook Box, type: LastRow In the refers to box, type this formula: =MAX(ROW(sheet1!$B$1:$B$500)*(sheet1!$B$1:$B$500<"")) adjust the rows (500 to what you want: 8000???). Now create another name for whole thing if all the rows had stuff that showed. Call it FullPrint In my example, it looked like: =sheet1!$A$1:$H$500 Now one more range name, but this one is special. It's a worksheet range name. The names box should look like this: sheet1!PrintArea the refers to box should be: =offset(fullprint,0,0,lastrow) You need the sheetname in front of PrintArea (with the exclamation point!!). The trick with the LastRow name is that it uses an array formula (but you don't hit ctrl-shift-enter) to find the last "non-blank" (different than non-empty) cell in that range (b1:b500). The PrintArea is a name that excel uses to define the printRange. The offset() stuff says to take the fullprint range, starting at the topleftcell of fullprint (that's the 0,0 portion) and resize it to the number of rows calculated by LastRow. ======= this is a pretty neat technique. I found it (well close to it) in a template that MS gives away. It's used for loan repayment calculations: Here's a loonnnnnnnnng link to it (all one line in your browser): /TemplateGallery/template s/4/tp830.asp? i=6&l=1116,113,841,1043,39,14,830,856,99,31,137,&RC=7&M=11& mh=20&qu=&ct=&cid=0.138.139 Alfman wrote: I created a template that will automatically calculate scale scores based on a user input of raw scores. How can I automatically set and reset the print area again and again based on the number of entries made? For example, formulas have been entered through row 8000. However, if only 50 raw scores have been entered, I only want to print 50 rows, not 8000 rows. Alfman -- Dave Peterson ec35720@msn.com . |
| 4760 | Crap. I just tried it. QW%#$%!!@ing typo. Change the sheet1!printArea to sheet1!Print_Area Notice the underscore!!!! Sorry, Although you may want to try Don's VBA solution (maybe in the beforeprint event??) (But the non-vba solution is pretty neat--especially when it works!) Alfman wrote: Thank you for the reply. I followed your detailed instructions, yet I'm still printing ALL cells. Any other suggestions? -----Original Message----- Non-VBA method. Select your worksheet (I'll call mine Sheet1.) Pick a column that will always have data if that row has anything showing. I'm gonna use column B. Then Insert|Name|Define In the Names in Workbook Box, type: LastRow In the refers to box, type this formula: =MAX(ROW(sheet1!$B$1:$B$500)*(sheet1!$B$1:$B$500<"")) adjust the rows (500 to what you want: 8000???). Now create another name for whole thing if all the rows had stuff that showed. Call it FullPrint In my example, it looked like: =sheet1!$A$1:$H$500 Now one more range name, but this one is special. It's a worksheet range name. The names box should look like this: sheet1!PrintArea the refers to box should be: =offset(fullprint,0,0,lastrow) You need the sheetname in front of PrintArea (with the exclamation point!!). The trick with the LastRow name is that it uses an array formula (but you don't hit ctrl-shift-enter) to find the last "non-blank" (different than non-empty) cell in that range (b1:b500). The PrintArea is a name that excel uses to define the printRange. The offset() stuff says to take the fullprint range, starting at the topleftcell of fullprint (that's the 0,0 portion) and resize it to the number of rows calculated by LastRow. ======= this is a pretty neat technique. I found it (well close to it) in a template that MS gives away. It's used for loan repayment calculations: Here's a loonnnnnnnnng link to it (all one line in your browser): /TemplateGallery/template s/4/tp830.asp? i=6&l=1116,113,841,1043,39,14,830,856,99,31,137,&RC=7&M=11& mh=20&qu=&ct=&cid=0.138.139 Alfman wrote: I created a template that will automatically calculate scale scores based on a user input of raw scores. How can I automatically set and reset the print area again and again based on the number of entries made? For example, formulas have been entered through row 8000. However, if only 50 raw scores have been entered, I only want to print 50 rows, not 8000 rows. Alfman -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com |
| 4780 | And I think the easiest solution would be to select your complete range. Then do Data|Filter|Autofilter Then you could filter on that one column--nonblanks. The rows that are blank in that column would be hidden and not print. Dave Peterson wrote: Crap. I just tried it. QW%#$%!!@ing typo. Change the sheet1!printArea to sheet1!Print_Area Notice the underscore!!!! Sorry, Although you may want to try Don's VBA solution (maybe in the beforeprint event??) (But the non-vba solution is pretty neat--especially when it works!) Alfman wrote: Thank you for the reply. I followed your detailed instructions, yet I'm still printing ALL cells. Any other suggestions? -----Original Message----- Non-VBA method. Select your worksheet (I'll call mine Sheet1.) Pick a column that will always have data if that row has anything showing. I'm gonna use column B. Then Insert|Name|Define In the Names in Workbook Box, type: LastRow In the refers to box, type this formula: =MAX(ROW(sheet1!$B$1:$B$500)*(sheet1!$B$1:$B$500<"")) adjust the rows (500 to what you want: 8000???). Now create another name for whole thing if all the rows had stuff that showed. Call it FullPrint In my example, it looked like: =sheet1!$A$1:$H$500 Now one more range name, but this one is special. It's a worksheet range name. The names box should look like this: sheet1!PrintArea the refers to box should be: =offset(fullprint,0,0,lastrow) You need the sheetname in front of PrintArea (with the exclamation point!!). The trick with the LastRow name is that it uses an array formula (but you don't hit ctrl-shift-enter) to find the last "non-blank" (different than non-empty) cell in that range (b1:b500). The PrintArea is a name that excel uses to define the printRange. The offset() stuff says to take the fullprint range, starting at the topleftcell of fullprint (that's the 0,0 portion) and resize it to the number of rows calculated by LastRow. ======= this is a pretty neat technique. I found it (well close to it) in a template that MS gives away. It's used for loan repayment calculations: Here's a loonnnnnnnnng link to it (all one line in your browser): /TemplateGallery/template s/4/tp830.asp? i=6&l=1116,113,841,1043,39,14,830,856,99,31,137,&RC=7&M=11& mh=20&qu=&ct=&cid=0.138.139 Alfman wrote: I created a template that will automatically calculate scale scores based on a user input of raw scores. How can I automatically set and reset the print area again and again based on the number of entries made? For example, formulas have been entered through row 8000. However, if only 50 raw scores have been entered, I only want to print 50 rows, not 8000 rows. Alfman -- Dave Peterson ec35720@msn.com . -- Dave Peterson ec35720@msn.com -- Dave Peterson ec35720@msn.com |