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?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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,166
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,750
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,082,126
Messages
5,363,317
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top