Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •