Is Tyler Nash on the Board who can read my question？

[chem888]

Dear Sir,

I have questions about the book "Don't fear the spreadsheet" by Tyler Nash. I follow her instructions in that Book to join this Forum. I would like to ask her Excel technical questions strictly related to that Book and nothing else. If she is not the Board member and not available to reply, can I have her email address so that I can ask her directly? I am a little better than Level 1 beginners Level, I suppose editors are Excel gurus of Level 5 or higher, only Tyler knew her projects or case studies. Unfortunately I can't find her email address on that Book, and she
did not indicate any way to communicate with her. May be she want to keep her privacy and don't want to be disturbed by readers? I just want to ask her questions only related to her case studies and will NOT ask her privacy or unrelated issues. Is it possible to reach her?

Thanks and Regards,

chem888 4-20-2014

 

[mole999]

write to her publisher with your exact question. you could of course (maybe) address the same question in the lounge

 

[Smitty]

Welcome to the Board!

I forwarded this onto the board's admins to have them let Tyler know you're trying to get in touch with him.

 

[chem888]

Dear Mole999 and Board Member:

Thanks for your kind help. Her publisher is "Holy Macro! Books", no email address available, and no physical address either, only has PO Box number as:

PO BOX 82, Union town OH 44685 USA

I wondered if anyone knew the Publisher's email address? Email communication is way faster than snail mails. If no one knows or the Publisher does not

have email address(Hard to believe), then I have to write snail mails and mail to the Publisher. The question is related to her Case Study 3 in that Book,

it's too long and I have only a minor question at the end of her Case Study3, it's a Beginner's Level 1 or level 2 question, not fitting level 5 (one who can

write VBA codes) or Excel gurus. I don't want to waste your precious time to read the whole case study and smile if not laugh out loud.

Anyone know about Publisher "Holy Macro! Books" email address?

Thanks and Regards,

chem888 4-21-2014

 

[Smitty]

Mr. Excel is the publisher.

Like I said, I reached out to have Tyler get in touch with you.

 

[chem888]

Dear Smitty(MrExcel MVP & Moderator):


Thank you for your kind help forwarding my question to Board Admins.
It look like the Board Admins are very busy, there are 4 digit replies
shown on the person forum. I don't blame them if it will take several
days for Board Admins to process, even it can reach Tyler Nash, she
may not be able to reply me if she is busy too. Anyway I appreciate
your efforts. If after several weeks there is no response, then I will
write a snail mail to the Publisher: "The Holy Macro! Books", ask
them to forward my question to Tyler Nash. The Publisher only　
have PO BOX 82 , Uniontown, OH 44685 shown on the 1st page
of the Book "Don't Fear the Spreadsheet", that's the best I can do.


Thanks and Regards,


chem888 4/21/2014

 

[Smitty]

Tyler's gotten your message, and should respond to you soon.

 

[chem888]

Dear Smitty:


Thanks for your reply stating that Tyler Nash got my message and she should

respond to me soon on 4-21-2014. However about 3 weeks passed, I did not

receive any message from her. May be she want to keep her privacy and didn’t

want to reply to the readers? I don’t blame her at all. Here is my question about

her Case Study 3 at the end of the Book “Don’t Fear the Spreadsheet”:

On the Case Study 3 (page 196) of the Tyler Nash "Don't Fear the Spreadsheet",

she demonstrated how to Track Billable Hours in Weekly Time Worksheet,

this projects incorporates many good stuffs including VLOOKUP function.

She said she need to know how much she can make each week so that she can

afford to "buy new shoes or not ", she figured out the weekly Total $ Amount

she made each week on Cell H1 so that she can read right away. However if

she would like to buy Jimmy Choo High Heel Shoes, it's convenient to create

one more worksheet as this year's Total Earning to add each week Earning

altogether, designed as Open ended Sum so that she knew how much she make

up to this year right away. Correct? I can create a worksheet so that I copy and Paste

each week's Earning to that "Annual" worksheet and sum it, but it cost me too much

time to do that. Is there any way when I finished weekly total earnings, it will

automatically figure out on the Annual Worksheet? I am thinking about using

VLOOKUP Function, but I don't know how to use it across worksheets for

each week? I hope Tyler Nash can read this question. If she does not want to reply,

Could you or somebody else help me on this?


Thanks and Regards,


chem888 5/9/2014

 

[Smitty]

I just found out that Tyler is finishing school, so I reached out to one of the other authors.

 

[zorvek]

Chem,

I'm one of the authors of the book. In order to do what you want, you will have to add a summary worksheet and then do your lookups and sums off of that worksheet. Trying to do the work with three-dimensional formulas is challenging but I will provide one example below just for giggles.

To implement the simple solution, we're going to create a new worksheet and list the weekly totals for every week of the year. In A1 and B1 enter "Week" and "Total". In A2 enter the date 7 Jan 2015 - this is the end day of the first week of the year 2015. In cell A3 enter the formula "=A2+7" and copy the formula down to A54. Enter this formula in B2 "=IFERROR(INDIRECT("'"&TEXT(A2,"MMM D")&"'!H1"),0)" and copy down to B54. You now have a single list of every week and every week's total. You can now use more traditional formulas to sum and lookup various weeks' totals.

Using a 3-D (multiple worksheet) formula requires some tricky stuff. Assuming you want to sum the weekly totals for a period spanning one or more weeks, you can use the following 3-D array formula (enter by pressing CTRL+SHIFT+ENTER):

=SUM(IFERROR(N(INDIRECT("'"&TEXT(DATE(2015,1,7)+(ROW(1:53)-1)*7,"MMM D")&"'!H1")),0)*IFERROR(N(INDIRECT("'"&TEXT(DATE(2015,1,7)+(ROW(1:53)-1)*7,"MMM D")&"'!A2"))>=A1,0)*IFERROR(N(INDIRECT("'"&TEXT(DATE(2015,1,7)+(ROW(1:53)-1)*7,"MMM D")&"'!A2"))<=A2,0))

The formula assumes that the first week ends on 7 Jan 2015, and the first week to sum is in A1 and the last week to sum is in A2. It will sum the totals for the weeks from the first week to the last week inclusive. It also assumes that the end day of each week is in that worksheet's cell A2. In the example in the book, cell A2 contains the value "Week Ending: June 10, 2015". This implies that a text value is entered into that cell which won't work with the above formula. To show the same value in the cell but enter a real date in that cell, enter the period's end date and format the cell with a custom format: "[$Week Ending: ]mmm d, yyyy"

All that said, I really don't like how the workbook in the example is designed. I prefer to design my workbook solutions more like I design my database solutions: I would use only one worksheet for ALL of my hours logged across all weeks, months, and years. This way I don't have to worry about making new worksheets every period, and analysis (sums, lookups, PivotTables, charting) becomes extremely simple. This is how the pros do it

As far as who did what in the book, Tyler came up with the idea of the book and presented a list of over 100 Excel questions to Bill Jelen, Tom Utis, and me. Bill, Tom, and I wrote the answers to Tyler's questions.

Kevin