Solved

# Excel formula for adding cells

Posted on 2011-10-18
Medium Priority
218 Views
I am trying to add 2 cells up & display the sum in one of the cells.

Example: Add number in A1 to number in B1 & then display the total in B1. I'm basically trying to create a way to enter a number for a particular week to the yearly total & display it the new amount in the yearly total column.

Is this possible?? I'm sure there would be some sort of variable involved, but not sure how to do it.
0
• 4
• 3
• 2
• +3

LVL 15

Expert Comment

ID: 36990259
It's not possible.  You'll be creating a circular reference because you are trying to sum a column with another column into itself.  Why not just create another column
0

LVL 15

Expert Comment

ID: 36990263
BTW:

Do you have a sample so we can look at it?  maybe we can suggest another way
0

LVL 1

Author Comment

ID: 36990436
I would like the entries from the first column cell to be added to the number in the 2nd column cell & then display the total in the 2nd column cell.
Test.xls
0

LVL 10

Expert Comment

ID: 36990533
Try running the following macro:
------------------------------------------------------

For each cell in rows("B:B")
cell.value = cell.value + cell.offset(0,-1).value
Next cell

End Sub
------------------------------------------------------

(to run a macro, go to the visual basic editor, insert a blank module, and copy and paste the text into the module. you can then set the macro to run by clicking a button of the quick access toolbar, or assigning a keyboard shortcut.)
0

LVL 10

Expert Comment

ID: 36990535
!Whoops! forgot one line. use THIS code:
------------------------------------------------------------------

For each cell in rows("B:B")
if cell.value = "" then exit for
cell.value = cell.value + cell.offset(0,-1).value
Next cell

End Sub
0

LVL 42

Expert Comment

ID: 36990648
You can do this in one step:

Select the values in column A, then hit COPY

Then, select B2

Then, hit Paste Special, and select the ADD operation.  Hit OK.

The two columns are added, with the result now in Column B.

here's a pictoral on that:http://www.contextures.com/xlDataEntry04.html

Cheers,

Dave
0

LVL 1

Author Comment

ID: 36993344
Thanks etech0, I will try that. But there about 5 sets of cells that will need to be added together.

Thanks dlmille, but I'm setting this up for someone else to enter the data & it automatically updates the total cell.

This may make it clearer:
I have a column for "Weekly Referrals", then a column for "Total Referrals".
Then a column for "Weekly Visitors", then a column for "Total Visitors".
Then a column for "Weekly Meetings", then a column for "Total Meetings".
Then a column for "Weekly Ref. Received", then a column for "Total Ref. Received".
Then a column for "Weekly Dollars", then a column for "Total Dollars".
The Total columns are annual.

Now this is the reason for not adding additional column as it would make the spreadsheet to large to print & harder to understand.

0

LVL 3

Expert Comment

ID: 36998694

take a look at the attach and try entering data in the weekly columns. The workbook should automatically add the weekly number to the Year totals.

I did this with a change event macro. This is code on a worksheet module, that will automatically be triggered whenever a value on a sheet gets entered/changed.
For this exampe i have limited the trigger to columns A, C, E, G, I and rows 2 to 6.
See code in the Sheet1 module (double click Sheet1 in the VBA editor):
If Not Intersect(Target, Range("A2:A6,C2:C6,E2:E6,G2:G6,I2:I6")) Is Nothing Then

Change this to the final layout of your worksheet.

Kind regards,
Davy

Test-1-.xls
0

LVL 43

Expert Comment

ID: 37010658
The problem with these approaches is that if the routine occurs more than once then the values would go berzerk and there would be no way to return. This could be most dangerous with the change event.

I strongly recommend that the results be recorded in a new column instead of the same column and the column B can be hidden.
0

LVL 1

Author Comment

ID: 37011666
ssagibh, I agree & I have been trying out a couple of things to do this. Can you point me to a link that gives a description of how to hide the calculating cells?? Also there is a really simple answer to this, but how do you display the value in one cell in another??

0

LVL 10

Expert Comment

ID: 37012711
to display the value of A1 in another cell, type
=A1
in that cell.
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 500 total points
ID: 37013266
This is how I would have done it.
ssaqibh-515890.flv
0

LVL 1

Author Closing Comment

ID: 37015049
Thanks!! This looks exactly like I wanted it to work in the first place.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This article describes a serious pitfall that can happen when deleting shapes using VBA.