Formula to search for worksheet name, then calculate

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

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.
 
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???).
 
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)
 
Are you bothered by the dialog you get that asks you where that non-existent
sheet is?
 
Dave said:
Are you bothered by the dialog you get that asks you where that non-existent
sheet is?
Click to expand...

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.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine two spreadsheets into one 3
switch to another worksheet, then return to original sheet 2
Macro to complete Google search based on a cell? 0
Can you Filter using names from a generated list??? 1
Copy adjacent Sheet and name from a list 42
Formula in a Text Box 2
Macro to run based on criteria 2
Search , then copy and paste from multiple worksheets. VBA help 1

Back
Top