Demo of Newsgroup Solution Capabilities

[Norman Harker]

Hi All!

I'm running a seminar to a group of 14 mixed background users and
during the next few hours will post various questions to demonstrate
to the delegates the power of Newsgroups as a resource.

I'll post under this thread and will avoid editing any question
raised.

Regards


Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi All!

First question from Patrick.

I've upgraded from Excel 97 to Excel 2002. I get red triangles all
over my old spreadsheet.

It's really annoying me. How can I get rid of them?

Patrick

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Peterson]

xl2002 has some extra features that help you find inconsistencies--things like
numbers typed as text, formulas that don't match the adjacent (usually above and
below) cells.

Even formulas that omit cells.

If the red triangles are in the top left corner, then this might be it. (I
think the default color is green, but it can be changed.)

Look at Tools|Options|Error Checking and you can toggle it off/on.

If the red triangles are in the top right corner, it sounds like comments.
(Like Paul wrote.) And you can toggle those by:
Tools|Options|View|Comments|choose your favorite option.

=======
Norman, you better post back with the actual solution!!!

 

[Norman Harker]

Jeff's Question

I have a problem with formatting cells. Some days I can select a range
and format and on other days I can't seem to be able to select the
cells and apply a new format. Excel refuses to allow my selection and
just goes back to the first cell I selected.

It happens on different computers.

Help!

Jeff

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Louis's Question!

I have membership for 1 year from a given date. How do I find the last
day of my membership. I must take into account Leap Years and adding
364 etc is not correct.

Why is Norman laughing!


Thanks

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[pfsardella]

A fairly open-ended question. Which should bring a wide variety of
answers.

David McRitchie's WebSite provides some info on how to get started;
the basics and some links to tutorials.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Using the macro recorder will teach you quite a bit. If you wish to
perform a set of operations, you start the macro recorder and do what
you want; move some data or format some cells. When you're done, you
can examine the code.

There are a number of books that provide info on creating solutions to
problems that people have already solved.

http://www.contextures.com/xlbooks.html

My basic assumption : If you are trying to solve a particular problem,
the chances are great that somebody else has already tried to deal
with circumstances similar to yours. 'Google' is your friend. This
link provides a 'Google' add-in that makes searching for answers to
your questions relatively easy.

http://www.rondebruin.nl/Google.htm

Here is some information that provides guidelines for searching and
posting to newsgroups.

http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/posting.htm
http://www.cpearson.com/excel/newposte.htm

Browsing the Excel newsgroups is an exercise in learning. Some very
experienced Excel individuals provide answers to questions that are
currently stumping others. You will see a wide variety of perspectives
in dealing with different problems.

And here are a few WebSites that have provided much useful
information.

http://www.add-ins.com/
http://www.j-walk.com/ss/
http://www.erlandsendata.no/english/topics.htm
http://www.bmsltd.co.uk/mvp/
http://www.mvps.org/dmcritchie/excel/xlindex.htm
http://www.cpearson.com/excel/ExcelPages.htm
http://www.geocities.com/jonpeltier/Excel/Charts/index.html
http://www.tushar-mehta.com/
http://www.contextures.com/tiptech.html
http://www.rondebruin.nl/index.html
http://www.bygsoftware.com/default.htm
http://archive.baarns.com/software/pages/solution.asp


HTH
Paul

 

[Debra Dalgleish]

You can learn by recording a macro as you perform actions in Excel. The
code is automatically created, and you can use it as is, or make a few
changes.

There are some instructions on the Microsoft site:

http://support.microsoft.com/default.aspx?scid=/support/excel/content/vba101/default.asp

 

[Dave Peterson]

I bet your worksheet isn't protected? If it is, then unprotect it.

The only time I've seen this is when the original file was created by a
different program--Lotus 123 or Crystal reports. Does that fit?

If yes, my work around was to select a big enough range that would allow
formatting. I'd change that range to the way I wanted that problem area
formatted. Then I'd come back and reformat my sacrificial cells (to what they
originally.)

I've seen this in xl97 more than xl2k. (But I inherited more Lotus files when I
was using xl97.)

Am I close?

 

[Dave Peterson]

I like this formula if the date is A1:

=date(year(a1)+1,month(a1),day(a1)-1)

Excel is pretty smart when it comes to dates. It can add one to the last day of
a month and get the first of the next month. If you give it the zeroth date of
a month, it's smart enough to go back to the last day of the previous month.

try it with stuff like:

=DATE(2003,12,0)
Dec 0, 2003 is really...

(Norman usually answers all the date questions.)

 

[Paul Corrado]

EDATE('"start date",12)-1

Though startdate+364 is a lot easier and won't cause any problems for
another few years and by then you'll probably have redone the worksheet
anyway.

PC

 

[John Wilson]

Dave,

=date(year(a1)+1,month(a1),day(a1)-1)

Cool...I like that one (and the fact that Excel will revert to the
the last day of the previous month if the result is zero).
I can use this myself.

zeroth ???

I was actually going to chastise you for misspeeling this but it's
in the dictionary (8 of them) and used in the context that you did.
http://www.onelook.com/

John

 

[Dave Peterson]

This better have been for humorous affect: misspeeling

Else the CQC will be all over you. (CQC=Canadian Quality Council)

(Hi, Deb!)

John Wilson wrote:

 

[Dana DeLouis]

Just a point to ponder. If one took out a membership on 2/29/2004, then the
use of "EDATE" returns 2/27/2005.
I don't know if a retail place would allow 2/28/2005 to be the appropriate
last day. (but they should...in my opinion)

 

[John Wilson]

Dave,

This better have been for humorous affect: misspeeling

That it was.
Had never seen the word "zeroth", was certain that it was wrong
and was just trying to insert a little humor into the thread. Had you
written "zero'th" (which isn't in any dictionary, by the way), I would
have understood it completely and not made a comment about it.
Fortunately, I did check the dictionary beforehand and to my amazement
found that "zeroth" was actually a word.
Ya' gotta' learn something new every day.
Oops, it's already a new day here so I can skate till tomorrow.

Take care,
John

 

[Paul Corrado]

Didn't think it mattered where I put the "-1" but Dana's point made me look
again and I think that this may solve that issue

=EDATE('startdate'-1,12)

PC

 

[Norman Harker]

Hi Dave!

No certainty on this one but from other comments my money is on trying
to work with a protected sheet!

A few delegates were a little stunned at how easy it was to get into
their protected worksheets! There's at least one large firm in Sydney
that's going to reviewing it's security. <vbg> I referred them to John
Green if they need a hand! (I break the windows. John puts in security
glass. Sounds like a good double act.)



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi All!

Michelle (and the others) were given the posting on "how to become an
Excel Expert" from a few months ago. The four replies were a good
backup to the objective of the day which was "How to solve Excel
Problems" rather than trying to cover specific skills.

JW and JG's sales in Sydney should be going up and we might find a lot
more questions come through from this area. I think that at least one
firm is going to have to learn to trust it's employees and allow
access to newsgroups. CDO allows it but my own view is that firms are
going to have to learn to live with it rather than use a complete
blocking system.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Peterson]

Severely off topic.

I saw Spellbound a couple of weeks ago. It's about spelling bees. The kids are
maybe 13 years old. I'm not sure I could spell any of the words in the
competition. (But some of the good luck messages were darn funny!)

 

[Norman Harker]

Hi Paul!

The solution: =EDATE(startdate-1,12) is neat but relies on Analysis
ToolPak and that appeared to be a pretty big issue.

DATE was agreed as acceptable as in most contexts the additional
function calls weren't regarded as an issue.

And didn't they all just love the solutions for last and first days of
a month plus other tricks. The Chip Pearson modified approach to
getting a robust schedule of dates a month or 3 months apart was also
seen as making setting up cash flow schedules with a variable base
date a lot easier.

The problem was of general importance to real estate people as it
determines the day that the tenant has to vacate the premises at the
end of a lease.

Zeroth! New to me as well. And it is new to the dictionary in Word as
well!

My laughing at this question was because of the rather nasty IF
function solution that was being used at present. One very happy
chappie at this solution. But he left pondering whether a lease
starting on 29th Feb 2004 ends on 27th Feb 2005 or 28th Feb 2005.

Precise date of vacating / final day of legal rights and
responsibilities can be a serious issue and with variable lease terms
(month to month, year to year) it's not always a question of simply
specifying a date.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Dave!

Apart from the source of the solution this thread also demonstrated
the camaraderie of the newsgroups and the fact that it was just a
source of "Excel geek" help.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.