# Thread: "IF EXIST"-type formula

1. ## "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?

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

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

4. Why dummy sheets??

=SUM(WK1:WK13,D4)

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

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

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

Lou Sander (2015-04-24)

8. 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. There is always less confusion when you attach a file with before/after examples along with the logic you provided.

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

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

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

Lou Sander (2015-04-24)

13. ## 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.

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

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

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

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

Page 1 of 2 12 Last

#### Posting Permissions

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