exporting to excel and manipulating data


T

Todd

Has anyone successfully exported a report to excel and then had
formulas that pull out the necessary data from a different sheet?
And then been able to re-export the data to update your "formulated"
report?

I can't get this to work.

Every time I export the data to the same workbook and worksheet
(over-writing the old data), it invalidates all my formulas that
pointed to those cells!!!!

As a clue, I had a range that was in a formula
=match("Total",QB_DATA!A1:A50,0)

After I exported the quickbooks data again, it CHANGED my formula to
=match("Total",QB_DATA!A31:A50,0)

The quickbooks report only went down 29 rows, so it's like it removed
all references to all the cells that it replaced.

HELP!!!

Any ideas?

Many thanks.
 
Ad

Advertisements

G

greg

Has anyone successfully exported a report to excel and then had
formulas that pull out the necessary data from a different sheet?
And then been able to re-export the data to update your "formulated"
report?

I can't get this to work.

Every time I export the data to the same workbook and worksheet
(over-writing the old data), it invalidates all my formulas that
pointed to those cells!!!!

As a clue, I had a range that was in a formula
=match("Total",QB_DATA!A1:A50,0)

After I exported the quickbooks data again, it CHANGED my formula to
=match("Total",QB_DATA!A31:A50,0)

The quickbooks report only went down 29 rows, so it's like it removed
all references to all the cells that it replaced.

HELP!!!

Any ideas?

Many thanks.
I have just tried and using QB2004 it works okay, maybe the formula u
are using?
 
J

John

It looks like the QB update inserted the new report at the top of the
worksheet and then cleared the contents of the old cells. Too bad for your
formula.

If you close your worksheet (in a different workbook) while you update the
QB report, then reopen your report, it will work. This is because the
closed worksheet won't know about the update and won't dynamically change.

Or, if you send the new report to a new worksheet within your open workbook,
then copy/paste it on top of the old QB report, the formulas will be OK,
too.

What is it you're trying to do?
 
T

Todd

I'm taking a sales report that shows sales by month and trying to
format it into a useful one-page summary comparing sales to the
last few years and projecting out sales to the end of the month.
This is a report that we had with the old accounting system, and
I'd like to duplicate it.

I had excel closed when I tried to "export" the data from Quickbooks.

I'm using QB 2004 with all the patches and Excel 2000.

Everyone seems to think this is easy, so maybe I'm just doing it wrong.

I'm clicking "export" from the report and specifying the same filename
and sheet that the last "export" went to. But my formulas on other
sheets in that work-book conver to #REF! when I check on them after the
second export. I close excel between these attempts.

The formula in my cell that references the QB data is
=Sheet1!F29

and once I re-export it changes to
=Sheet1!#REF!

Both my worksheets are in the same workbook, and yes, it is closed when
I run "export" from Quickbooks.

What am I doing wrong??????
Thank you.
 
A

Allan Martin

Todd said:
I'm taking a sales report that shows sales by month and trying to
format it into a useful one-page summary comparing sales to the
last few years and projecting out sales to the end of the month.
This is a report that we had with the old accounting system, and
I'd like to duplicate it.

I had excel closed when I tried to "export" the data from Quickbooks.

I'm using QB 2004 with all the patches and Excel 2000.

Everyone seems to think this is easy, so maybe I'm just doing it wrong.

I'm clicking "export" from the report and specifying the same filename
and sheet that the last "export" went to. But my formulas on other
sheets in that work-book conver to #REF! when I check on them after the
second export. I close excel between these attempts.

The formula in my cell that references the QB data is
=Sheet1!F29

and once I re-export it changes to
=Sheet1!#REF!

Both my worksheets are in the same workbook, and yes, it is closed when
I run "export" from Quickbooks.

What am I doing wrong??????

You are assumming that the warning message that displays telling the user
that all data and formulas on the exisiting worksheet will be over written
somehow does not apply to you. Hint! that message also applies to you.

Place all formulas in a different workbook, not the one you are exporting
to.
 
Ad

Advertisements

T

Todd

Argh!!! THANK YOU!!

I thought about that, but the description in the help sheet it prints
for you SPECIFICALLY says "worksheet" and not "workbook".

But nonetheless, that was my problem. Maybe they were talking about
just formatting a report and not "updating" it.

Thank you very much for helping me out. I really appreciate it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Export Data to Excel 1
export data to excel 0
Exporting data to EXCEl compatible format 3
Exporting to Excel 5
export to excel 6
Export to Excel 1
Export to EXCEL 2
Export to Excel 2

Top