cell reference problems

Microsoft Office excel 2007 home & stude...
June 28, 2010 at 09:51:58
Specs: Windows XP
hi there, i'm trying to reference cells from different worksheets imbeded in formulas. when i clear the contents of the reference worksheet and replace with new data i keep coming up with #REF! problems. and having to retype the formulas is a pain.

my original
=IF(ISBLANK(verifiredata!$M1)," ",verifiredata!$M1)

what i get after i clear the contents on verifiredata worksheet and replace with new data....
=IF(ISBLANK(verifiredata!#REF!)," ",verifiredata!#REF!)

any help would be great.

See More: cell reference problems

June 28, 2010 at 11:56:33

I suspect that it depends on how you are 'clearing' the data.

If you just use the Delete key or 'Clear Contents', the contents of the cells is removed, but the link is still there, but if you used Edit-Delete or right-click 'Delete', the cell itself is removed, and its place is taken by another cell and the link is lost.

If the cell that was linked to is deleted you get the #REF error because the cell that was there has gone.

If you just delete the contents, the cell is still there and you will not get an error.


Report •

June 28, 2010 at 14:43:07
i was highlighting the the entire block and right click clear contents.

i was thinking on using vlookup and the if statements i have nested up to 8 or so times look up the same infor for each cell. it might make a difference.

basically what i am doing is exporting a report from a 3rd party into excel 7.0 format and i want to have a template set up so i can just rearrange the columns and get rid of the empty rows columns etc on 1 worksheet, and have the end report full of referencing cells to the data worksheet i fill.

in blanktemplate.xls

<final report sheet> < different report> <vlookup tables> <raw data from 3rd party report sheet>

=IF(verifiredata!D13="PHOTO","FSP-851A",IF(verifiredata!D13="ION","FSI-851A",IF(verifiredata!D13="MANUAL STATION","NBG-12LX",IF(verifiredata!D13="MONITOR","FMM-1A",IF(verifiredata!D13="RELAY","FRM-1A",IF(verifiredata!D13="CONTROL","FCM-1A",IF(verifiredata!D13="MINI/DUAL MONITOR","FMM-101 OR FDM-1A","OTHER")))))))

atm everytime i do a report i have to redo all the referencing in it.

Report •

June 28, 2010 at 14:58:29

If you are just 'Clearing Contents' then that should not affect the references.

How do you put the new data into the worksheet.

Look at your formulas after 'Clearing contents' and before adding new data - are the references still correct at that point?


Report •

Related Solutions

June 28, 2010 at 15:06:26
the formulas are good after i clear the contents.

i suspect its how i am putting the data in for the new stuff. what i have been doing is opening another excel worksheet the one that was exported from the 3rd party as excel 7.0 format and having both open highlight what i want from the exprot and drag and drop in the template(4th sheet) and rearrange as necessary there. if they were 2 different types of excel file excel7.0 and excel office 2007 make a difference?

Report •

June 29, 2010 at 03:43:02

It sounds as though the process of rearranging data is moving cells and so losing the references in your formula.

If you just copy and paste in your new data - don't move it about - and create your links to where it is when pasted.

You shouldn't need to rearrange this 'raw' data.

As long as you paste in the same data to the same cells each time, your formulas will work.


Report •

July 7, 2010 at 07:30:43
hi there, yeah i think i will do all the rearranging before i move it and that will solve my problem of the cell reference.

on another note maybe you could help with a new problem that came up when i was figuring this out. to change all those embedded if statments into a VLOOKUP table i think would be easier everytime i look over the statment but my problem is i need to compare it to 2 columns and pick the 3rd. can you have nested vlookups? this is the data i'm comparing.

Horn Circuit CONTROL FCM or XP6-CA
Latch Superv MONITOR FMM-1A
Pull Station MINI/DUAL MON NFM-950B
Smoke(Photo) PHOTO FSP-851A
Smoke(Ion) ION FSI-851A

the matches have to be A and B and i want C to show up in the cell with the formula on sheet1, this data is on sheet3 permanently and will not need to be changed each time. just the raw data in sheet4.

any ideas would be great. going to try to figure out how to do nested vlookups unless you answer back with an idea or 2.

thanks in advance

Report •

July 7, 2010 at 11:21:50
obviously my tabs dont work here but theres 3 columns of data.

i found the nested vlookups not working right but i was thinking of trying with an and() formula in there somewhere. just trying to figure it out. i keep coming up with blanks atm. i think if i could get the nested vlookup to work it would be easy.
i tried...this but i get a #N/A like its missing the middle reference number or the 1st nested vlookup cell, where the 2nd vlookup takes it place.

still trying. basically the data is for formulating a report for a fire alarm print out i have 2 sets of data, device type and type code both on the vlookup data and the imported data and i have to match that with a particular piece of equipment.

Report •

July 8, 2010 at 07:04:21

Here is a way to return the two-item match without using nested IF's and it is easy to extend to either more items in the list or even a third column to match against.

For this example, my table was in columns A, B & C, rows 2 to 11, like this:

	A			B		C
2	Horn Circuit		CONTROL FCM	XP6-CA
3	Relay			RELAY		FRM-1A
5	Latch Superv		MONITOR		FMM-1A
6	Monitor			MONITOR		FMM-1A
7	Monitor			MINI/DUAL MON	FDM-1A
8	Latch Superv		MINI/DUAL MON	FDM-1A
9	Pull Station		MINI/DUAL MON	NFM-950B
10	Smoke(Photo)		PHOTO		FSP-851A
11	Smoke(Ion)		ION		FSI-851A

I had the first selection in cell D12 (for col. A) and the second in D13 (for col. B)
You can change these addresses to the appropriate worksheet/cell as required.

The final result is returned using the offset function.
The SUMPRODUCT() function returns the row number of the matching combination and this has to be adjusted based on which row the table starts on. You will see a '-2' near the end of the formula, and this takes account of where the table starts.
The reference to C2 at the beginning is the first cell in the table in the results column (column C in this case).

This is the formula:

In the example, Pull Station and MINI/DUAL MON returned NFM-950B.

As a note for future posting, put your data between <pre> and </pre> tags that you can insert using the 'Pre' icon above the reply box. Then use the Preview button and edit, as required. To preview again, check the 'Check To Show Confirmation Page Again' box and click 'Confirm and see post'

If you have a question that is about another issue, even if for the same project, could you start a new post, with a new title. It makes it easier to track questions and answers.


Report •

Ask Question