Problem with Formatting of Imported Data

    Hi All,


    I have a formula that uses one column of Account #'s and some columns of monthly balances of these accounts to calculate and display sum.


    Sum is calculated based on Acc's chosen in a separate cell.


    If I import a set of data from an external database the formula does not recognize the formating of the Account #'s and shows 0 balance.


    When I enter an account # in a separate cell elsewhere and drag it over the matching acc # in the imported range the formula works correctly.


    I have checked the formating of both cells before the cut and paste and it is exactly the same (general).


    Why does this happens and is there a way to fix this?

    Format issue with imported data


    Hi All,


    I have a formula that uses one column of Account #'s and some columns of monthly balances of these accounts to calculate and display sum.


    Sum is calculated based on Acc's chosen in a separate cell.


    If I import a set of data from an external database the formula does not recognize the formating of the Account #'s and shows 0 balance.


    When I enter an account # in a separate cell elsewhere and drag it over the matching acc # in the imported range the formula works correctly.


    After import the formating of both cells (imported and manualy entered acc #) is exactly the same (general etc) but the text alignment is different (strange)!



    Why does this happens and is there a way to fix this?

    Re: Bug!!!


    Look at the formula.


    It uses ISNUMBER in some places could this cause the error.


    May be the formula can be adjusted to understand different formating?

    Re: Format issue with imported data


    Hi Alex,


    Actually the imported numbers are TEXT values. To convert them into TRUE numbers try the following.


    Select a blank cell-> copy it-> highlight the imported range->edit
    paste special->check add->OK


    HTH

    Re: Problems with Formating of Imported Data


    Hi


    Try changing Formatting to text or number.
    If it dosent help, attach a sample, will checck it out


    Aadarsh

    :angrypc:

    Re: Format issue with imported data


    Hi,


    Thanks a lot! :)


    By the way is it possible to record a macro or put some code into the sheet so that on open or data refresh the a column or a sheet etc. would get 'pasted' :) over.



    Thanks again!!!

    Re: Problems with Formating of Imported Data


    This very often happens with data imported from another system.


    The fix will probably depend on whether your account numbers are text or numeric. If they are text, then you may need to Trim the data before you check it. If they are numeric you may need to use Value to turn it into a number. Either way, without manually formatting the data first you may need to use a formula along the lines of:


    =SUMPRODUCT((TRIM(A2:A4)=D2)*B2:B4)


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Problems with Formating of Imported Data


    Please DO NOT post duplicate threads on identical topics. It is a massive waste of people's time as they can't see all the options that have been suggested. I have merged the two threads.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login