Change Queries in a Worksheet Without Affecting References

  • by Robert Oliver, Investment Banking IT
  • March 1, 2004
The results for multiple queries are often placed in one Excel worksheet, which is then referenced to other sheets in the workbook. This practice, which is embraced by many, can cause problems, however, if a query changes the cell, column, or row structure in the sheet containing the results areas. The author demonstrates a easy way to change a sheet's structure so it can accommodate new data in one area without altering the rest of the worksheet, allowing you to preserve the cell references used throughout a workbook.


BW users often rely on the results areas for multiple queries residing in a single Excel worksheet to act as data feeders referenced by other sheets in the workbook. Sheets containing elements such as charts, graphs, or data tables commonly reference the various result area cells.

This arrangement is not without drawbacks because it can result in problems if the cell, row, or column structures change in the sheet containing the results areas. When queries are refreshed to provide new information, any changes made to one result area can potentially affect the results areas for the other queries on the sheet.

Allowing the BW system to insert new rows or columns automatically when a query changes can alter the structure of the cells and throw the references out of synch. It is then up to the BW report developer to reset all the references so that the graphs plotted on other sheets are rendered correctly.

Robert Oliver

Robert Oliver is an SAP BI developer working for an investment bank in London. He has been working with SAP R/3 since 1994 and SAP NetWeaver BW since 1999. Since 2005 he has been working with the planning and consolidation systems provided by SAP. Prior to his current role he was an end user and a consultant.

If you have comments about this article or BI Expert, or would like to submit an article idea, contact the BI Expert editor.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.