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 in Database by Edureka
• 9,320 points
21 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 by Edureka
• 8,820 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 in Database by Edureka
• 8,820 points
28 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
579 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 in Database by Edureka
• 8,820 points
37 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 in Database by Edureka
• 8,820 points
27 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 in Database by Edureka
• 8,820 points
42 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 in Database by Edureka
• 8,820 points
41 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP