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 the formula down from the top cell in the column to apply to every cell, it breaks. I am wanting the formula to reference A1, then B1, then C1, and so on. When I drag it down with A1, the following formulas reference A2, A3, A4, etc. I tried using A$1 to keep the row the same, but then when I drag the formula down it acts as if I've put $A$1 and keeps the A1 reference throughout the whole column. Any ideas on what I'm doing wrong here? Here is an image of a simplified formula with the problems/what I am wanting to accomplish:
=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!
Hi Oliver, this does not work for me but I have a feeling it is because the cells I am referencing are on a separate sheet? To be more precise, I need to reference Row 5 on a different sheet so really in my equation it's written as:
Sheet!G5, Sheet!H5, Sheet!II5, etc. which I am assuming will change the wording of your formula a bit?
I supposed worst case I could use this formula to create a column of the values in the row, and then just reference that created column in my complicated formula. Excel might like that workaround better for running the calcs.Edit: Nope, just tried doing that and even within the same sheet it does not work, still just displays whatever is in G5 the whole way down.
=INDEX(Sheet!$5:$5,,ROW(A7))This works in my file if i correctly understand what you want to do.
