Results 1 to 3 of 3

20090228, 09:53 #1
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
... or if that can't be done, how can a macro be tweaked to change a range formula to accomplish the same thing?
I've tried help myself for a while with this but can't  could anyone give me a steer?
I am using range formulas on a "View" sheet to summon any one of 32 data blocks from a seperate data sheet. The latter consists of named and identically sized ranges, e.g.
data_1
data_2
up to
data_32.
Let's say each one is a 10 X 10 cell square..
If on the View sheet I want to summon data from the 5th data set, for example, on the View sheet I write the range formula
{=data_5}
across a 10 x 10 cell range.
It works, but it makes summoning a new data set, and scrolling through many of them, very clumsy and slow  if I want to change so that I view the 6th data set, I have to select the 10X10 range and use search and replace to change the "5" in the range formula to a "6", or I have to delete the existing range formula and type in a new one (i.e. {=data_6} )
Is there a way to have the array formula reference a number which is set by the user in a seperate cell? I tried something which failed  specifically, I typed "6" in cell a1 of the View sheet  i.e. NOT part of the range with the range formula  and then changed the range formula to
{=data_(a1)}
but no good, error messages in every cell.
Can this be done somehow?
If not: another fix would be a macro which reads from cell a1, selects the range with the formula to be changed, deletes the existing formula, and writes a new range formula which uses the number in cell a1 , for example, if there's a "6" in cell a1, the formula the macro would write {=data_6}.
I've tried recording a macro to do this but I can't seem to make VBA understand what's the value in cell a1. Here's how I tried it  the macro will
 select the value in cell a1  I actually select this from within the formula bar, when cell a1 is selected
 copy this to the clipboard (Edit/ Copy)
 select the range which will contain the range formula
 write:
=data_
 after the underscore, paste the value from the clipboard (Edit/ Paste) so that the formula reads
=data_6
 enter it as a range formula (control + shift+enter) to make it
{=data_6}
The problem I'm having is that the macro doesn't remember the value in cell a1.
Can anyone help with either solution?
I'd be grateful for any help

20090228, 10:02 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
You can use the INDIRECT function for this:
Enter the number (1, 2, ..., 32) in a cell, say in A1.
Select the 10x10 range where you want the formula.
Enter the formula
=INDIRECT("data_"&A1)
Confirm with Ctrl+Shift+Enter to make it an array formula.
Now you only have to change the number in A1 to display a different named range.

20090228, 13:10 #3
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
Hans, thanks. You won't believe how much this helps!