Problems using Indirect() to refer to arrays

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
Hello, people! Here is my problem, and I hope it moves you to reply:

I need to refer to an array on another sheet, but at the time of writing the formula, I do not know the name of that sheet, and the sheet does not even exist (it is created and named by my macro). When it is created, the name will be put into cell A1 (for eg) and I will then refer to that cell to get the name of the sheet from which I need to extract data. In order to do this (see The Specific Task, below) I need to use something similar to this formula, which is random and useless, but encapsulates the error that is causing me problems, without reams of references etc.

=IF(INDIRECT(A1&"!AS"&ROW(1:2))=2,1,0)

When I evaluate this formula using Excel 2007, It gets to this step (the worksheet needs to function in Excel 97 by the way)

=if(indirect({"JDM!AS1";"JDM!AS2"}) = 2,1,0)

and the indirect command on an array leads to an error. Is there any way to refer to arrays in sheets whose name is unknown at the time of writing the formula, and if so, how is it done?

The Specific Task
I need to collect data sheets of daily activity of 20+ staff members, and bring the data together into one workbook, which pretty graphs etc for supervisor types to pore over. I have done this by opening each activity record, and pasting the data into my workbook, with a separate worksheet for each person the supervisor chooses to sample. I can collect data from single cells on each sheet, such as totals, with no probs, but they want me to be able to show the average activity on Mondays, Tuesdays etc for that person throughout that month. This means I need to look up which days of the month are Mondays etc, and then look up the number of payments made, customers called etc on Mondays, Tuesdays etc and take the average. for each person. I only know how to do this using arrays, and hence the problem as the sheet names to be used are unknown until the macro runs. I do not want to make the macro any longer - it is slow and clumsy already, and besides which... This should not be as hard as I am finding it! The name will be listed in a cell, on the sheet where the formula is, at some point, so why can that not be used? Or can it?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
Cant you just test for blank before the INDIRECT, e.g.

=IF(A1="","",INDIRECT(...etc))
 

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
The errors occurs when Indirect() tries to manage an array - I am typing the formula with a trial value in A1, so I can see whether it would work once a name (in this case JDM) has been put in A1. By the time the formula is viewed, A1 will not be blank.

Thanks for looking at this, and hopefully a solution will present itself. Apologies for a (I am sure ) very unclear writing style.
 

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
Sorry - I will at least make a formula which does something to demonstrate this problem:
{=IF(SUM(IF(INDIRECT(I3&"!AS"&ROW(1:2))=2,1,0))>=1,1,0)}

where A1 is the name of the sheet from which the data in cells AS1 and AS2 is to be sampled.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,213
Are you putting "JDM" in cell A1 before you make the JDM worksheet?

Also, since you are putting JDM in A1 with the macro, you could have the macro put the array formula in the other cell as well without the need for using the indirect function.
 

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
The JDM sheet appears before the name is put into Cell A1. I am trying to avoid putting new things into the macro - it runs really slowly already and I am scared of making it take even longer - I was hoping to have the formulae already in situ awaiting the sheet names which would allow them to return a value.

Maybe I should just give up on this quest and do as you say; put it in the macro and maybe find a way to slim the code down to speed it up; I just thought that there must be a way, but "must be" is not the same as "is", Thank you for your time anyway, and I think sense has probably triumphed over idealism.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,769
Hi

Try:

=IF(SUM(IF(N(INDIRECT(I3&"!AS"&ROW(1:2)))=2,1,0))>=1,1,0)

... confirmed with CTRL+SHIFT+ENTER.
 

High Plains Grifter

Board Regular
Joined
Mar 9, 2010
Messages
129
Wonderful! Thanks - a new function for me, which will take a bit of investigating, but you have solved the problem beautifully - Thank you very much :)
 

Forum statistics

Threads
1,089,283
Messages
5,407,366
Members
403,137
Latest member
ExcelGzh

This Week's Hot Topics

Top