Mar 21 2019 10:46 AM
I'm setting up a spreadsheet with an indirect formula which I can get to work for the first line but any line after this seems to just reference the original cell and not what is populated for that line. How do I fix this?
Mar 22 2019 02:45 AM
Mar 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
= INDIRECT(project.name&"!Status")
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'
= INDIRECT(project.name)
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
= INDIRECT(project.name&"[Task]")
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.
Mar 22 2019 03:44 PM
Mar 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", … ) )