MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Fill without changing format?

Posted by Laura Peagler on July 14, 2001 3:43 PM

I'm working on some reports containing references to cells in another worksheet. After inserting new rows I now need to change the references in all the cells after the new rows because they now reference the wrong cells. For example, cell J138 has become cell J141 because I added 3 rows, but it still references L138 in the other worksheet.

This is what the reference looks like now: ='[2001_ACT.XLS]2001 actuals'!L138 All I want to change is the cell reference at the end to L141. Easy enough to do manually, but I have three reports with over 900 rows and 6 columns each that need the change. I know this is probably a simple problem but I don't use Excel that much and am hoping someone can help me before Monday morning.


Posted by Damon Ostrander on July 15, 2001 8:50 PM

Hi Laura,

If I understand you correctly, you want the formula to always access the data from the same row as the cell containing the formula from column L of the other worksheet. If this is true, simply use the OFFSET function:

=OFFSET('2001 actuals'!$A$1,ROW()-1,11)

where the 11 refers to the number of columns offset from column A to get to column L.

Happy computing.