Problems using Indirect() to refer to arrays

[High Plains Grifter]

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?

 

[Special-K99]

Cant you just test for blank before the INDIRECT, e.g.

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

 

[High Plains Grifter]

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]

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]

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]

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]

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]

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