Keeping formula when line is inserted 1

[kcoleman26]

Excel 2003

I am working with Two Sheets one that has the Data on it that is entered manually. The other is a like a Contract where if I fill out the Data sheet it will populate the contract.

The problem I am having is when I insert a row that copies my formulas the formulas on the contract follow the previously entered data.

Example:
Contract formula says =sheet1!A4 when I type the Name in sheet1 A4 it works fine. When I insert a line so that it moves the info down I add a new group name and the formula in the Contract now says sheet1!A5. I don't want the contract formula to change I always want it to read cell A4 when I insert a new line.

Thanks
KC

 

[AnotherHiggins]

First, I wouldn't insert lines. Just add a line to the end and, if needed, sort to realign everything.

But the behavior you're seeing can be bypassed by using the INDIRECT function.

Example:
[tab]=INDIRECT("Sheet1!A4")

Or you can just add a new row of data at the end of Sheet1 and always return the last row by using something like:
[tab]=INDIRECT("Sheet1!A" & COUNTA(Sheet1!A:A))

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[kcoleman26]

The 1st INDIRECT worked great

Thank you anohterhiggins