I have a Excel doc with a master log sheet that I would like to link to cells in other detailed sheets. The main sheet is called RFI Log and the other sheets are RFI001, RFI002, RFI003, etc.. Ideally on the RFI Log sheet in the Status column the formula would be =RFI_001!$A$18 and that would show the Status of RFI001. It works great. But I have over 50 RFI Sheets with a dozen fields that I want on RFI Log. Without having to type in the formula in each cell on RFI Log is there a faster way to replicate the formulas? I have tried all my cell locking tips ($) but when I drag a corner of a cell to pull down the formula the sheet name doesn't change incrementally. For example if I drag the cell =RFI_001!$A$18 the next cell turns into =RFI_001!$A$19 and not =RFI_002!$A$18.

CodePudding user response：

INDIRECT should help you here. Usage: =INDIRECT(cellWithSheetName&"!A18")

You don't need the $ to fix column or row when dragging the formula. Just create a range of your sheet names and let the sheet name reference be adjusted when you drag the formula.

CodePudding user response：

Something like =INDIRECT"RFI_"&TEXT(SEQUENCE(18),"000")&"!A"&18-SEQUENCE(18))