Search code examples
excelvbaexcel-formulaoffset

Searching another workbook for current active text offset and returning text from 3 cells offset from found text

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.

main workbook:
enter image description here

second workbook to get data:
enter image description here

Solution

  • This does exactly what I need. Thanks all for the help.

    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