Update Links between cells of two worksheets in the same workbook
 

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

If sheetB and SheetA are in the same workbook, then your formula should adjust
ok.

If those two sheets are in two different workbooks, then the formulas will
adjust nicely if both workbooks are open.

If you can't open the other workbook and you only have a few (whatever that
means), you could name each cell (insert|name|define) and refer to those names
in your formulas.



37Drive wrote:

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

--

Dave Peterson

Oops! I failed to say that yes they are both in the same workbook. I thought
they would update automatically, but they don't. My formula reads
=SheetName!specificcell Do I need to use a more generic reference instead
of a specific cell name?

Any help is greatly appreciated!
Maureen

"Dave Peterson" wrote:

If sheetB and SheetA are in the same workbook, then your formula should adjust
ok.

If those two sheets are in two different workbooks, then the formulas will
adjust nicely if both workbooks are open.

If you can't open the other workbook and you only have a few (whatever that
means), you could name each cell (insert|name|define) and refer to those names
in your formulas.



37Drive wrote:

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

--

Dave Peterson

If you have a formula on sheet2 that looks like this:

=sheet1!a1

and you insert a new row 1 in sheet1, then your formula should change to:
=sheet1!a2

I've never seen excel do anything else.

======
Just in case you meant you always wanted to point to the same address--not
matter what was inserted or deleted, you could use this formula:

=indirect("sheet1!a1")

(If you did mean this, then I got confused. Sorry.)

37Drive wrote:

Oops! I failed to say that yes they are both in the same workbook. I thought
they would update automatically, but they don't. My formula reads
=SheetName!specificcell Do I need to use a more generic reference instead
of a specific cell name?

Any help is greatly appreciated!
Maureen

"Dave Peterson" wrote:

If sheetB and SheetA are in the same workbook, then your formula should adjust
ok.

If those two sheets are in two different workbooks, then the formulas will
adjust nicely if both workbooks are open.

If you can't open the other workbook and you only have a few (whatever that
means), you could name each cell (insert|name|define) and refer to those names
in your formulas.



37Drive wrote:

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

--

Dave Peterson


--

Dave Peterson

Hmmmm...thanks for the ideas, but it still does not seem to work. If I add a
test row, thus bumping down my reference, the formula does not change, so the
info displayed is the new row that has taken place of the old one due to the
row insertion. I'm at a loss right now - augh!

"Dave Peterson" wrote:

If you have a formula on sheet2 that looks like this:

=sheet1!a1

and you insert a new row 1 in sheet1, then your formula should change to:
=sheet1!a2

I've never seen excel do anything else.

======
Just in case you meant you always wanted to point to the same address--not
matter what was inserted or deleted, you could use this formula:

=indirect("sheet1!a1")

(If you did mean this, then I got confused. Sorry.)

37Drive wrote:

Oops! I failed to say that yes they are both in the same workbook. I thought
they would update automatically, but they don't. My formula reads
=SheetName!specificcell Do I need to use a more generic reference instead
of a specific cell name?

Any help is greatly appreciated!
Maureen

"Dave Peterson" wrote:

If sheetB and SheetA are in the same workbook, then your formula should adjust
ok.

If those two sheets are in two different workbooks, then the formulas will
adjust nicely if both workbooks are open.

If you can't open the other workbook and you only have a few (whatever that
means), you could name each cell (insert|name|define) and refer to those names
in your formulas.



37Drive wrote:

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

--

Dave Peterson


--

Dave Peterson

Maybe you can give more details.

The name of the worksheet with the formula and the address of that cell. And
the formula that you're using.

And the name of the worksheet that's getting the row inserted and what row is
being inserted.



37Drive wrote:

Hmmmm...thanks for the ideas, but it still does not seem to work. If I add a
test row, thus bumping down my reference, the formula does not change, so the
info displayed is the new row that has taken place of the old one due to the
row insertion. I'm at a loss right now - augh!

"Dave Peterson" wrote:

If you have a formula on sheet2 that looks like this:

=sheet1!a1

and you insert a new row 1 in sheet1, then your formula should change to:
=sheet1!a2

I've never seen excel do anything else.

======
Just in case you meant you always wanted to point to the same address--not
matter what was inserted or deleted, you could use this formula:

=indirect("sheet1!a1")

(If you did mean this, then I got confused. Sorry.)

37Drive wrote:

Oops! I failed to say that yes they are both in the same workbook. I thought
they would update automatically, but they don't. My formula reads
=SheetName!specificcell Do I need to use a more generic reference instead
of a specific cell name?

Any help is greatly appreciated!
Maureen

"Dave Peterson" wrote:

If sheetB and SheetA are in the same workbook, then your formula should adjust
ok.

If those two sheets are in two different workbooks, then the formulas will
adjust nicely if both workbooks are open.

If you can't open the other workbook and you only have a few (whatever that
means), you could name each cell (insert|name|define) and refer to those names
in your formulas.



37Drive wrote:

I have a link from a cell on Sheet B that refers to a cell on Sheet A. When I
add a row to Sheet A (and the cell gets 'bumped down'), my reference is off
by that row? What can I do to automatically update this? How do I apply some
type of relative reference so that no matter how many rows I add, the
references shuffle with the new row position?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson