• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 19. Auditing and Validating Worksheets > Common Problems and Solutions

Common Problems and Solutions

You should run into few problems when auditing your worksheets or validating user input. The following sections discuss a few minor difficulties that you may experience while validating data in your worksheets or trying to reference names in other worksheets.

Q1:The Recalculate message keeps appearing during data validation. How can I get rid of the message?
A1: If you set up data validation rules that involve calculations, and your worksheet is set to manual recalculation, you may see a message prompting you to recalculate the worksheet when you find incorrect entries. To avoid seeing this message each time Excel discovers invalid data, choose Tools, Options, and then click the Calculation tab. In the Calculation section, click Automatic to switch to automatic calculation; then click OK.
Q2:How do I reference named cells in other worksheets?
A2: If you want to refer to other cells or ranges from other sheets in a workbook, you must include a sheet reference as well as a cell reference or name in a formula. Use an exclamation mark (!) to separate the sheet reference from the cell reference or name.

If the cell you want to reference on Sheet2 is named Q1Sales, you would use Sheet2!Q1Sales as the reference. If you have named the sheet, simply use the sheet name and then the cell name, such as Details!Q1Sales. If the sheet name includes spaces, you must surround the sheet reference with single quotation marks, such as 'Sales Details'!Q1Sales.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint