Say you have sheets for specific items, and then an overview sheet which pulls some of the information for specific item sheets with simple references.
Now let's say that to create a specific item sheet you just copy the previous sheet, so that they get named "Item", "Item (2)", "Item (3)", etc as is excel's default.
Is there any way that you can write a reference so that you can then drag your cells across columns to update the reference formula to the next sheet copy automatically, without the use of vb code? (Ie, automatically update the formula from 'Item (2)'!CellNumber to 'Item (3)'!CellNumber)
Answer: (Curtsy of TMShucks) is to use a format similar to =INDIRECT("'<sheetname> ("&COLUMN()<- any difference between cell number and sheet number>&")'!<Cell>")
Bookmarks