#REF! Error

[GiNa]

We have a software system on our server that contains our company data. I
have created reports using the Table wizard in Excel that accesses this data.
The data gets dumped into "Worksheet 1" and I have formatted the cells in
"Worksheet2" to refer to the corresponding cells in worksheet 1 and be
displayed as a dashboard for executives.

Its all fine for the current data..but when I run the reports and "refresh"
the data from the server, the new data gets dumped correctly into Worksheet
1. However, the data in Worksheet 2 shows #REF! error.

(Row 5 is the header for both worksheets.. and the data startes form Row 6.
worksheet2 Row 6 refers to Worksheet1 Row 6 and so on.).. when the new data
comes in, the top rows below the header in Worksheet 2 displays the #REF!
error and that cell does not refer any cell =Worksheet1#REF! )

Please help!

 

[Pete_UK]

Your "refresh" action is deleting rows in Sheet1 before writing the
new data - this messes up the formulae in Sheet2 as the cells that
they referred to no longer exist.

A slightly different approach which would avoid this is to just have
Sheet1 updated as it is currently, and then to manually copy the
complete data from Sheet1 to a new sheet. The formulae in your current
Sheet2 should refer to the new sheet rather than directly to Sheet1
(you can use Find and Replace in Sheet2 to change references to
Sheet1! to Sheet3!, assuming your new sheet is named Sheet3).

Then when you refresh Sheet1 you just have to copy that data verbatim
into Sheet3.

Hope this helps.

Pete

 

[GiNa]

Thanks Pete!

Unfortunately, I can't copy this manually everytime because this report
exists as a dashboard for various people across the organization. Executives
just want to refresh and get the latest data!

Is there some way to ensure all the data in Sheet 1 automatically gets
copied to Sheet 2 everytime it is refreshed?

Thanks
Gina

 

[Spiky]

Thanks Pete!

Unfortunately, I can't copy this manually everytime because this report
exists as a dashboard for various people across the organization. Executives
just want to refresh and get the latest data!

Is there some way to ensure all the data in Sheet 1 automatically gets
copied to Sheet 2 everytime it is refreshed?

Thanks
Gina

I don't know how your download happens exactly, but is it possible to
do this download/refresh command with a Clear instead of Delete? That
would solve the problem without the 2nd data area.

Or, an event macro could be added to do what is mentioned above, to
copy (without Deleting) to the 2nd area automatically after the
refresh is done.

 

[GiNa]

Spiky - thanks!

I don't know how your download happens exactly, but is it possible to
do this download/refresh command with a Clear instead of Delete? That
would solve the problem without the 2nd data area.

Or, an event macro could be added to do what is mentioned above, to
copy (without Deleting) to the 2nd area automatically after the
refresh is done.

How do I check if the refresh command has Clear or Delete? I have no clue
about adding event macros.. can you pelase explain in layman's terms?

thanks
GiNa

 

[Spiky]

Spiky - thanks!



How do I check if the refresh command has Clear or Delete? I have no clue
about adding event macros.. can you pelase explain in layman's terms?

thanks
GiNa

I don't know anything about the Table Wizard, so I don't know how your
refresh works. Maybe someone else will comment. Or perhaps the author
of your file would be a resource.

But an event macro is automatically triggered by something you do. It
could be choosing a different sheet, opening the file, clicking on a
certain cell, etc. These are stored under "Objects" in the VBA editor
instead of under "Modules" like most macros.