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.