Excel indirect reference produces a ref error

0 votes

I am trying to dynamically reference a cell on a different open and visible worksheet on the same workbook by using the INDIRECT function. When I make the cell reference with a static value, it works fine, but does not if the reference is not static. With the code snippet below, $A$31 is the sheet name, F9 is the cell reference for that sheet. This works fine. However, it is the F9 than needs to be dynamic, such as G9, H9 and F10, F11.

=INDIRECT("'" & $A$31 & "'!F9")

I would have thought the following snippet should have been the change to make. However this only results in a #REF error

=INDIRECT("'" & $A$31 & "'!" & F9)

What changes do I need to make, and why am I getting the error?

Apr 6, 2022 in Database by Edureka
• 13,690 points
1,795 views

1 answer to this question.

0 votes

The formula =SUM(B2,C2,D2) is used in column E in the following example.

A formula using explicit cell references like =SUM(B2,C2,D2) can cause a #REF! error if a column is deleted.

A #REF! error would occur if column B, C, or D were deleted. We'll eliminate column C (2007 Sales) in this scenario, and the formula will now be =SUM(B2,#REF!,C2). When you use explicit cell references like this (where each cell is referenced separately, separated by a comma) then delete a referenced row or column, Excel is unable to resolve the reference and returns the #REF! error. This is the main reason why explicit cell references are not recommended in functions.

Example of the #REF! error caused by deleting a column.

If you mistakenly erased rows or columns, you can restore them by using CTRL+Z or clicking the Undo button on the Quick Access Toolbar.

Make the formula, like =SUM, utilise a range reference instead of individual cells (B2:D2). You can now delete any column inside the sum range and Excel will adjust the calculation automatically. For a sum of rows, you could write =SUM(B2:B5).

answered Apr 11, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

When there was no unhidden workbook open ...READ MORE

answered Apr 6, 2022 in Database by gaurav
• 23,260 points
861 views
0 votes
1 answer

showing an error in a MySQL trigger

use this code: CREATE TRIGGER sample_trigger_msg ...READ MORE

answered Sep 27, 2019 in Database by Omaiz
• 560 points
2,532 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
782 views
0 votes
1 answer
0 votes
1 answer

Prevent cell numbers from incrementing in a formula in Excel

In Excel, you can use a feature ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
9,979 views
0 votes
1 answer

Excel 2003 - Match Column A with Column B & Column C to correspond to Column B

Write a standard IF formula that compares ...READ MORE

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
2,882 views
0 votes
1 answer

I have a Run Time Error 91 for an Excel Add In

"Runtime Error 91: Object variable or with ...READ MORE

answered Mar 30, 2022 in Database by gaurav
• 23,260 points
19,724 views
0 votes
1 answer

Excel problem for beginners: #REF! Error When referenced Row is deleted or Added

You'll run into this problem if you ...READ MORE

answered Apr 4, 2022 in Database by gaurav
• 23,260 points
495 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP