View Full Version : Excel-adding numbers pulled in from another sheet
DrJ
Oct 27, 2011, 02:22 PM
I know I know the solution to this but it has been 2 years since I even opened an Excel Spreadsheet...
Anyway, lets say I have a column on Sheet1 pulling in numbers from Sheet2. On Sheet1, I want to SUM the total value of the numbers that are being pulled over.. but I am just getting 0
Can someone point me in the right direction here? I REALLLLLY appreciate it!
JBeaucaire
Oct 28, 2011, 02:25 AM
Should be as simple as:
=SUM(A:A)
DrJ
Oct 31, 2011, 12:20 PM
That's what I thought but for some reason, it's not. The cells that it is summing, is pulling in data from another sheet. I thought that it should add them up just the same, but it is just giving me a result of 0
EX:
cell A1 contains ='Sheet2'!B1 and produces a result of 2000
cell A2 contains ='Sheet2'!B2 and produces a result of 3000
cell A3 contains =SUM(A1:A2) but produces a result of 0
?
DrJ
Oct 31, 2011, 05:01 PM
Actually, where the problem seems to be is where the data is originating. Let me try to explain...
I have a sheet (Raw Data) where I paste these emails that I get that have client info on. These emails automatically parse the info into cells that contain things like this:
ClientName: Joe Shmoe
DebtAmount: 20000
that is all contained into one cell. I then use another sheet (Form Data) to extract just the specific client data (Joe Shmoe & 20000) from the field names (ClientName & DebtAmount).
This is where the problem seems to be. When I pull the DebtAmount (20000) into yet another sheet where I tally it all up, I get a 0. If I substitute the original formula for a simple numerical value, it works fine. But with this formula to separate the data from the field name, I get a 0. This is the formula I am using to extract the actual data from the field names:
=MID('Raw Data'!A216,FIND("=",SUBSTITUTE('Raw Data'!A216,": ","=",LEN('Raw Data'!A216)-LEN(SUBSTITUTE('Raw Data'!A216,": ",""))-1))+2,256)
That takes this:
CurrentBalance20: 20000
and gives me just the 20000 into a field. That is ultimately what I am trying to add together.
Does that make sense?
DrJ
Oct 31, 2011, 07:59 PM
Well I figured out how I had gotten around this before...
I named the cells that were pulling in the data (creditor_1_debt, creditor_2_debt, etc) then just added them all together (=creditor_1_debt+creditor_2_debt)
This seems to do what I needed it to do.
Thanks
JBeaucaire
Nov 3, 2011, 06:35 AM
Thats what I thought but for some reason, it's not. The cells that it is summing, is pulling in data from another sheet. i thought that it should add them up just the same, but it is just giving me a result of 0
EX:
cell A1 contains ='Sheet2'!B1 and produces a result of 2000
cell A2 contains ='Sheet2'!B2 and produces a result of 3000
cell A3 contains =SUM(A1:A2) but produces a result of 0
?
Sorry for the delay in answering. The values being displayed in A1 and A2 might "appear" to be numbers, but apparently are text strings. Try adding a +0 to the end of the formula to convert any numbers that are really text back into numbers.
The functions MID, LEFT and RIGHT are text functions, they return text. The +0 trick should fix the results back to normal numbers.