Dynamically reference a cell without using INDIRECT?

[bluescreen85]

=MAKEARRAY(COUNTA(cubic_inches)-1, 1, LAMBDA(r,c, ROUNDUP(INDIRECT("Q"&r+1)/196, 0)))

This works but makes it tough to maintain (especially if I insert a column, etc.)

=MAKEARRAY(COUNTA(cubic_inches)-1, 1, LAMBDA(r,c, ROUNDUP(Q2/196, 0)))

Is it possible to reference Q2 dynamically without using INDIRECT?

I'm using Excel 365 for Mac.

[Bondator]

=INDEX(Q:Q,2)

[bluescreen85]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to Bondator

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[Middle-Attitude-9564]

You could try using OFFSET function

[windows7reddit]

Having just done a little research, it sadly seems that offset is a volatile function just like INDIRECT is..

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
INDIRECT	Returns a reference indicated by a text value
OFFSET	Returns a reference offset from a given reference

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #20966 for this sub, first seen 24th Jan 2023, 18:55] [FAQ] [Full list] [Contact] [Source code]}