|
|
|
|
Ultra Member
|
|
Oct 27, 2011, 02:22 PM
|
|
Excel-adding numbers pulled in from another sheet
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!
|
|
|
Software Expert
|
|
Oct 28, 2011, 02:25 AM
|
|
Should be as simple as:
=SUM(A:A)
|
|
|
Ultra Member
|
|
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
?
|
|
|
Ultra Member
|
|
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?
|
|
|
Ultra Member
|
|
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
|
|
|
Software Expert
|
|
Nov 3, 2011, 06:35 AM
|
|
Originally Posted by DrJ
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.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Eliminate data in sheet 1 from sheet 2 in excel
[ 1 Answers ]
I've got 4000 names in Sheet 1 and I've got 200 names is Sheet 2. The names of 200 person in Sheet 2 also contain in Sheet 1. I would like to ask how can I eliminate names in Sheet 1 (4000 names) based on names in Sheet 2 (200 names).
Padding numbers in excel
[ 2 Answers ]
I have about 500,000 cells on a spreadsheet that all follow a letter and number pattern. There are two patterns that look like this:
y124f07 and h67g02.
So the first is:
Letter, number, number, number, letter, number, number
The second is:
Letter, number, number, letter, number, number
View more questions
Search
|