1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Another Excel Formula Problem

Discussion in 'Business Applications' started by RHurlburt, Apr 6, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    I am downloading tables that have numeric/alpha formats:
    Example:
    "14.9M"
    In order to get the value I need, I may divide by a number OR an alpha/numeric:
    Exampel:
    "7.0M" or "7.0"

    So, the formula "=SUM(C9/C10)" now gives me "#VALUE!".

    In order to complicate this even more, Cells "C9" and "C10" are links to a table on another page that I have downloaded via query.... the formula in "C9", for example, is:

    "='Larry'!$P$7" and the value is "14.9M"

    I need to have a formula that will disregard the "M" or any alpha designation that may be accompanying that value. I have no control over the input value as it varies with each query download, and, sometimes even the value I am dividing with changes.

    It would seem there would be a format of the cell where I copy the value from...

    ?????

    Thanks
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    If you can't do it the way I'm about to explain, can you zip and upload?

    Where ever your 14.9 exists, what is making that up? If it is typed in as "14.9M", then stop doing that. Instead, type it in as 14.9. Then go to Format-Cell and choose Custom format and type this in:

    0.0"M"

    That way, it'll DISPLAY 14.9M, but the value remains 14.9 for your other calculations.
     
  3. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    <<If it is typed in as "14.9M", then stop doing that>>

    Love it!

    As I stated in my first post, it is a downloaded table that has that format. I can't do anything about it, otherwise, your suggestion would be heeded.

    This is a table via a web site query.
     
  4. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Oh, yes you can. :)
    Are they all "M"?
    Just select that column, hit Ctrl+H.
    Type M in the FIND WHAT box.
    Put nothing in the REPLACE WITH box.
    Hit replace all.

    Now...since they'd been formatted as text, you MAY need to force Excel to see them as numbers. To do that, type a 1 in any blank cell. Copy the cell. Select all the new "numbers only" cells, hit Edit-Paste special, Multiply, OK. Keep the cells selected, or select the whole column, format custom like I described before.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    LOL. By the way, you don't think I read everyone's complete post, do ya? I know, I know!! I should. My bad.
     
  6. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    Is that the column in the table I am linking from, or the column in the table I am linking to? And, when I do that, will I have to do this everytime I update (several times a day)?

    As you can tell, I haven't tried it yet, but will!!
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Aha.
    You're gonna need code in your workbook.
    Wanna upload?

    Or start learning code now?
     
  8. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    I have no problem with coding. Have learned a lot in the past few weeks. (short learning curve).
     
  9. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  10. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    As a matter of fact, I have that very document downloaded and have used it. I didn't think of a macro and THAT is what halfzheimer's does to a person.

    The next step would be to place that "sub" so that when the table/page updates, the sub runs ...???? Don't want to have to press a button.

    Or, am I getting to far ahead?
     
  11. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Nope. That's fine. I don't know how often your table updates or what fires an update... ?
     
  12. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    I have a cell that periodically changes when someone makes an input. This cell change fires the queries which download new table data based on that input.
     
  13. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Makes an input? So it's a worksheet change event?
    You could likely record those two tasks, take the guts of that code and paste it into the end of your other code.

    If you can't/don't know how, send it to [email protected], and I'll do it for ya. Will be back in about a half hour.
     
  14. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    Me too. I have to try it.

    Thanks a bunch!
     
  15. RHurlburt

    RHurlburt Thread Starter

    Joined:
    Oct 31, 2003
    Messages:
    130
    It works really great. Of course I had to delete the macro a couple of times and re-create it. I haven't got it to automatically update on the event yet ... but eventually will. The macro is "FormulaM" and I thought I could place that name at the end (before "End Sub") of an update that is attached to a control button that takes me to this sheet. But, it doesn't activate.

    One more question here .... does the sub "FormulaM" macro have to be in the same module as the sub attached to the control button? Or will it find it? If so, I need to put them in the same module.

    I could end up with this thing in a couple of modules if this is the case.

    By the way, I seem to remember that you (Dreamboat) have another home (web site) other than at Ozgrid. I didn't keep the link and I notice those pieces of information aren't being displayed at this site any longer.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/217787

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice