INDIRECT lookup of sheet names

J

Jenny

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
 
P

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
 
J

Jenny

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
 

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

Top