I have a macro that I am running that adds sheets based on a list. I
have a summary sheet that has formulas based on the names. Without
getting the REF# error, is there anyway I can refer to a sheet that has
not been created?

Example formula: ='john smith'!D2:D17

John smith will not have a sheet until after I run the macro (to create
from the list).

I want the code to add the cells based on the condition that John Smith
sheet exist. If not, just remain blank.

Any help would be appreciated....

Thanx,

What about If(iserr(sum('john smith'!D2:D17),"",sum('john
smith'!D2:D17) as your formula.

J.W. Aldridge wrote:
I have a macro that I am running that adds sheets based on a list. I
have a summary sheet that has formulas based on the names. Without
getting the REF# error, is there anyway I can refer to a sheet that has
not been created?

Example formula: ='john smith'!D2:D17

John smith will not have a sheet until after I run the macro (to create
from the list).

I want the code to add the cells based on the condition that John Smith
sheet exist. If not, just remain blank.

Any help would be appreciated....

Thanx,

Seems like a pretty funny example formula.

=IF(ISERROR(CELL("address",INDIRECT("'john smith'!a1"))),"missing",
indirect("'john smith'!d2:d17"))

Change missing to what you want to see instead ("" maybe???).



"J.W. Aldridge" wrote:

I have a macro that I am running that adds sheets based on a list. I
have a summary sheet that has formulas based on the names. Without
getting the REF# error, is there anyway I can refer to a sheet that has
not been created?

Example formula: ='john smith'!D2:D17

John smith will not have a sheet until after I run the macro (to create
from the list).

I want the code to add the cells based on the condition that John Smith
sheet exist. If not, just remain blank.

Any help would be appreciated....

Thanx,

--

Dave Peterson

I couldn't get the first recommended forumula to take. The "" part kept
highlighting.

=If(iserr(sum('john smith'!D2:D17),"",sum('john smith'!D2:D17)


I got Dave's formula to return a value when I made it an array formula.
But I cant figure where to SUM would go so it would add the cell range
d2:d17.


Thanx.

Sorry, missing right bracket. Try:

=If(iserr(sum('john smith'!D2:D17)),"",sum('john smith'!D2:D17)

J.W. Aldridge wrote:
I couldn't get the first recommended forumula to take. The "" part kept
highlighting.

=If(iserr(sum('john smith'!D2:D17),"",sum('john smith'!D2:D17)


I got Dave's formula to return a value when I made it an array formula.
But I cant figure where to SUM would go so it would add the cell range
d2:d17.


Thanx.

Thanx both of you....

The last one worked like a charm!

Are you bothered by the dialog you get that asks you where that non-existent
sheet is?



"J.W. Aldridge" wrote:

Thanx both of you....

The last one worked like a charm!

--

Dave Peterson

Or when you reopen the workbook?

Dave Peterson wrote:

Are you bothered by the dialog you get that asks you where that non-existent
sheet is?

"J.W. Aldridge" wrote:

Thanx both of you....

The last one worked like a charm!

--

Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
Are you bothered by the dialog you get that asks you where that non-existent
sheet is?



The formulas I had in place returned the REF# error because the sheets
were not yet created. I was bothered by that error, but also by the
fact that when the sheet was actually added, the formula's reference to
that sheet would change to REF# as well. However, like I said above....
that last formula seemed to work out for me.

Thanx.