“IF EXIST”-type formula

Topic

New Reply

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?

Reply | Quote

Replies

Lou,

I don’t know what your data or summary sheet look like but here’s one possibility:
[noparse]=IFERROR(INDIRECT(“WK”&$A2&”!B17″),0)[/noparse]
40316-LouSander

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

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Why dummy sheets??

=SUM(WK1:WK13,D4)

Reply | Quote

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

40332-Capture2

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.

Reply | Quote

There is always less confusion when you attach a file with before/after examples along with the logic you provided.

Reply | Quote

Lou,

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

[noparse]B2: =IFERROR(INDIRECT($A2&”!B17″),0)[/noparse] {Replacing B17 with the appropriate cell in your detail sheets.}

Once in B2 you can fill down.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Lou,

No problem: [noparse]=IFERROR(INDIRECT($A2&”!TotalSales”),0)[/noparse]

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

40351-LouScope

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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

40365-louudf

Test File: 40366-LouSander-IfExists-emulation-UDF-version

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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: [noparse]=Get_Summary2($A2,”TotalSales”) [/noparse]
Range of cells: [noparse]=Get_Summary($A2,”PAWinnings”)[/noparse]

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

The workbook about which I ask almost all my questions is a 5MB file that involves a large lottery pool. It’s just too big to share on this forum. When I need help with it, I could certainly strip out the names of the players and post it to some sort of cloud service where Loungers could download it and have a look.

I’ve never done anything like this, and in fact I usually have no need for the numerous free cloud storage options that I’m offered every few months. It looks like it might be a better way to get help, and it seems as though it would be easy enough to do.

What say you?

Reply | Quote

Lou,

Probably a good idea. I’d suggest dropbox. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote