Help needed Excel formula, textjoin, filter, indirect

Anonymous
2023-10-13T09:22:18+00:00

Hi all, really struggling to work out a formula if you could please help.

I'm trying to bring in notes from a table on a different (variable) tab and have had success with the formula =textjoin(", ",true,indirect(e1&"[notes]")) however I cannot figure out how to filter it based on 2 other criteria, department and staff group.

The formula needs to be dynamic as the tab can change.

I have also tried, without success:

=Textjoin(", ",true,filter(indirect(e1&"[notes]"),indirect(e1&"[cog directorate]")=b9,"")

=Textjoin(", ",true,if(indirect(e1&"[cog directorate]")=b9,indirect(e1&"[notes]"),""))

Unfortunately I'm not able to share the file sure to the sensitive nature of the data.

Please help!

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-10-13T11:04:40+00:00

ImageWeirdly now I've simplified the file the formula is working for me too now I think, in e9 anyway, which is the cell I have the working formula in.Image

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-10-13T11:34:22+00:00

    Glad to hear that. If you need more help on it. Feel free to come back.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-10-13T10:44:33+00:00

    Could you share some dummy data to test formula？ Your formula seems work at my side/

    =TEXTJOIN(", ",TRUE,FILTER(INDIRECT(E1&"[notes]"),INDIRECT(E1&"[cog directorate]")=B9,""))

    Image

    .

    =TEXTJOIN(", ",TRUE,FILTER(INDIRECT(E1&"[notes]"),(INDIRECT(E1&"[cog directorate]")=B9)*(INDIRECT(E1&"[Staff]")=B10),""))

    Was this answer helpful?

    0 comments