SEARCHING ANOTHER WORKBOOK FOR CURRENT ACTIVE TEXT OFFSET AND RETURNING TEXT FROM 3 CELLS OFFSET FROM FOUND TEXT

[BAKELOVEMORE]

I have 2 workbooks. I use one workbook to track issues that I have input on the other workbook. i.e. on the current workbook, I need to search the second workbook for a value offset by -3 from the current workbooks activecell. I need to take that text value found on the second workbook and insert the text found in the cells offset by 2 and 3 and 4. i.e. The text value offset by -3 of the active cell is Banana. Find Banana in column 3 and insert the concat text from columns 5 , 6, 7.

=IF(E24="TEXT",CONCAT('[OTHER SHEET.xlsm]Sheet1'!$E$2, "     ", '[OTHER SHEET.xlsm]Sheet1'!$F$2, "     ",'[OTHER SHEET.xlsm]Sheet1'!$G$2))

This works but I would have to use IFS and nest 66 different variables. I have tried using named cells as ranges. And activecell.offset, etc. I would also have to drag this formula down 32 cells. I really would rather have a function that I can just click when I need it because not all lines in my main workbook will need this.

 

[Cubist]

Try assign this macro to a shape. Select a cell and push the button.

Sub InsertFormula()
    Dim activeRow As Long
    Dim formula As String

    ' Get the active row number
    activeRow = ActiveCell.Row

    ' Construct the formula with a dynamic reference to cell E & activeRow
    formula = "=TEXTJOIN("" "",TRUE,FILTER('[OTHER SHEET.xlsm]Sheet1'!$E$2:$G$100,'[OTHER SHEET.xlsm]Sheet1'!$C$2:$C$100 = E" & activeRow & "))"

    ' Insert the formula into column H of the active cell's row
    ActiveSheet.Cells(activeRow, "H").Formula = formula
End Sub

 

[BAKELOVEMORE]

I recorded a macro pasting this into a cell but of course it recorded the actual cell. I need to change the Range().Select to use the current cell.

Try assign this macro to a shape. Select a cell and push the button.

Sub InsertFormula()
    Dim activeRow As Long
    Dim formula As String

    ' Get the active row number
    activeRow = ActiveCell.Row

    ' Construct the formula with a dynamic reference to cell E & activeRow
    formula = "=TEXTJOIN("" "",TRUE,FILTER('[OTHER SHEET.xlsm]Sheet1'!$E$2:$G$100,'[OTHER SHEET.xlsm]Sheet1'!$C$2:$C$100 = E" & activeRow & "))"

    ' Insert the formula into column H of the active cell's row
    ActiveSheet.Cells(activeRow, "H").Formula = formula
End Sub

Worked Great! Thanks so much.