PDA

View Full Version : Solved: That Pesky #REF Error



Opv
02-17-2010, 07:22 AM
I have had a tendency to use a simple formula to automatically populate cells in newly created rows so as to avoid having to manually enter repetitious data elements once data in new rows are added. For example, in cell C3 I might have a formula that says =IF(A3<>"",C2,"").

This automatically populates C3 with the data currently in C2 once I enter the initial data in cell A3. Of course, if I ever delete a row, you know what happens...it kills the ref to the cell in the delete row and results in the dreaded #REF error.

Before I go to the trouble of restructuring my worksheets (which are rather large), I am wondering if there is a workaround that avoids this problem.

Thanks,

Opv

Bob Phillips
02-17-2010, 07:29 AM
You can use INDIRECT, although this is volatile and should be best avoided IMO.

SamT
02-17-2010, 09:31 AM
Copy...Paste Special...Values

Defined Names

Offsets

On Change...calculate values in C2

Various other methods. It really depends on the structure you've set up.

SamT

Opv
02-17-2010, 10:25 AM
I thought about VBA; however, I am brand new to VBA so I was hoping for a simple fix with the formula.

The data sheet is a rather large sheet that tracks monthly rent payments and distribution of funds to various people. So, it is not always the value in a specific cell that gets screwed up. I have used the formula described in my original post across multiple columns so that new rows remain hidden until the trigger cell in each new row is manually populated. Once it is populated, a number of the cells in that row populate with the default entry from the previous row to automatically populate the fields directly below in new rows.

I tend to go in from time to time and perform some "what-if" scenarios and then delete those rows when finished. When I do, all of the cells that call for the data from a previous row return #REF when the trigger cell is populated. I'm currently having to manually copy the formulas from uneffected rows (above-- that actually have data in them) and Paste Special on the blank rows underneath in order to correct the problem.

Thanks to all of you for your suggestions.

Opv

Opv
02-17-2010, 10:34 AM
I think I solved it. Thanks for the Offset suggestion. That works. I simply changed my formula on Row 4, for example, from =If(Date<>"",C3,"") to =If Date<>"",Offset(C4,-1,0),"") and that retains its integrity when previous rows are deleted. Thanks to everyone for your help.

Opv

SamT
02-17-2010, 11:25 AM
OPV,

Be sure and mark this thread "Solved" with the thread tools at the top.

SamT