1

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.

6
  • Welcome! Why not use the OFFSET() function bound to the current row number? For example, for Problem 1, if the resulting table is built from cell A1, the formula could be like this =ifna(TEXTJOIN(", ",TRUE,UNIQUE(FILTER('Reference Sheet'!$A$6:$A$1000,value( OFFSET('Reference Sheet'!B$6:B$1000,0,ROW()*3) )>0))),"")
    – JohnSUN
    Sep 9, 2022 at 19:01
  • @JohnSUN that would work fine for one of them, but the next one would then need to be *6, *9 and so forth. For problem two it gets worse. In cell J2 is; =IF(isblank(A2),"", COUNT('Reference Sheet'!B:B)) in cell L2; =if(isblank(A2),"", SUM('Reference Sheet'!B:B)), in cell N2; =if(isblank(A2),"", SUM('Reference Sheet'!C:C)), in cell P2; =if(isblank(A2),"", SUM('Reference Sheet'!D:D)) on the following row it shifts to E:E, E:E, F:F, and G:G respectively
    – KC Reeley
    Sep 10, 2022 at 0:23
  • Why *6, *9, etc.? ROW(A1)=1, ROW(A2)=2, ROW(A3)=3. So ROW()*3 return 3;6;9 etc. and OFFSET() will return E$6:E$1000, H$6:H$1000; K$6:K$1000; N$6:N$1000... Maybe I misunderstood the second paragraph in the description Problem 1: to change to reference 'Reference Sheet'!H$6:H$1000. Which of the ranges in the first formula are you changing? E$6:E$1000? Or A$6:$A$1000 ?
    – JohnSUN
    Sep 10, 2022 at 5:10
  • @JohnSUN I need the values to update automatically based on reference to another cell otherwise I'm still manually updating every formula which is what I'm trying to avoid. Nevertheless I tried your suggestion but when I input it exactly as provided it came back that the filter couldn't find a match, where it does with the formula as I've created it. It took a while to strip confidential information, but here's a mockup: docs.google.com/spreadsheets/d/… trying to get columns K, M, O, Q, and S to update automatically
    – KC Reeley
    Sep 14, 2022 at 2:25
  • *Columns K, M, O, Q, and S on the "Pick List" tab - it'd like to be able to drag down and have the references correctly update automatically
    – KC Reeley
    Sep 14, 2022 at 2:26

1 Answer 1

0

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.

3
  • it took a very long time for me to strip out all confidential information (even some formulas had some references in their names I couldn't risk) but thought this might be more helpful: docs.google.com/spreadsheets/d/… I would ideally like columns K, M, O, Q, and S to update the appropriate references correctly and automatically when I drag down. I've manually entered the formulas for now but only to a limited row because it's so time consuming. I tried some of what you've suggested but couldn't get it to work.
    – KC Reeley
    Sep 14, 2022 at 2:17
  • *Columns K, M, O, Q, and S on the "Pick List" tab - it'd like to be able to drag down and have the references correctly update automatically
    – KC Reeley
    Sep 14, 2022 at 2:26
  • Did my first formula work? If all numbers in the formula are not changed when you drag to fill the formula, it should work. I'm sorry, but for some inconvenience reason, I'm using a school account where I don't have access to outside files. I'll take a look using another account when I have time (maybe a few days).
    – Flyson
    Sep 14, 2022 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.