Learning Microsoft Excel

[vantillian]

Great board.

[berge]

lobogotti, thanks, I'll try and help out whenever I can. I posted some excel links (of sorts) in the Ibox, congrats on the new baby! First? or experienced at not getting much sleep?

[lobogotti]

i do apologize. i'm just not here enough like i should be. glad berge joined as a mod. as he seems knowledgable.

with my sigma project at work, teaching my excel class, my spanish lessons, and a new baby i just don't make it to ihub at night.

glad you resolved the issue

[berge]

not a problem, even if you delete the macro, the module remains, and it will ask about macros, so you need to remove the module. Glad the problem is solved!

[Koikaze]

Thank you, very much, for your help with this. Linda finally got it. Here's how she took care of it:

Yes! I got rid of the macro error. I'm not quite sure where it clicked in my head but here is what I did:

I closed the file saving it under another Excel version.

I opened the file, tried to type in a field and tab out to get the error message.

After the original error, a different information window appeared:

"Visual Basic macro modules are now edited in the Visual Basic editor, not the workbook. To see your modules, click Visual Basic Editor (Tools menu, Macro command). In the Project window, look in the Modules folder. Modules are still saved in the workbook."

Well, this made me look at the modules folder a bit further and I discovered that there was one saved - even if the previous nine times I looked I thought there were none because this one was blank. A right click on it allowed me to remove it and, voila!

Of course there was a problem in that I had to go fix conditional formats and ALL formulas. I think I've got the formulas in, but beware.

Perhaps that information will be helpful to someone else.

Thanks, again

Fred

[berge]

try going to window-->unhide. If it's shaded out, you don't have a personal workbook.

[Koikaze]

OK, berge

We're off to do da google
The wonderful google of oz

I'll let you know if we find anything on it.

Thanks,

Fred

[berge]

do a search in google on personal workbooks. I had that issue with a spreadsheet from a customer. It won't open if any other book is open, and the personal workbook opens up as an invisible workbook any time you use excel (if you have ever saved macros to the personal option)

[Koikaze]

Yup, ran a virus scan this morning.

There are no macros shown in either tools->macro->macros or tools->macro->visual basic editor.

The link to mrexcel discusses a somewhat similar situation but we are not using any autostartup. I'm not a member there, so the search function didn't work for me.

Thanks for your help.

We'll keep looking.

Fred

[berge]

go to tools-->macro-->macros, and see if there are any macros in the open book. You can choose to delete any.

if you have stored any macros in the personal workbook, it might be an issue http://www.mrexcel.com/archive2/69900/81032.htm

if this doesn't help, go to tools-->macro-->visual basic editor, and see if there are any modules listed in the VBA project window...you can delete those as well

I assume you ran a virus scan just to be safe?

[Koikaze]

Hi,

Just in case you're passing by, I have a weird problem with a workbook my daughter created for me. It has 13 pages (a couple of which are for calculation only) and umpty-ump dozen formulae. I produce about a dozen .CSV files which she opens to copy the data to the proper pages in the workbook. The method has worked flawlessly for several years.

I recently changed the layout and content of the data I produce. Linda modified the appropriate pages to accept the new data, and the system started generating a warning during keyboard entry. The warning is:

"The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros."

We do not want macros enabled for security reasons. To the best of our knowledge, the workbook does not contain macros. We certainly have not written any. We have no idea the source of this message or how to stop it.

Have you ever seen anything like this? Can you help us get rid of this annoyance?

Fred

[mmayr]

EXCELLENT! Do what you love!

Peace,

[lobogotti]

i volunteer a class to my employer. i thought it would help a few people that have problems with it. turns out over 4% of the work force wants to take the course...i'm booked two years out at Headquarters alone (no joke)

i opened my mouth big on that one

[mmayr]

Thanks Lobo!

I'm sure I'll be back for more help. I'd love to take your class. Is it offered online? I'd definitely like to get better at using Excel.

Keep me posted.

Peace,

[BullNBear52]

If you have an excel problem you can post it here.

[analyzethis]

Anyone here know lobogotti's phone number?

[lobogotti]

sorry, i rarely frequent iHub anymore. you can reach me at [e-mail] if you want help with a specific question.

though it looks like you got the answer you were looking for.

i'm teaching an Excel class right now, and it's going well.

[BullNBear52]

Easy...

Change the language of the user interface or Help in Office programs
Show All
Hide All
To change the user interface or Help language, your organization must have purchased and distributed Microsoft Office 2003 Multilingual User Interface Pack, and it must be installed. For details, see your system administrator.

In Microsoft Windows XP, on the Windows Start menu, point to All Programs, point to Microsoft Office Tools, and then click Microsoft Office 2003 Language Settings.
In Windows 2000, on the Windows Start menu, point to Programs, point to Microsoft Office Tools, and then click Microsoft Office 2003 Language Settings.

On the User Interface and Help tab, do one of the following:
Change the user interface language

In the Display Office 2003 in box, click the language you want.
Change the Help language

In the Display Help in box, click the language you want.
Note Some Office components don't support changing the language of the user interface or Help. For more information, see the Microsoft Office 2003 Editions Resource Kit

[Wayne R]

I figured it out, thanks for the help. I needed to replace IF with SI

Really never occurred to me that excell could be language sensitive. How can you share data across the world?

[MSC290]

#NAME error message in Excel.

Here is what is listed for possible problems resulting in the #NAME error message. Check cell F2 for a possible entry error. Also check that there are no spaces in the =IF formula anywhere.

Good luck.

What does the error #NAME? mean?
The #NAME? error value occurs when Microsoft Excel doesn't recognize text in a formula.

Possible cause Suggested action
Deleting a name used in the formula, or using a name that does not exist. Make sure the name exists. On the Insert menu, point to Name, and then click Define. If the name is not listed, add the name by using the Define command.
Misspelling the name. Correct the spelling.
To insert the correct name in the formula, you can select the name in the formula bar, point to Name on the Insert menu, and then click Paste. In the Paste Name dialog box, click the name you want to use, and then click OK.

Using a label in a formula. On the Tools menu, click Options, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.
Misspelling the name of a function. Correct the spelling. Insert the correct function name into the formula by using the Formula Palette.
If the worksheet function is part of an add-in program, the add-in program must be loaded. For more information about using add-in programs, click .

Entering text in a formula without enclosing the text in double quotation marks. Microsoft Excel tries to interpret your entry as a name even though you intended it to be used as text. Enclose text in the formula in double quotation marks. For example, the following formula joins a piece of text "The total amount is " with the value in cell B50:
="The total amount is "&B50

Omitting a colon (:) in a range reference. Make sure all range references in the formula use a colon (:), for example, SUM(A1:C10).

[Wayne R]

well that sounds about right as far as the formula I used to have. But it doesn't work.

Looks like I have Excel 2000 9.0.2812

It's a french version, which shouldn't make any difference in the formulas, but when I enter that one it gives an error "#NOM?" Which I guess would be "#NAME?" on an english version.

[MSC290]

Try this Excel formula to see if it works for you. It worked on a test sheet here using Excel 2000.

=IF(F2="BUY",H2*-1,H2)

[Wayne R]

Simple question, at least I think it is;

http://www.investorshub.com/boards/read_msg.asp?message_id=19912795

[BullNBear52]

Anytime.

[mmayr]

Thanks B-N-B. I appreciate it. :) eom

[BullNBear52]

Click on File then Page setup.

Then under scaling,

Fit to 1 page wide by 1 page tall. Same window you can designate portrait or landscape.

[mmayr]

Lobo,

This is Mark from UCIrvine. I need to know how to fit a spreadsheet to an 8 1/2 sheet of paper. I can't seem to find the command to shrink one down so it fits the entire sheet whether I go in portrait or landscape. Any suggestions?

As always, your input is very much appreciated.

Best,

[berge]

HA...got another sucker!! lol...you'll enjoy them

[dropdeadfred]

I will, thanks :)

[berge]

search the net for asap utilities, best free addin out there. great tools

[dropdeadfred]

yeah Right, like I'm going to click on that

[berge]

a neat little file to download historic quotes from yahoo...you would need to enable macros,and this was done with excel 2003 enter the symbol into B4, hit enter, then the "download" button http://excel-stuff.xoompages.com/download_yahoo_historic2.zip
let me know if anyone tries it!

[apilgrim2]

The browser opens in Firefox but just the Excel hyperlinks go to IE.

Would appreciate it berge!!

Take care.........pilgrim

"When the character of a man is not clear to you, look at his friends." Japanese proverb

[berge]

strange, I've changed things all over the place, but I've always managed to have Firefox stay as my default. I'll see if anything else makes sense.

[apilgrim2]

Yes,

I have several Excel spreadsheets with hyperlinks to charts and once upon a time they opened in Firefox.

Now all of them open in IE. MS changed something and do not know what it was.

Have done several system restores to earlier dates to no avail.

I am stumped.

Take care.........pilgrim

"When the character of a man is not clear to you, look at his friends." Japanese proverb

[berge]

and it still opens links in IE?

[apilgrim2]

Yep, did that on downloading


firefox.

Take care.........pilgrim

"When the character of a man is not clear to you, look at his friends." Japanese proverb

[berge]

open your firefox tools--->option--->"main" tab, at the bottom is the system default for the browser. Hit the "check now" button, if not the default browser, set it

[apilgrim2]

berge, Thanks for looking

at this.

Do not know how, when or where but sometime on a new MS download the Excel hyperlinks 'got whacked'!!!

Only open in IE now and can't find a way to open in firefox. Went through the preferences in the settings and it still uses IE for the hyperlinks.

Would you have any idea what I can do. It was really convenient for a spreadsheet to have the firefox use.


Take care.........pilgrim

"When the character of a man is not clear to you, look at his friends." Japanese proverb

[berge]

I have firefox as my default browser, and all my hyperlinks open in firefox. The only thing I can't change to firefox is the browser used in web queries.

[berge]

you can conditionally format so the text or cell color changes depending on the value. Select the range, go to format-->conditional formatting, and play around. You can also use a macro to change cell colors on intervals...

[berge]

use these http://www.asap-utilities.com/ you can convert to all upper, all lower, capitalize the first letter, sort by color, you name it

[lobogotti]

very good! you utilized the MID function without it having been suggested. now let me show you another tool that is a life saver in these situations........

highlight column A

go to Data, go to Text To Columns, select fixed width, then scroll down and create your line breaks after every number....you would then click next, finished or just finished.

this will break up your data and the you can use a CONCATENATE to build it back together with the *'s.

Data, Text To Columns can be an EXTREMELY handy tool when dealing with exported info that isn't cooperative.

[MSC290]

Solutions for the problem:

#1 (the easy way) Go to the Learning Microsoft Excel board and post a message to lobogotti and say HELP!

#2 (the hard way) Tax your brain for quite some time doing numerous trials and errors to come up with the following formula to place in cell B1:

=CONCATENATE(MID(A1,1,1),"*",MID(A1,2,1),"*",MID(A1,3,1),"*",MID(A1,4,1),"*",MID(A1,5,1),"*",MID(A1,6,1),"*",MID(A1,7,1))

[lobogotti]

since you seem to pick this up rather well, let's move back to the first step. only this time i want to take those seven digit numbers and put a * in between every number.

for an example, they should look like this.........

6*4*9*7*3*2*1

now remember, i have 10,000 parts that i want to do this with and i need a quick way to achieve my deadline.

can you think of a way to accomplish this?

hint, you wouldn't use a LEFT or RIGHT command, but instead a two part function. CONCATENATE would be the second function. what would be the first? how can you break up all of those numbers?

[lobogotti]

very good.

that is Advanced Excel work. you should see some of the formulas i have typed up, one is 500+ characters long.

[MSC290]

Solution #2 for todays problem

Have not tweaked spreadsheet formulas for a long time so this was a fun project.

With your list of imported numbers in the "A" column starting at A1 the following formula can be entered into B1 and copied down:

=CONCATENATE(LEFT($A1,3),"-",RIGHT($A1,4))

To adjust placement of the "-" the ,3 and ,4 can be changed accordingly.

For example if you want the dash after the first two digits the formula changes to"

=CONCATENATE(LEFT($A1,2),"-",RIGHT($A1,5))

[lobogotti]

and no, i've really grown into Excel (Lotus was a little before I got interested in computers), but i understand the commands are close.

[lobogotti]

yes, that is correct, but only effective when using a 7 digit number, or if it will place the dash where needed for any other amount of characters.

nevertheless, perfect for this example and much quicker.

[MSC290]

Excellent lesson in spreadsheet functions!

Duplicated your results in a test using your instructions. It's not every day that one gets to CONCATENATE!

Here is another way that works with your 7 digit/character example.

Click on the A above the "A" column which will highlight the entire column of imported numbers.

Click Format>Cells...>Special>Phone Number>OK


Did you ever use any of the older Lotus programs with the HAL add-in? It could perform some amazing tasks. If you go one letter in the alphabet past H A L you get IBM!