INDIRECT lookup of sheet names

[Guest]

Hi there
I have done the following to create a generic payroll system for our other
branches to be able to set up with their own staff and utilise.
1. set up a named range of Staff (StaffName) on one sheet, and used dummy
names to populate
2. created individual sheets for each staff member to act as their timesheet
3. the name of each sheet updates from a drop-down (validation list) box on
the timesheets (I learned that clever little trick from you guys)
4. made a summary sheet that collects data from the individual sheets in a
format that I can create a pivot table from
Problem:
My summary sheet uses the INDIRECT function to lookup the sheet names, and
if the sheets have not been created it returns a #REF! My formula looks like
this =INDIRECT("'"&$B64&"'!I$15").
Is there something else I can put in here so that if the sheet name is not
valid, it will return a "0"?
Hoping someone can help...
Thanks
Jenny

 

[Samo]

Hi,

You can use the following formula:

=if(ISERROR(INDIRECT("'"&$B64&"'!I$15"))=True,0,=INDIRECT("'"&$B64&"'!I$15"))

Samo

 

[Peo Sjoblom]

There is really no need to use TRUE

=IF(ISERROR(INDIRECT("'"&$B64&"'!I15")),0,INDIRECT("'"&$B64&"'!I$15"))

will suffice (I also took out the equal sign which I believe was something
you copied over by mistake when creating the formula)

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

Hi Samo
I hope you guys realise what a wonderful service you do for us "wannabe"
experts. I couldn't do without you.
Many thanks, it works a treat.

Jenny

 

[Samo]

You are right Peo, no need to use True in the Formula. Thank you for the
advice.

Samo