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.

 

[BAKELOVEMORE]

In the example below, I have H4 highlighted. This is where I want the text from the other file to show. Based on cell D4, I want to search the other file in column C2:C66 for the row containing the same text "FRNKFORD" and CONCAT(E23," ",F23," ",G23) and placing that text in H4.

 

[Cubist]

Assuming the last row is 100 ( adjust as needed). Try:

=TEXTJOIN(" ",TRUE,FILTER('[OTHER SHEET.xlsm]Sheet1'!$E$2:$G$100,'[OTHER SHEET.xlsm]Sheet1'!$C$2:$C$100 = E4))

 

[BAKELOVEMORE]

Assuming the last row is 100 ( adjust as needed). Try:

=TEXTJOIN(" ",TRUE,FILTER('[OTHER SHEET.xlsm]Sheet1'!$E$2:$G$100,'[OTHER SHEET.xlsm]Sheet1'!$C$2:$C$100 = E4))

That works great but I need E$4 to be whatever the active row is. I would rather not have to drag this down the entire project and use it as a macro only when I need to.

 

[Cubist]

How do you imagine the macro would trigger e.g. cell change, click on the cell? What should it use as an indicator to know which row should be applied to?

 

[BAKELOVEMORE]

How do you imagine the macro would trigger? What should it use as an indicator to know which row should be applied to?

I have tried using activecell.offset but I never seem to get the syntax exactly right.

 

[Cubist]

I have tried using activecell.offset but I never seem to get the syntax exactly right.

That didn't really answer my questions.

 

[BAKELOVEMORE]

That didn't really answer my questions.

Sorry. It would use whatever active row I am on and use the text found in column E(active row).

 

[Cubist]

Ok, why don't you just copy and paste the formula into the row that you need it for? I don't see a point of a macro.

 

[BAKELOVEMORE]

Ok, why don't you just copy and paste the formula into the row that you need it for? I don't see a point of a macro.

Time. I need to so things as quickly and efficiently as possible. I work in a train control center and 30 seconds adds up to minutes during a shift. Pasting the formulas is definitely faster than copy/pasting back and forth each cell, but I am also trying to make it as simple as possible for the other users in the office that have no clue about what to do. They understand "Push this button".