Another Excel Formula Problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

Anne Troy

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

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!!
 

Anne Troy

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

Or start learning code now?
 

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).
 

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?
 

Anne Troy

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

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top