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

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

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

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

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

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

Yes, I was aware of the logic behind the "wrong" result before I posted.

When we drag down a cell containing a formula, the relative address is changed.

I think this is different and there is no way that Excel knows how to readjust. ;)

Next time, I'll say it is not the result that I have expected but ......

Epinn

"Dave Peterson" wrote in message ...
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

This type of "sandwich" can be useful.

I like to have two worksheets named Start and End surrounding the worksheets
that I want to add (say).

Then I can do:
=sum(start:end!a1)

And I can move worksheets into that sandwich or move worksheets out of the
sandwich. It makes playing what-if games pretty nice.



Epinn wrote:

Yes, I was aware of the logic behind the "wrong" result before I posted.

When we drag down a cell containing a formula, the relative address is changed.

I think this is different and there is no way that Excel knows how to readjust. ;)

Next time, I'll say it is not the result that I have expected but ......

Epinn

"Dave Peterson" wrote in message ...
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

--

Dave Peterson

Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets?

Thank you for sharing.

Epinn

"Dave Peterson" wrote in message ...
This type of "sandwich" can be useful.

I like to have two worksheets named Start and End surrounding the worksheets
that I want to add (say).

Then I can do:
=sum(start:end!a1)

And I can move worksheets into that sandwich or move worksheets out of the
sandwich. It makes playing what-if games pretty nice.



Epinn wrote:

Yes, I was aware of the logic behind the "wrong" result before I posted.

When we drag down a cell containing a formula, the relative address is changed.

I think this is different and there is no way that Excel knows how to readjust. ;)

Next time, I'll say it is not the result that I have expected but .......

Epinn

"Dave Peterson" wrote in message ...
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

--

Dave Peterson

I don't. But I know many like to.

If I hide those sheets, then it gets less obvious what's happening. I will lock
all the cells and protect the sheet, though. I don't want someone (me!)
changing a cell that affects my results.

Epinn wrote:

Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets?

Thank you for sharing.

Epinn

"Dave Peterson" wrote in message ...
This type of "sandwich" can be useful.

I like to have two worksheets named Start and End surrounding the worksheets
that I want to add (say).

Then I can do:
=sum(start:end!a1)

And I can move worksheets into that sandwich or move worksheets out of the
sandwich. It makes playing what-if games pretty nice.

Epinn wrote:

Yes, I was aware of the logic behind the "wrong" result before I posted.

When we drag down a cell containing a formula, the relative address is changed.

I think this is different and there is no way that Excel knows how to readjust. ;)

Next time, I'll say it is not the result that I have expected but ......

Epinn

"Dave Peterson" wrote in message ...
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

--

Dave Peterson

--

Dave Peterson

Dave, Good idea. I think I'll do the same.

Does anyone want to know what I have learned today?

Bob U. mentioned that Mike Alexander was one of the presenters. I found out from his web site the function that was used to calculate age. I have always liked DATEDIF but Mike used one function that I didn't even know exists.

DAYS360( ) <--- I haven't come across this function on this board.

=IF(D8="","",DAYS360(D8,D2)/360) (Formatnumber decimal places = 0)

D8 = date of birth D2 = today's date.

DAYS360 is based on a 30-day month for 12 months and leap year February is within 30 days.

Any comments on DAYS360( )?

Epinn

"Dave Peterson" wrote in message ...
I don't. But I know many like to.

If I hide those sheets, then it gets less obvious what's happening. I will lock
all the cells and protect the sheet, though. I don't want someone (me!)
changing a cell that affects my results.

Epinn wrote:

Yes, totally agree. I only learned about this a couple of days ago. Do you usually hide the dummy sheets?

Thank you for sharing.

Epinn

"Dave Peterson" wrote in message ...
This type of "sandwich" can be useful.

I like to have two worksheets named Start and End surrounding the worksheets
that I want to add (say).

Then I can do:
=sum(start:end!a1)

And I can move worksheets into that sandwich or move worksheets out of the
sandwich. It makes playing what-if games pretty nice.

Epinn wrote:

Yes, I was aware of the logic behind the "wrong" result before I posted.

When we drag down a cell containing a formula, the relative address is changed.

I think this is different and there is no way that Excel knows how to readjust. ;)

Next time, I'll say it is not the result that I have expected but .......

Epinn

"Dave Peterson" wrote in message ...
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

--

Dave Peterson

--

Dave Peterson