Complex question: Indirect & Array combination not working

FrankieG

New Member
Joined
Jun 13, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Dear friends,

Just now my INDIRECT formula stopped working, whilst I am 99% confident nothing changed.

To provide some context; I am pulling specific data points from a number of worksheets. To make this process easier I am using a combination of INDIRECT and array.

Exact example:

=INDIRECT("'["&i.Name.file.data.Workbook&"]"&x.Name.sheet.list.Worksheet&"'!Revenues")

My workbook array name is a single cell, since it is just one workbook (i.Name.file.data.Workbook)
My worksheet array name is a list / column with different values (Worksheet 1, Worksheet 2, Worksheet 3 etc.) (x.Name.sheet.list.Worksheet)

Suddenly this no longer works - the formula that is in there still does want it needs to do, e.g. if I change the inputs in one of the worksheets the outputs do change. But when I re-calculate it stops.

Interestingly, when I replace the array name for worksheets with a hardcoded value e.g."Worksheet 1" everything works again.

So the error seems to be in the worksheet array, as if Excel is not happy with a column (or array in the INDIRECT formula) here as opposed to a single hardcoded value. But it has worked before, so this should be possible

Very curious to your feedback and many thanks in advance

Cheers,
Frank
 

Attachments

  • Capture fg.JPG
    Capture fg.JPG
    149.4 KB · Views: 4

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

earthworm

Well-known Member
Joined
May 19, 2009
Messages
521
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think you are missing an exclamation (!" in front of sheet name
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,890
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula (INDIRECT & Array) suddenly stopped working
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top