How can I use an IF function to look for a worksheet name and then look for a
specific cell and return the value in that cell. And if the worksheet is not
found, return a value of zero?
Example: Worksheet AB, cell B1, if not found return value of zero.

=IF(ISERROR(INDIRECT("'Worksheet AB'!B1")),0,INDIRECT("'Worksheet AB'!B1"))

Note the single quoation marks around the sheet name.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"wcurtis" wrote:

How can I use an IF function to look for a worksheet name and then look for a
specific cell and return the value in that cell. And if the worksheet is not
found, return a value of zero?
Example: Worksheet AB, cell B1, if not found return value of zero.

Hi,

Technically you can write a formula such as this.

=IF(ISERR(shane2!C6),0,shane2!C6)

It will show a result of 0 until Shane2 exists, then it will pick of the
value in that sheet. If you're worried about deleting sheets permanately
then it works also but the formula now show a #REF on the formula bar.

But if you are worried about deleting sheets and possible readding them then
don't use this formula because it will not be prepared when you add back the
sheet.

=IF(ISERR(INDIRECT("shane2!C6")),0,INDIRECT("shane 2!C6"))
or in 2007
=IFERROR(INDIRECT("shane2!C6"),0)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"wcurtis" wrote:

How can I use an IF function to look for a worksheet name and then look for a
specific cell and return the value in that cell. And if the worksheet is not
found, return a value of zero?
Example: Worksheet AB, cell B1, if not found return value of zero.

I am getting an error when I write in the formula. I am worried about taking
a sheet away and then putting in back in later. I want the cell to show 0 if
the sheet is not there.
I would ultimately like it if I had a template sheet that I could just copy
into any workbook and it would autopopulate. I have 22 to 28 worksheets that
have several lines of info I need.

"Shane Devenshire" wrote:

Hi,

Technically you can write a formula such as this.

=IF(ISERR(shane2!C6),0,shane2!C6)

It will show a result of 0 until Shane2 exists, then it will pick of the
value in that sheet. If you're worried about deleting sheets permanately
then it works also but the formula now show a #REF on the formula bar.

But if you are worried about deleting sheets and possible readding them then
don't use this formula because it will not be prepared when you add back the
sheet.

=IF(ISERR(INDIRECT("shane2!C6")),0,INDIRECT("shane 2!C6"))
or in 2007
=IFERROR(INDIRECT("shane2!C6"),0)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"wcurtis" wrote:

How can I use an IF function to look for a worksheet name and then look for a
specific cell and return the value in that cell. And if the worksheet is not
found, return a value of zero?
Example: Worksheet AB, cell B1, if not found return value of zero.

You could use something like this

=IF(ISERROR(Sheet8!B1),0,Sheet8!B1)

But that would mask all errors.

i.e. you might have the correct sheet but if B1 contains an error you will
still get a 0


Gord Dibben MS Excel MVP




On Thu, 8 Jan 2009 12:49:02 -0800, wcurtis
wrote:

How can I use an IF function to look for a worksheet name and then look for a
specific cell and return the value in that cell. And if the worksheet is not
found, return a value of zero?
Example: Worksheet AB, cell B1, if not found return value of zero.