+ Reply to Thread
Results 1 to 4 of 4

Using formulas with imported

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Using formulas with imported

    Hi Everybody,

    Basically i'm maintaining a spreadsheet which has a data connection to an SQL database. It imports customer submitted ratings for our employees. There are 4 columns for each employee each with a value out of 10. My problem is that no matter what i do i cannot format these columns as numbers, which is absolutley necessary as averages and such need to be found.

    In the format cells dialog box it states they are all numbers, but when I try and create average formulas i get a div#0 error no matter what. Also, the numbers are aligned left which leads me to believe their format has not been changed.

    Would anyone be able to help with this? It would be greatly appreciated as I'm relativley new to Excel.

    Thanks

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,383

    Re: Using formulas with imported

    Hi mo0ki,

    Welcome to the forum!!

    Try incorporating the following code into your existing macro or run it as a stand alone solution:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Using formulas with imported

    Hi Robert,

    Thanks alot for the welcome and your reply. Unfortunatley I am very new to excel and am not exactly sure where to implement this.

    The data connection was already set up on the spread sheet when I started working on it. I've just thought of some ways to gather meaningful information from the raw data in the spreadsheet. Would I implement that code in the 'command text' portion of the 'Definition' tab in 'Connection Properties'? Thats where the current code is which retrieves the data from the database.

    Thanks for your help!
    Last edited by Cutter; 08-03-2012 at 07:08 AM. Reason: Removed whole post quote

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,383

    Re: Using formulas with imported

    OK, maybe it's better to do it the manual way via these five steps:

    1. Make sure you're on the tab in question and in any empty cell enter an 1 and then copy this to the clipboard (Ctrl + C)
    2. Highlight the relevant range and from the shortcut menu (accessed via a right mouse click) select Paste Special
    3. From the Paste Special dialog click the Multiply option (radio button) and click OK
    4. Press Esc
    5. Clear the contents of the cell you used in step 1.

    HTH

    Robert

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1