Copying formula by changing only one rule. [Solved]

shan_asa 7 Posts Sunday October 29, 2017Registration date December 5, 2017 Last seen - Nov 7, 2017 at 10:37 AM - Latest reply: shan_asa 7 Posts Sunday October 29, 2017Registration date December 5, 2017 Last seen
- Nov 7, 2017 at 01:03 PM
Hello Team,

I want to copy the formula "=INDEX((Sheet1!B2:B99),MATCH(B24,Sheet1!A2:A99,0))" 1000 cells by changing only "B24" to "B25, B26" and so on.
can any 1 help me with this.
See more 

Your reply

2 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Nov 7, 2017 at 11:59 AM
0
Thank you
Hi Shan_asa,

What you are looking for is changing relative cell references to absolute cell refrences. This is done by placing a dollar symbol in front of either the column letter or the row number or both.

You can do this manually or selecting the range in the formula and hit F4. Hitting it multiple times cycles through the different options (row, column or both and then back to none)

When you apply this knowledge to your formula it will look like this:
=INDEX((Sheet1!B$2:B$99),MATCH(B24,Sheet1!A$2:A$99,0))

Since you are dragging your formula down, there is no need to place dollar signs in front of the column letter.

Best regards,
Trowa

shan_asa 7 Posts Sunday October 29, 2017Registration date December 5, 2017 Last seen - Nov 7, 2017 at 01:03 PM
Dear TrowaD,

You were of great help. Thank you very much.
Respond to TrowaD