Forum Discussion

Layton__E's avatar
Layton__E
Copper Contributor
Sep 28, 2023
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...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Sep 28, 2023

    Layton__E 

    =INDEX(Sheet!$5:$5,,ROW(A7))

    This works in my file if i correctly understand what you want to do.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 28, 2023

Layton__E 

=INDEX($1:$1,,ROW(A1))+1

Does this return the intended result?

Layton__E's avatar
Layton__E
Copper Contributor
Sep 28, 2023

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. 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Sep 28, 2023

    Layton__E 

    =INDEX(Sheet!$5:$5,,ROW(A7))

    This works in my file if i correctly understand what you want to do.

     

    • Layton__E's avatar
      Layton__E
      Copper Contributor
      Sep 28, 2023
      That got it working! Thanks so much for the help!! You would think referencing rows in columns and vice versa wouldn't be so difficult!

Resources