Update ending cell reference for entire column

[PaxMedJim]

Hi,

I am so lame when it comes to Excel, but my job requires that I manage an invoice spreadsheet that has been going on since 2009, even though all invoices are recorded in QuickBooks.

• Column A - Name of client w/list of projects nested under each client on a separate line
• Column B - Project Number assigned upon initiating the project
• Column C - Estimate provided to clients for each project
• Column D - sum of monthly invoice totals captured from columns to the right, from H to infinity, titled "Total to Date"
• Columns E-G - blank
• Column H - first entries began August of 2009 for monthly invoicing, per client, per project

Dilemma:

Column D sums up the invoices for that line and the formula reads, e.g.:  =SUM(H5:JT5).  Great for projects that ended by column JT5. However, some clients come back several years later and I enter a value in LD5 or LF5, etc. Now my formula ended @ JT5 and if I don't catch the error, the project "Total to Date" (Column D) is incorrect.

As I mentioned earlier, I inherited this massive spreadsheet that has over 2000 lines and not being an Excel wiz, I find myself updating Column D's formula line by line in order to make the lines total correctly. And, if I add a new client to the spreadsheet, I have found myself using ctrl+c for Column D (from a client above or below the newly added client to paste the formula for Column D for the new client and if I'm not careful, the formula may end back at JT5 bu I'm now on LB5.

I'd like to make a change to Column D that would update the end reference cell # to match cohesively throughout the spreadsheet. Currently, I'm making using +SUM(H5:ZZ5) for formulas in Column D so the ending reference will last for several years.

Obviously the #5 is not a constant throughout the spreadsheet. When a new client is added, said client is inserted alphabetically in the spreadsheet, therefore, it could read:  =SUM(H643:ZZ643), as an example.

Hopefully I've provided enough info for someone to understand my dilemma and provide a solution, if there is one.

Thank you in advance!

Jim

[#2]

@PaxMedJim Not sure I understand your problem, other than that you are forced to maintain a rather meaningless spreadsheet. But based on what you described it seems to be a good idea to use a SUM range like H5:ZZ5, big enough for many years to come. When you enter your formula in D5 all you need to do is copy it all the way down and the row reference (I.e. the number 5) will increase automatically. No need to edit each and every cell in column D. And when you insert a row for a new customer, for example  row 500, just copy cell D499 and paste it in D500 (or just drag the bottom right-hand corner of D499 down.

[#3]

@Riny_van_Eekelen 

You have a gist of the issue. How does one copy that formula for the entire column? When I try to do so, nothing changes. But, your suggestion is what I want to do - change the entire column to reflect the zz***. I realize the # will update automatically when I do it cell by cell. However, that is far too tedious for a spreadsheet with nearly 1800 lines. So, I'm all eyes to see how you can tell me to make that ZZ value apply to the entire column.

Thank you very much for responding!

Jim

[#4 · Accepted solution]

@PaxMedJim If column C is completely filled (i.e. no empty cells), enter the formula in D5 and double click the small square in the bottom right-hand corner of D5. That should copy down the formula in the blink of an eye, as far as column C contains something.

[#5]

Riny_van_Eekelen,

Thank you very much! That worked like a charm! I knew there had to be a way to do that quicker than 1 by 1 or ctrl+c and then scrolling to the bottom of the spreadsheet and highlighting the cells and then ctrl+v. You "learned" me sumptin' today. Much appreciated!!!

Jim