I suggest a different approach!

Couple things to do:

1. Select A2 and open the Define Name dialog;
2. Create a new name "LastCell";
3. Give the name local (sheet-level) scope by including the
sheetname...
'Sheet1'!LastCell
..where the sheetname is wrapped in apostrophes, and the defined
name is prefixed with the exclamation character;
4. In the ReferTo box type...
=a1
5. Click 'Add' and close the dialog.

Now.., if your values start in A2 and span several columns then in the
totals row enter the following formula...

=A$2:LastCell

...where you want to specify that the formula absolutely refs row 2 by
prefixing the row number with the currency symbol.

Now.., as rows are inserted/deleted between row 2 and the totals row
you formulas will auto-update appropriately.

NOTE: in your case I'd use a helper column to do the multiplication so
your totals sum the helper column[s]. If that's not practical for your
worksheet layout then add a single column to contain the multiplier...

Say, for example, using column 'Z':
Select Z1 and add another local scope defined name as follows:

Name: Factor
RefersTo: =$Z1

...so that it absolutely refs column Z and adjusts for the row it's used
in automatically. Then, in your value cells include the multiplier
something like this...

In Q55, convert to formula style input. So if the value is 100
then...
=100*Factor

...OR if it contains a formula then wrap that in parenthesis and append
the multiplier:

=(formula)*Factor

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com