Tip(s) from Excel User Conference

E

Epinn

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
 
B

Bob Umlas, Excel MVP

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
 
D

Duke Carey

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?
 
B

Biff

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
 
E

Epinn

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

I shared it with others in another forum.

Bob saw that shared post.
 
E

Epinn

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

Sheet names are: T1, nT234e, T555

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

Only T1 and T555 are picked up.

Epinn

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

I shared it with others in another forum.

Bob saw that shared post.
 
D

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:
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!
Click to expand...
<<snipped>>
 
E

Epinn

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

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:
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!
Click to expand...
<<snipped>>
 
D

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

Epinn

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

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

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

Epinn

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) (Format>number 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

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