[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

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
0
Maliki Hassani
Asked:
Maliki Hassani
  • 5
  • 3
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now