Forum Discussion
Solved
A$1 working as $A$1 instead of B$1, C$1, etc.
Hi all, I am working on a spreadsheet for work and seem to be running into the same issue over and over again. I am wanting to reference a row in a formula for a column, however when I try to drag th...
=INDEX(Sheet!$5:$5,,ROW(A7))This works in my file if i correctly understand what you want to do.
The problem is simply one of using traditional spreadsheet techniques such as relative referencing. The idea they depend upon is to replace array operations by scalar operations with relative references. The 'best' solution would be to get Excel 365 and do the job properly, but that may well not be possible!
One approach might be to write a helper range of indices [1, ..., N] next to your intended formula. You could then use INDEX with the relative references in the column parameter.
= INDEX(array, 1, K1)
= INDEX(array, 1, K2)
= ...
Better would be to switch to array formula and use
= {TRANSPOSE(array)}
or more recently
= TOCOL(array)
Yep, unfortunately excel 365 is not an option, but the INDEX solution did end up working so in the end it is fixed! Now I just need to lock and hide those cells so my coworkers don't break it.... thanks!