Excel: How to stop losing my formulas when refreshing CMS download

Experts,

I have a spreadsheet that contains several Vlookups.  When I import the new data into the spreadsheet all my formulas turn in "#REF".  Is there a way around this?

Thanks
Maliki HassaniAsked:
Who is Participating?
 
SiddharthRoutCommented:
1) Where r u importing the data?
2) After importing, you will have to change formula to cover the last row of the table for example

=VLOOKUP($D$2,Sheet1!$A$5:$Z$7,15,0)

The last Row is 7 ($Z$7)

So if you data is up till say 500 then change that 7 to 500 so that the formula becomes

=VLOOKUP($D$2,Sheet1!$A$5:$Z$500,15,0)

Sid
0
 
Maliki HassaniAuthor Commented:
Sid:
  When I import the data regardless of the $ signs being placed it returns a
=VLOOKUP($D$2,'NOC Agent Daily S'!#REF!,15,0)
after my import.

Hmm
0
 
Maliki HassaniAuthor Commented:
The only thing I suppose I could do is write a script that will but in the formulas by VBA.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SiddharthRoutCommented:
Ok, Can you upload a workbook which has data imported in it and it is giving you an error?

Sid
0
 
Maliki HassaniAuthor Commented:
I can wite the VBA script so that is okay.  I was just hoping there was another way.
0
 
SiddharthRoutCommented:
If I look at your file then maybe I can suggest you another way?

Sid
0
 
Maliki HassaniAuthor Commented:
Okay so I went with find and replace after I imported the info. Puts back the formula.  

Thanks for your help!
0
 
Maliki HassaniAuthor Commented:
The $ does help in other applications, though
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.