03-21-2019 10:46 AM
03-22-2019 01:08 PM
I might not be the best person to answer because it is many years since I last used a direct cell reference as part of a spreadsheet solution.
I would recommend using a Template sheet with as much structure as possible built in through the use of defined names and Tables and structured references to the variable sized panels. Copying a template sheet then automatically creates local names for each resulting worksheet.
Different areas within the Summary sheet can be used to reference different sheets using the same name, by looking up the name associated with as set of rows numbers, e.g. 'project.name', from a table of steering data 'Project', which comprises a set of row indices and the associated name, which acts rather like a contents page in a book.
= LOOKUP(@RowNum, Project )
The sheet tabs are your project codes, which allows names such as
to direct to any sheet.
'Panels' might be defined by Tables which would allow them to accommodate different sized data sets, the size being readable using ROWS(Table1). Table names must be unique across the workbook but the can use the same name as the sheet tab. This means ''project.data'
can represent an entire table (on different sheets according to the setting of 'project.name'). A data column within each table could then be referenced by 'project.task' which refers to
or, alternatively, it would be possible to use
= INDEX( project.data, 0, 1 )
for the first column.
All this structuring is alien to normal spreadsheet practice because it requires planning and structure whereas the traditional approach is more of a 'bottom-up' process in which user actions can create infinite variation.
03-23-2019 01:04 AM
I think you are correct. An Excel 'running out of memory' error crashed my computer a number of times yesterday and I guess I didn't pick up the pieces correctly. My post was intended as a reply to a question that showed an image of a sheet with a detailed formula involving
= INDIRECT( Sheetname , CELL("address", … ) )
by Christine Karambelas on May 28, 2020
by cuong on May 27, 2020
by Ingeborg Hawighorst on May 13, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020