Results 1 to 3 of 3

Thread: How to covert Cr amount as Negative while data exported from Tally Software

  1. #1

    Lightbulb How to covert Cr amount as Negative while data exported from Tally Software

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I often need to export data from Tally Accounting Software to Excel. While exporting data from Tally the positive amount transferred as Dr and credit amount as Cr . However I need to show the Dr amount as positive and Cr amount as negative like (-) 10,000 or (10,000). I have tried to use function like IF.. RIGHT..SUBSTITUTE or IF..RIGHT..LEFT...LEN, but it doesn't work. Though the Dr or Cr followed by number is displaying in excel, however when i put the cursor in respective cell it shows only the number and no Dr or Cr appears. More over the range in auto sum considered the numbers as positive.
    Please refer the file attached herewith. I would appreciate you for solving the above problem.
    Attached Files Attached Files
    Last edited by Prodip Das; 2015-12-09 at 11:35 AM.

  2. #2
    Conjurer Beamer's Avatar
    Join Date
    Nov 2014
    Excel Version
    Hi Prodip Das.
    That is a bit tricky, but after much consulting with the Google God, I came up with this:-

    1. Select Cell C5 of Trial Balance Sheet
    2. Press Ctrl+F3 > New and in the Name box, type cell_format
    3. In the Refers to box, enter this formula =GET.CELL(53,'Trial Balance'!$C5) and click on Close

    On Sheet1 Cell C5 enter this formula and copy down: =IF(RIGHT(cell_format,2)="Cr",-('Trial Balance'!C5),'Trial Balance'!C5)

    Edit: If you wish to get rid of the Cr/Dr just reformat the cells as currency.
    Last edited by Beamer; 2015-12-09 at 02:01 PM.

  3. #3
    Wow..its great..thanks a lot..I was trying this solution and visit several sites. But u are great man..

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts