Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT lookup of sheet names
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT lookup of sheet names
Hi,
You can use the following formula: =if(ISERROR(INDIRECT("'"&$B64&"'!I$15"))=True,0,=I NDIRECT("'"&$B64&"'!I$15")) Samo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT lookup of sheet names
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 "Samo" wrote in message oups.com... Hi, You can use the following formula: =if(ISERROR(INDIRECT("'"&$B64&"'!I$15"))=True,0,=I NDIRECT("'"&$B64&"'!I$15")) Samo |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT lookup of sheet names
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" wrote: Hi, You can use the following formula: =if(ISERROR(INDIRECT("'"&$B64&"'!I$15"))=True,0,=I NDIRECT("'"&$B64&"'!I$15")) Samo |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDIRECT lookup of sheet names
You are right Peo, no need to use True in the Formula. Thank you for the advice. Samo -- Samo ------------------------------------------------------------------------ Samo's Profile: http://www.excelforum.com/member.php...o&userid=34413 View this thread: http://www.excelforum.com/showthread...hreadid=541888 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup different sheets and get the value from the sheet specified | Excel Worksheet Functions | |||
dynamic range name | Excel Discussion (Misc queries) | |||
Lookup from other sheet | Excel Discussion (Misc queries) | |||
Get other sheet names into a column | Excel Worksheet Functions | |||
Sheet names used in formulas | Excel Discussion (Misc queries) |