Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
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 On Jun 18, 4:26*pm, GiNa wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
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 "Pete_UK" wrote: 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 On Jun 18, 4:26 pm, GiNa wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
On Jun 18, 12:28 pm, GiNa wrote:
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 "Pete_UK" wrote: 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 On Jun 18, 4:26 pm, GiNa wrote: 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! 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
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" wrote: On Jun 18, 12:28 pm, GiNa wrote: 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 "Pete_UK" wrote: 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 On Jun 18, 4:26 pm, GiNa wrote: 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#REF! Error
On Jun 18, 2:15 pm, GiNa wrote:
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" wrote: On Jun 18, 12:28 pm, GiNa wrote: 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 "Pete_UK" wrote: 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 On Jun 18, 4:26 pm, GiNa wrote: 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! 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |