So....a friend has a spreadsheet that will be growing daily with new data and on one row, he'll have the totals of all calls that he wants to sum automatically. Cell a2 has the year to date total but instead of using sum(a3:xx3) or something similar, he asked if there's an 'Infinite' or some other command that would catch it if his colums go to xy, he'd still have the correct total. Is there any way this can be done?

There are two ways I can think of,

one is simply SUM the whole row like:=SUM(A2:A65536) this is for Excel 2003

=SUM(A2:A1048576) this is for Excel 2007 + 2010A much more elegant way is to use a

Dynamic Named Range

Here are two examples:http://www.excel-easy.com/examples/...

http://www.ozgrid.com/Excel/Dynamic...MIKE

Thanks, Mike! I like the simplicity of the first one.

I like the simplicity of the first oneBut you may give up speed for simplicity,

because you will be checking a LOT of cells that are empty.I always advise against using the shortcut form of A:A to sum a whole column

this is basically the same thing, so I would advise you use the Dynamic Name Range solution. It may take a few seconds longer to set up, but in the long run it is the better of the two option. In my opinion.MIKE

Ask Your Question

Weekly Poll