I'm using excel to prepare a large group of forms/letters to be templatized and input into a document management system. Part of this process is digesting each form/letter into an excel workbook; each form gets its own sheet named after it, where all fillable fields or pieces of templated information (like a recipient name or address) are recorded as Fields. Each sheet has the following columns: "Field", "Selectable Options", "Data Indexed Field?", and "Used on Other Forms". The "Used on Other Forms" column is a particular pain point - it needs to contain the name of each other form that contains the same Field, which becomes quite cumbersome to manually update after the fifth or so item.
What I want to accomplish is to set up the "Used on Other Forms" column in each worksheet such that it references the appropriate cell in the "Field" column, then checks all other sheets in the workbook to see if they contain the value in that cell, then populates with a list of the sheets which do. Ex, I had sheets A, B, C, D, and E, and sheets A, C, and E have the value "Date of Birth" in their respective "Fields" columns, then I would want the cell in the column "Used on Other Forms" and the row "Date of Birth" on sheet E to contain the list "A, C, E". Is this possible? I don't have much excel knowledge, but I feel that there must be a way to accomplish this.
I attempted to use ChatGPT to solve the issue; after a few iterations of going through it explaining what different formulas do and refining its output, it gave me the following formula:
=INDEX(CELL("filename", A1:A4), SMALL(IF(COUNTIF(INDIRECT("'"&CELL("filename")&"'!A1:A1000"), "Field")>0, ROW(A1:A4)-MIN(ROW(A1:A4))+1), ROWS($1:1)))
Upon hitting "Enter" to run the formula, however, nothing happened. As stated before, I'm not very experienced with Excel, so I don't know exactly how the formula works. Thanks!
Within Name Manager, define SheetNames as:
=LET(ζ,GET.WORKBOOK(1),REPLACE(ζ,1,FIND("]",ζ),""))
In the Used on Other Forms column, you can then use the following formula (for row 2, and assuming the Field column is column A in all worksheets):
=TEXTJOIN(", ",,FILTER(SheetNames,COUNTIF(INDIRECT("'"&SheetNames&"'!A:A"),A2)))
The use of the GET.WORKBOOK function means that your workbook will need to be saved as macro-enabled. If this is not desirable you can replace the definition for SheetNames with a reference to a worksheet range comprising a list of all manually input worksheet names.