Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    "IF EXIST"-type formula

    I have a workbook that includes, among other things, anywhere from 1 to 13 worksheets, each pertaining to a given week in a calendar quarter.

    The first week in the quarter, I create a sheet named WK1. During that week, I enter data for that week.

    The second week, I create sheet WK2 and enter its data. The third week I do the same for WK3, and so on through WK13. For various reasons, I do not want to create all the sheets ahead of time.

    I'm looking for a way to collect data from each week's sheet in one summary sheet in my workbook. As I envision it now, that summary sheet will contain references to WKn sheets that do not yet exist. I can see that my summary sheet might, in that case, be a nightmare that contains a bunch of cells that produce errors.

    Is there some way for Excel to detect whether a sheet exists before it fills a cell on the summary sheet?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    I don't know what your data or summary sheet look like but here's one possibility:
    =IFERROR(INDIRECT("WK"&$A2&"!B17"),0)
    LouSander.JPG

    As always sample workbooks go a long way to getting it right the first time.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Lou

    Using a 'sum-across-sheets' method that RG recently posted, you could use a similar method.
    See attached file.
    (Uses 'dummy-hidden' sheets to anchor your start and end points for the sheet range to sum across.)

    zeddy
    Attached Files Attached Files

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Why dummy sheets??

    =SUM(WK1:WK13,D4)
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm not getting it. I didn't explain in enough detail. My summary sheet just picks up some data from the various WKn sheets. It doesn't need to perform any operations.

    The data being picked up for each week is an entry from TUE and one from WED, as shown below. (Actually there's also one from FRI, and one from SAT for each week, and there are actually several items for each day, which would be in additional columns to the left of Column B. I left that stuff out in the interest of simplicity.)

    Capture2.PNG

    I can easily plug the appropriate cell references into the summary sheet. My concern is what might happen if the summary sheet is looking for data from WK8 when that sheet doesn't yet exist. When I posted the question, I had no idea if this could even be dealt with. It looks like it CAN be dealt with, but I can't tell how by looking at the posts so far. My fault, I'm sure.
    Attached Images Attached Images
    Last edited by Lou Sander; 2015-04-23 at 20:16. Reason: I uploaded the wrong picture. Sheesh! I can't get rid of the old one.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    My post #2 should do what you want with a minor change visa-vie your post #5.

    B2: =IFERROR(INDIRECT($A2&"!B17"),0) {Replacing B17 with the appropriate cell in your detail sheets.}

    Once in B2 you can fill down.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2015-04-24)

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi RG

    Your IFERROR and INDIRECT function solves Lou's issue with 'missing' week sheets.

    Don: Why dummy sheets? Lou said in post#1 that he didn't want to create all the sheets ahead of time. So sheet [WK13] might not exist yet, so formula =SUM(WK1:WK13,D4) won't work. Although I included that sheet [WK13] in my post#3, you could delete that sheet [WK13] and the formulas on sheet [QTR1] would not be affected.

    zeddy

  9. #8
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    There is always less confusion when you attach a file with before/after examples along with the logic you provided.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  10. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    RG's Post #6 solved my problem. I spent quite a while fooling around with it until I finally figured out how it worked. That stuff was a lot easier 20 years ago.

    One more thing: Though I can implement this fix by using Row and Column references for the cells in the various WKn sheets I want to summarize, every one of the cells actually has a name. The names are consistent from sheet to sheet. For example, cell M3 on each of the WKn sheets is always named MAAnnuityValue.

    I'm pretty familiar with the names and what they refer to, and they are part of some pretty complicated WKn sheets. It would be MUCH easier on my end if I could use the names in the formulas on the summary sheet. Some of the Excel help seems to indicate that this is possible, but I haven't been able to figure it out. Too many quotation marks and parenthesis for me to deal with at my advanced age, I'm afraid. Or maybe I'm just getting stupider.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    No problem: =IFERROR(INDIRECT($A2&"!TotalSales"),0)

    Just make sure that ALL the references to the same name have Worksheet SCOPE and you don't have one with Workbook Scope!

    LouScope.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2015-04-24)

  13. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Well, here's a problem

    I use this workbook on two computers. One is running Excel 2010 and the other Excel 2003. The summary sheet doesn't seem to work on the Excel 2003 machine.

    Where the formulas discussed above appear, the Excel 2003 machine shows a #NAME? error in the cell, and shows _xlfn. right after the equals sign in the formula.

    I'm guessing that this has to do with something in the formula not being available on Excel 2003. I'm not really eager to put Excel 2010 on the older machine. If the only problem is that the summary sheet with these new formulas doesn't work with 2003, I suppose I could live with it.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Yes the IFERROR function was new in XL2007!

    Here's a UDF (User Defined Function) that will do the trick either place:

    Code:
    Option Explicit
    
    Function Get_Summary(zShtName As String, zRangeName As String) As Double
    
       On Error GoTo NoSheet
       
       Get_Summary = Sheets(zShtName).Range(zRangeName).Value
       
       GoTo NormalExit
       
    NoSheet:
      
      Get_Summary = 0
      
    NormalExit:
    
    End Function
    louudf.jpg

    Test File: LouSander -IfExists emulation UDF version.xls

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #13
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Good stuff! I think I will keep the UDF in my bag of tricks for a while, pending putting Excel 2010 on the other computer. I've forgotten the specific reasons I want to keep Excel 2003 alive -- that's probably a good indication that they aren't important any more.

    I've been building my Summary Sheet, and it's going pretty well. I'm up to column Q and row 31. I am VERY thankful that I put so many named cells into the WKn sheets. It facilitates plugging their values into the Summary Sheet without making misteaks.

    I've run into one snag, to which maybe there's a solution:

    While most of what I want to put on the summary sheet is contained in one named cell on the WKn sheets, there's one item that's actually contained in a named range.

    Normally the Summary Sheet has a cell like =IFERROR(INDIRECT($A3&"!MAAnnuityValue"),"No Sheet"), which, if the referenced WKn sheet exists, shows MAAnnuityValue from that sheet. If the sheet doesn't exist, it shows No Sheet.

    Now I'm wanting to use SUM(PAWinnings) in a cell, instead of MAAnnuityValue. I've played with it a bit, with no luck.

    I can always just put a named cell containing SUM(PAWinnings) into each of the WKn sheets, then reference them in the Summary Sheet, but I have a slight preference not to clutter them with stuff like that.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    OK this will handle either case:

    Code:
    Option Explicit
    
    Function Get_Summary(zShtName As String, zRangeName As String) As Double
    
       On Error GoTo NoSheet
       
       Get_Summary = WorksheetFunction.Sum(Sheets(zShtName).Range(zRangeName))
       
       GoTo NormalExit
       
    NoSheet:
      
      Get_Summary = 0
      
    NormalExit:
      
    
    End Function
    Single cell: =Get_Summary2($A2,"TotalSales")
    Range of cells: =Get_Summary($A2,"PAWinnings")

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  17. #15
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'm just going to put a cell into each WKn that shows Sum(PAWinnings), etc. Now that I've looked closer, there are already some similar cells there, tucked away in a corner.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Page 1 of 2 12 LastLast

Posting Permissions

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