+ Reply to Thread
Results 1 to 3 of 3

Setting formula in template with reference to main workbook

  1. #1
    Registered User
    Join Date
    03-21-2008
    Posts
    52

    Setting formula in template with reference to main workbook

    Hi guys!

    I hope someone can help me because this is driving me nuts!

    I'm making a spreadsheet with a column in which calculates an average of values across all worksheets by summing 2 figures and then dividing one by the other (in this case leads/hours to calculate average leads per hour). I've done this by creating a Start sheet and a Spacer sheet and then hiding them as the first and last sheets, and then summing with them as the start and end values. I have also figured in the dreaded dividing by zero error, so my formula looks like this:

    =IF(ISERROR(SUM(Start:Spacer!Z4)/SUM(Start:Spacer!AA4)), "",SUM(Start:Spacer!Z4)/SUM(Start:Spacer!AA4))

    This works fine. My problem is that I cannot create a template or save as a worksheet with these values because of the reference to the Start and Spacer sheets. My additional problem is that I need to email this spreadsheet to other offices and have it work out of the box, so creating a reference to the spreadsheet in the template is problematic down the line.

    HELP!!!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote Originally Posted by monkdelafunk
    Hi guys!

    I hope someone can help me because this is driving me nuts!

    I'm making a spreadsheet with a column in which calculates an average of values across all worksheets by summing 2 figures and then dividing one by the other (in this case leads/hours to calculate average leads per hour). I've done this by creating a Start sheet and a Spacer sheet and then hiding them as the first and last sheets, and then summing with them as the start and end values. I have also figured in the dreaded dividing by zero error, so my formula looks like this:

    =IF(ISERROR(SUM(Start:Spacer!Z4)/SUM(Start:Spacer!AA4)), "",SUM(Start:Spacer!Z4)/SUM(Start:Spacer!AA4))

    This works fine. My problem is that I cannot create a template or save as a worksheet with these values because of the reference to the Start and Spacer sheets. My additional problem is that I need to email this spreadsheet to other offices and have it work out of the box, so creating a reference to the spreadsheet in the template is problematic down the line.

    HELP!!!
    Can you expand on this please? There seems no reason why you can't hide these sheets, save and close the workbook (or template) and then re-open it with the same functionality. I've just tested this on my (admittedly a Mac) but it works OK for me.

    Exactly what problems do you encounter? You say you can't save it because of these hidden sheets which I find inexplicable.

    Rgds

  3. #3
    Registered User
    Join Date
    03-21-2008
    Posts
    52
    Sorry, I was stressing out when I wrote the original post!

    The problem I'm having is to save a single sheet as a template or a worksheet to drop in to the workbook at the start of a new week.

    For example I have the workbook template with my 2 hidden sheets (Start and Spacer) and the first week's blank sheet in it. The first weeks sheet contains the formula mentioned above. If I then try to save the first week's sheet as an Excel Worksheet (so I can drop a new one in at the start of a new week) I get the following error:

    Cannot save to that file format. There are references to other sheets in this workbook. Copy this sheet to a different workbook first, then try again.


    I assume this is beacuse of the above formula.

    If I try simply to create a one page template to drop in at the start of a new week it will not allow the formula because it refers to worksheets that are not present.

    I guess I can just leave a blank template in the workbook to copy at the start of the week, I'm just surprised that what I'm trying to do is proving so difficult!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1