Share via

Excel - How to fix row, but have the column change.

Anonymous
2021-08-18T13:23:52+00:00

Hello,

I´m working on a formula that copies values from a sheet that has many calculations of percentages, to be able to upload to an online system. But the problem is that in order to do so, I need to work with one sheet that has the values Horizontally to the other that has the values Vertically.

What I need to do, is make the formula copy the values from the row B2 and when I copy downwards, that it takes the value C2, D2, E2 etc. In other words, I would need the row to remain fixed and the columns to go changing, but I can´t make it work. I tried using B$2, but that kept the whole formula fixed.

Does anybody know a solution to this?

Appreciate any help.

Many thanks,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-08-18T13:35:58+00:00

    Let's say the values are on Sheet 2, in B2, C2, ...

    And the first formula should be in D3 on another sheet. Use this:

    =INDEX('Sheet 2'!$2:$2, ROW(D3)-ROW($D$3)+2)

    The +2 is because the first formula should refer to the 2nd cell in row 2 on Sheet 2.

    Fill down.

    2 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-08-18T16:48:11+00:00

    Thanks Hans! It´s working now. Appreciate your help.

    0 comments