#REF! issue when replacing data on sheet 1 out of 2

**Arba** · 04-27-2012, 12:24 PM

Hi,

I am having this problem. I have an advanced excel formula on SHEET2 that is referencing SHEET1.

If I change out data on SHEET1, every field in SHEET2 shows #REF!.

Is there a way I can lock down the formula on SHEET2, or create a work around to avoid #REF! when replacing data on SHEET1?

Thanks,
Arba

**Russell Dawson** · 04-27-2012, 12:30 PM

Please post your workbook

**Arba** · 04-27-2012, 12:40 PM

The reason I did not post my workbook is because I assume this is the same for any workbook, or formula. If the data is removed or replaced it no longer has a reference point.

Please find attached,

Untitled.xlsx

**Russell Dawson** · 04-27-2012, 01:10 PM

Quite right. That's just the nature of formulas.

But it does seem to look Ok. What exactly do you do to cause the #ref?

**Arba** · 04-27-2012, 01:19 PM

The first sheet is an export from a 3rd party software I am testing, the second sheet is an import to my local database in the broken out format I needed it in.

Since I have SHEET2 created, all I have to do is copy/paste data on SHEET1 and it will automatically populate SHEET2 without much more structuring.

EDIT: This would be a frequent task, so I'm trying to find a way to avoid #REF! where possible for 'slower' end-users.

EDIT: In my second sheet, is it possible to lock down the fact that A3 is referencing A2, and will always be? Whether or not it contains data? Instead of making A3 reference #REF! when it's removed or replaced?

**Russell Dawson** · 04-27-2012, 02:00 PM

If I understand you correctly you are importing a file into sheet1 and then to put it into a correct format for export to your database you are copying into sheet2 which manipulates the data before export.

Is your example a true representation of what you have when data is received? I have an idea. Is the crucial task to separate the reference "SC[2],EO[1],FL[1]" into SC 2 EO 1 FL 1 ?

It can be done with Text to columns and a macro could be devised to deal with this as long as the source is always in the same format. You may have considered this and eliminated it but thought I'd mention it.