Pulling a value from the same location on new sheets created from an Excel template.

I am currently creating a set of templates to be used in a quality assurance excel sheet. The aim is to have the scores from each sheet displayed on the top sheet of the excel 2007 book. Currently I can use a look up to pull the data, but I only pulls it to the same cell, and only for current sheets. Not for any new ones added.

Is it possible to have a formula that will look up a value on any new sheet created from a template and place it in a table that 开发者_开发问答I can graph?

You can do it with VBA, but I think this 3 step XLM (Excel4.0 Macro) approach is cleaner.The approach is to set a range name that contains all sheet names in the workbook, then extract them against a list of numbers.

1. Define a range name, wsName as =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

(credit david Hager http://spreadsheetpage.com/index.php/site/eee/issue_no_3_april_15_1999/)

2. use a formula such as =IF(A2<=COUNTA(wsNames)+0*RAND(),INDEX(wsNames,A2),"") to extract all the names on your summary sheet against a numbered list. This formula simply checks whether x sheets (say 6) are in the live file, if so its put the sheet x name (the 6th sheet) against the number x (6).

3. Use INDIRECT with this sheet name to retrieve the values, ie to get A1 from each sheet, =IF(B3<>"",INDIRECT("'" &B2&"'!A1"),"")

Should add sheets be added - or deleted - all the names update in sequence automatically