Solved

Posted on 2011-10-18
215 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

LVL 14

Expert Comment

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 14

Expert Comment

BTW:

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

LVL 1

Author Comment

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

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

!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 41

Expert Comment

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

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

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

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

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

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

LVL 43

Accepted Solution

This is how I would have done it.
ssaqibh-515890.flv
0

LVL 1

Author Closing Comment

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

Featured Post

Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…