Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.
Keeping number of rows in Excel formula
Name: uhjb Date: July 3, 2009 at 12:46:03 Pacific OS: Windows XP Subcategory: Microsoft Office
Comment:
If I have a formula in A1 that reads: =sum(a2:a5000) to get the sum of up to 5000 rows. Is it possible to have that value remains as 5000 after I insert or delete some rows? TIA.
Name: DerbyDad03 Date: July 3, 2009 at 15:46:14 Pacific
Reply:
No need to "waste" B1.
=SUM(A2:INDIRECT("A5000"))
Keep in mind that in either case, you can't mess with Row 2. You will get a #REF error if you delete Row 2 or the Formula will change to =SUM(A3:INDIRECT("A5000")) if you insert a Row at 2.
If you want to lock in both the upper and lower cell, use this:
Summary: Hello, how to set the type (text, number, date,..) of column in EXCEL with VBA? (not manually through Ctrl+1 -> Format of Cells ->...) Thanks for your willingness. ...
0 
