Dynamic Reference in Formula without named ranges

Question

I have a few different places in my spreadsheet that I need to dynamically modify the column in a range I'm referring to within a formula.

Problem 1: For example, the static version: =ifna(TEXTJOIN(", ",TRUE,UNIQUE(FILTER('Reference Sheet'!$A$6:$A$1000,value('Reference Sheet'!E$6:E$1000)>0))),"")

Works just fine, but when I go to the next row, I need it to change to reference 'Reference Sheet'!H$6:H$1000

Each row jumps by three columns in this particular case. Given that I have hundreds of rows, I'd rather not configure each manually. I have a cell in the sheet where the formula with live that pulls the proper column letter that would need to be referenced, but any time I try to build a dynamic reference it just spits out as text or a range that is incomplete.

This particular formula is in column N, with the column address reference in column I. So in this case a proper reference for the first static one would amount to something like "'Reference Sheet'!"(I2)$6:(I2)$1000"

Problem 2 In another section I have a similar issue. I need to reference that same "I" column to create the proper column letter in the referenced range. The formula lives in column K. In columns L and M I need the column number to increase by one each time it moves over, so if Column K's formula references E:E, I need L to reference F:F and M to reference "G:G". I could create additional hidden reference columns that would do the E, F, and G work for me that I could reference columns L and M to if I can just figure out how to dynamically create that sheet range reference without using named ranges.

The Reference sheet has hundreds of columns across, and column "I" in my formula sheet finds a match that pulls the column reference. There are a lot of people using the doc and a high risk the data inputs could get shuffled so I have to do it this way to be able to find it no matter how people put it in or if they move it.

I've tried a number of variations, none of which actually create a reference, either just text or an incomplete range reference string. Named ranges will be unsustainable due to the size of the sheet and potential that things get moved.

Answer 1

For problem 1, I assume the one you want to change is 'Reference Sheet'!E$6:E$1000. Assume your formula is placed in the same column, and the first equation is in column 1. Try to use INDIRECT(ADDRESS(6,2+ROW()*3,2,TRUE,"Reference Sheet")&":E$1000") instead.

If your formula is placed in the same row, change the ROW() to COLUMN(). If your formula does not start at row 1, change ROW()*3 to (ROW()-x+1), where x is the start row.

For problem 2, you can solve it by understanding how the above formula work. ADDRESS(row_number,column_number,[relative or absolute],[IsA1Notation],[Sheet]), input row and column in number, output its coordinate. A1Notation is coordinate in the form of "A2:D". Relative or absolute, just look at the hint it pops up when you type in the formula, no space to explain. For example, ADDRESS(2,1,TRUE,4,"XX X") would output 'XX X'!A2 in coordinate instead of string.

INDIRECT(coordinate in string,[IsA1Notation]), this formula turns a coordinate in string into an actual coordinate.