Excel formula help

Copper Contributor

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?

6 Replies
Please specify your formula.

@Twifoo =INDIRECT($F$2)

Use relative reference for the row, like this:
=INDIRECT($F2)

@kmmmm_03 

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.

You must have posted your reply to an incorrect conversation. Please verify.

@Twifoo 

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", … ) )