Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel-adding numbers pulled in from another sheet (https://www.askmehelpdesk.com/showthread.php?t=607348)

  • Oct 27, 2011, 02:22 PM
    DrJ
    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!
  • Oct 28, 2011, 02:25 AM
    JBeaucaire
    Should be as simple as:

    =SUM(A:A)
  • Oct 31, 2011, 12:20 PM
    DrJ
    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

    ?
  • Oct 31, 2011, 05:01 PM
    DrJ
    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?
  • Oct 31, 2011, 07:59 PM
    DrJ
    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
  • Nov 3, 2011, 06:35 AM
    JBeaucaire
    Quote:

    Originally Posted by DrJ View Post
    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.

  • All times are GMT -7. The time now is 02:05 AM.