West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn

Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike
Alexander (Jon P did not make it). The best tip I have is for you guys to buy
my book (tips) called "This isn't Excel, it's Magic"
(http://www.iil.com/iil/excelmagic). My tips & tricks presentation had tips
from this book. But, since you ask, I'll share a tip I just learned today!
Enter this in a cell:
=SUM('*'!A1) (that's single quotes around the *)
and then re-examine the formula. Sums all A1's from all sheets except the
sheet it was entered on.
Bob Umlas

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn

Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see

1) On entering the formula on Sheet 1, Excel immediately converts the '*' to
Sheet2:Sheet3
2) Excel immediately crashes

Tried it twicee with identical results

Using Win 2k and Office XP Pro

What's your configuration?


"Bob Umlas, Excel MVP" wrote:

Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike
Alexander (Jon P did not make it). The best tip I have is for you guys to buy
my book (tips) called "This isn't Excel, it's Magic"
(http://www.iil.com/iil/excelmagic). My tips & tricks presentation had tips
from this book. But, since you ask, I'll share a tip I just learned today!
Enter this in a cell:
=SUM('*'!A1) (that's single quotes around the *)
and then re-examine the formula. Sums all A1's from all sheets except the
sheet it was entered on.
Bob Umlas

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn

Win XP, Office XP SBE (all updates/patches applied)

The first time I tried it worked OK. Sheet names: Sheet1, Sheet2, Sheet3.
Formula entered on Sheet1.

The second time I tried it:

I deleted the formula

I renamed Sheet2 --- Sheet5

Re-entered the formula

Excel crashed!

Biff

"Duke Carey" wrote in message
...
Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see

1) On entering the formula on Sheet 1, Excel immediately converts the '*'
to
Sheet2:Sheet3
2) Excel immediately crashes

Tried it twicee with identical results

Using Win 2k and Office XP Pro

What's your configuration?


"Bob Umlas, Excel MVP" wrote:

Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike
Alexander (Jon P did not make it). The best tip I have is for you guys to
buy
my book (tips) called "This isn't Excel, it's Magic"
(http://www.iil.com/iil/excelmagic). My tips & tricks presentation had
tips
from this book. But, since you ask, I'll share a tip I just learned
today!
Enter this in a cell:
=SUM('*'!A1) (that's single quotes around the *)
and then re-examine the formula. Sums all A1's from all sheets except the
sheet it was entered on.
Bob Umlas

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your
latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than
generous. Thank you!

Have a good weekend!

Epinn

Lori posted that it crashes xl2002. But it works ok in xl2003.

Duke Carey wrote:

Well, Bob, I tried that little =SUM('*'!A1) trick and this is what I see

1) On entering the formula on Sheet 1, Excel immediately converts the '*' to
Sheet2:Sheet3
2) Excel immediately crashes

Tried it twicee with identical results

Using Win 2k and Office XP Pro

What's your configuration?

"Bob Umlas, Excel MVP" wrote:

Actually, the 3 presenters were Bob U (me), Damon Longworth, and Mike
Alexander (Jon P did not make it). The best tip I have is for you guys to buy
my book (tips) called "This isn't Excel, it's Magic"
(http://www.iil.com/iil/excelmagic). My tips & tricks presentation had tips
from this book. But, since you ask, I'll share a tip I just learned today!
Enter this in a cell:
=SUM('*'!A1) (that's single quotes around the *)
and then re-examine the formula. Sums all A1's from all sheets except the
sheet it was entered on.
Bob Umlas

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn





--

Dave Peterson

And another question -- why weren't YOU there?
:-)

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn

Did you pick this up from our earlier thread today:

http://groups.google.com/group/micro...c6c50bd920e56c

or is it just coincidence? Happy to have found a trick many others are
not aware of - not even Bob...

Bob wrote:
And another question -- why weren't YOU there?
:-)

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn

I shared it with others in another forum.

Bob saw that shared post.

Lori wrote:

Did you pick this up from our earlier thread today:

http://groups.google.com/group/micro...c6c50bd920e56c

or is it just coincidence? Happy to have found a trick many others are
not aware of - not even Bob...

Bob wrote:
And another question -- why weren't YOU there?
:-)

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn





--

Dave Peterson

Thank you all for your contribution. Good timing as I have lately been learning how to add values across worksheets.

This trick won't add the sheet that the formula is in. So, I place the formula sheet in between the sheets I want to add up. This is what I have discovered.

Open a brand new workbook. Have the three sheets as default. Insert a blank new sheet before Sheet3. So, I have Sheet1, Sheet2, Sheet4 and Sheet3 (in this order). I enter the formula into a cell in Sheet4.

=SUM('*'!A1)

It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4.

Wrong!

I have learned that to avoid potential future problem, I must place the formula/summary sheet either in the beginning or at the end of the sheets to be added up. Secondly, I must double check the formula when I drag the sheet(s) around.

I have learned more than a new trick. Thanks again.

Anyone else has something else to share? The more, the better.

Epinn

"Dave Peterson" wrote in message ...
I shared it with others in another forum.

Bob saw that shared post.

Lori wrote:

Did you pick this up from our earlier thread today:

http://groups.google.com/group/micro...c6c50bd920e56c

or is it just coincidence? Happy to have found a trick many others are
not aware of - not even Bob...

Bob wrote:
And another question -- why weren't YOU there?
:-)

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn





--

Dave Peterson

I tested the wildcard tip by Lori and I say "neat!"

Sheet names a T1, nT234e, T555

Formula is = sum('T*'!A1)

Only T1 and T555 are picked up.

Epinn

"Epinn" wrote in message ...
Thank you all for your contribution. Good timing as I have lately been learning how to add values across worksheets.

This trick won't add the sheet that the formula is in. So, I place the formula sheet in between the sheets I want to add up. This is what I have discovered.

Open a brand new workbook. Have the three sheets as default. Insert a blank new sheet before Sheet3. So, I have Sheet1, Sheet2, Sheet4 and Sheet3 (in this order). I enter the formula into a cell in Sheet4.

=SUM('*'!A1)

It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4.

Wrong!

I have learned that to avoid potential future problem, I must place the formula/summary sheet either in the beginning or at the end of the sheets to be added up. Secondly, I must double check the formula when I drag the sheet(s) around.

I have learned more than a new trick. Thanks again.

Anyone else has something else to share? The more, the better.

Epinn

"Dave Peterson" wrote in message ...
I shared it with others in another forum.

Bob saw that shared post.

Lori wrote:

Did you pick this up from our earlier thread today:

http://groups.google.com/group/micro...c6c50bd920e56c

or is it just coincidence? Happy to have found a trick many others are
not aware of - not even Bob...

Bob wrote:
And another question -- why weren't YOU there?
:-)

"Epinn" wrote:

West Coast Excel User Conference
October 25 2006 - October 27 2006

I know Bob U. and Jon P. were presenters.

Did anyone else attend the conference?

If yes, wound you mind sharing with the group

one favourite tip that you took away.

As a matter of fact, anyone who didn't go but has an urge to tell your latest "discovery," you are more than welcome.

Look forward to a sharing spirit. The experts have been more than generous. Thank you!

Have a good weekend!

Epinn





--

Dave Peterson

After you hit enter, excel expanded this:
=SUM('*'!A1)
to:
=SUM(Sheet1:Sheet2!A1,Sheet3!A1)

After you drag sheet3 after sheet1, your formula is still:
=SUM(Sheet1:Sheet2!A1,Sheet3!A1)

Which means to add all the values in A1 in all the sheets between sheet1 and
sheet2 (including sheet3 since in that "sandwich" of sheets), then add Sheet3's
A1 one more time.

It may not be what you expect, but the formula evaluates to the correct (but not
your intended) sum.

It's not too unlike this in B1:
=sum(a1:a5,a3)

It adds up just fine, but it may not be what you wanted.

Epinn wrote:
<<snipped
It is pretty smart that it adds up A1 in Sheet1,Sheet2 and Sheet3 even though they are not in sequence/contiguous. I get the correct result. Then I drag Sheet3 to after Sheet1 and end up with Sheet1,Sheet3,Sheet2 and Sheet4. Check the result again on Sheet4.

Wrong!
<<snipped
--

Dave Peterson