How do I keep column reference from changing in a formula when I add new columns?

Question

I have an excel where I collect data every two weeks, and then the following formula to search that range of data:

=TEXTJOIN(" ",TRUE,IF(Current!D:D="x2",Current!B:B,""))

However, every two weeks I add blank columns to collect new data for that time period. When I add the new columns, the first column reference gets bumped over. For instance, the above formula becomes the following:

=TEXTJOIN(" ",TRUE,IF(Current!G:G="x2",Current!B:B,""))

When I add the new set of columns, I want the formula to not change the columns from D to G. I want them to remain on D.

Answer 1

Hi,

Does this work?

=TEXTJOIN(" ",TRUE,FILTER(Current!B:B,INDIRECT(Current!D:D)="x2",""))

Also, avoid giving entire ranges in formulas. Specify the range in which you have data.

Answer 2

Use the INDIRECT function:

=TEXTJOIN(" ",TRUE,IF(INDIRECT("Current!D:D")="x2",Current!B:B,""))