Importing Data Into Formula Based Cells

[Heyna]

Hi Folks;
Hope I can get some help here.
I've been tasked with creating a "hands free" Cost Analysis Spreadsheet
(we have a lot of non-Excel Savvy users at work) by having data from
Quickbooks exported directly into a Specific Workbook Template. It's
simple enough, to a point. Quickbooks has a built-in export feature
that will send the info to an existing workbook. Since it is basically
a paste function, I created a blank worksheet for it to fill into, and
then had the imported data motivate the formulas (ex: formula page cell
a1= "imported page" c5 -"imported page" c6). It works like a charm,
unless you need to re-import information. I will get #REF errors, as
the export function seems to be a paste function, not a paste values
function.
Is there a way around this problem without having a hundred new
worksheets for a hundred re-imports? These templates are Cost Analysis,
so it is more than likely that there would need to be an exporting of
new data to Excel from Quickbooks every two weeks. I can have a blank
template set up, to create an updated Cost Analysis from scratch every
time, but it just seems that there should be a quick fix. Is there a
way to keep formulas safe when they are motivated by a straight paste
function on another sheet?
Many thanks,
Scott

 

[Harlan Grove]

I've been tasked with creating a "hands free" Cost Analysis Spreadsheet
(we have a lot of non-Excel Savvy users at work) by having data from
Quickbooks exported directly into a Specific Workbook Template. It's
simple enough, to a point. Quickbooks has a built-in export feature
that will send the info to an existing workbook. Since it is basically
a paste function, I created a blank worksheet for it to fill into, and
then had the imported data motivate the formulas (ex: formula page cell
a1= "imported page" c5 -"imported page" c6). It works like a charm,
unless you need to re-import information. I will get #REF errors, as
the export function seems to be a paste function, not a paste values
function.

....

You haven't been sufficiently complete in your explanation.

How *EXACTLY* does the imported data "motivate" the formulas? While this may
be clear to you, it's opaque to anyone else. Do you mean there are
pre-existing formulas that reference cells in the 'Imported Page' worksheet?
That is, you have some other worksheet containing formulas like

A1: ='Imported Page'!C5-'Imported Page'!C6

?

If so, do you mean that when you re-import, these formulas become

A1: ='Imported Page'!#REF!-'Imported Page'!#REF!

?

If so, the problem would appear to be that importing works like
*Cut*-and-Paste rather than Copy-and-Paste. The simplest solution for that,
assuming the 'Imported Page' worksheet name never changes, would be using
INDIRECT.

A1: =INDIRECT("'Imported Page'!C5")-INDIRECT("'Imported Page'!C6")

If this isn't what you mean, you need to provide *COMPLETE* details of both
the import process and how you create the formulas that reference the
imported data.