Keep cell reference

[fishcad]

I use Excel to run wrestling tournaments in which the wrestlers are sorted by weight and automatically placed in brackets. I have one workbook for round-robin tourneys and another workbook for line-bracket tourneys. Occasionally I need to insert a bye into the list of wrestlers and have the other wrestlers move down in the list. I need the cells that refer to the "Bye" cell to stay on that cell rather than move to the next cell down. Please look at the attached images to see what I mean.

 

[jtakw]

Hi,

Try this in I5

=D$4

 

[fishcad]

Nope. Just changed I5 to "=D$5".

 

[jtakw]

When you say "insert" Bye, exactly what do you mean?
Honestly your pics in OP didn't help me, it's just too small for me to make head or tails out of them.
So my answer was "in general" that's how you maintain the cell reference by using Absolute referencing.

 

[jtakw]

At first I thought you were inserting a cell at I5
But if you're inserting a cell at D4
Then, try this:

=INDIRECT("D4")

 

[fishcad]

When you say "insert" Bye, exactly what do you mean?
Honestly your pics in OP didn't help me, it's just too small for me to make head or tails out of them.
So my answer was "in general" that's how you maintain the cell reference by using Absolute referencing.

I select D4, E4, and F4 then insert cells so all the cells shift down. Then I type in "BYE".

 

[fishcad]

At first I thought you were inserting a cell at I5
But if you're inserting a cell at D4
Then, try this:

=INDIRECT("D4")

 

[fishcad]

That just resulted in I5 being a "0".

 

[jtakw]

That just resulted in I5 being a "0".

That's because the new D4 is blank.
Until you Enter Something in the Now (new) D4, it will show 0, put something in the new D4, and I5 will show whatever is there.

 

[fishcad]

Thanks, that did it.