[Solved] Stumped: INDIRECT & Dynamic Ranges

    OK, a poser for you... It's got me stumped :)


    Three Dynamic ranges are defined as follows


    ACT04 =VisionACT!$A$3:OFFSET(VisionACT!$A$3,COUNTA(VisionACT!$A:$A)-1,COUNTA(VisionACT!$3:$3)-1)


    ACT04C =VisionACT!$A$3:OFFSET(VisionACT!$A$3,0,COUNTA(VisionACT!$3:$3)-1)


    ACT04R =VisionACT!$A$3:OFFSET(VisionACT!$A$3,COUNTA(VisionACT!$A:$A)-1,0)


    All work fine and refer to the correct data


    Formulas in my outputs spreadsheet are as follows


    =INDEX(INDIRECT(F$9),MATCH($E14,INDIRECT(F$10),0),MATCH(F$8,INDIRECT(F$11),0))


    Where F9 has the value ACT04
    Where F10 Has the value ACT04R
    Where F11 has the value ACT04C


    .......


    Now here's the issue....


    When the formula was first constructed it worked fine. The coorect data was returned to my table from the dynamic range ACT04 using ACT04R and ACT04C as the dynamic column & Row references..


    However, when i refresh the data using the following code


    Code
    With Sheets("VisionACT").Range("ACT04")
 .ClearContents
End With


    and rerun my query that repopulates the sheet.. even though the ranges are reset and appear to work, the formulas (that use INDIRECT) revert to a #REF error
    value....


    Anyone know why this is and care to share this pearl of wisdom with me?
    I thought it was all quite neat in theory...


    Thanks,


    Will

    Hi Will,


    I think your problem is caused by the INDIRECT and MATCH functions trying to look at a range which has zero length after you've Cleared Contents in ACT04.


    Perhaps you need to initialise ACT04 as a 1-cell range after you've Cleared it, maybe by making F9 = "VisionACT!$A$3".


    I've got a similar problem, charting dynamic ranges which get cleared occasionally. I had to include a sheet-specific reference in the cell looked at by the INDIRECT function, which is then overwritten when you put some data in the named range.


    Hope that helps


    Chris

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

    If someone can convince me that INDIRECT can't really handle volatile referencing, then I'll code it OK, and reset the ranges as the data gets repopulated... I just thought that in theory it was a neat solution and would save hard coding it... but it seems not.


    Will

    Hmmm...


    If the outputs spreadsheet isn't on "VisionACT", maybe you need to include the sheet reference in the range name, viz:


    Where F9 has the value VisionACT!ACT04
    Where F10 Has the value VisionACT!ACT04R
    Where F11 has the value VisionACT!ACT04C


    Failing that, it might have something to do with the Undo stack being cleared when you recalculate . . . Maybe "The Professor" can help?!


    I'm as stuck as you, now!


    Chris

    Cheers,
    Chris


    "Varium et mutabile semper Excel"


    It shouldn't need a sheet reference....


    for example, the hard coded version as follows works fine


    =INDEX(ACT04,MATCH($E14,ACT04R,0),MATCH(F$8,ACT04C,0))


    no problems there....


    I wanted to use the INDIRECT cell reference though so that three cells that refrerence the relevant ranges could be changed as opposed to about 4,800 formulas... :(


    hmmmmm


    Thanks for your inputs though buddy


    Will

    I've got a feeling that it's because INDIRECT expects a string argument, so that:


    =INDIRECT(F9) works if F9 contains (say) $A$3, but fails if F9 contains ACT04.


    To make INDIRECT work, it needs to be something like:


    =INDIRECT("OutputSheet!"&$F$9)


    Any Good?


    I'm as curious about this as you, now!


    Chris

    Cheers,
    Chris


    "Varium et mutabile semper Excel"

    Ah but then why would INDIRECT initially work, which it did prior to the dynamic ranges being cleared & repopulated with data....


    That was my issue...


    =INDEX(INDIRECT(F$9),MATCH($E14,INDIRECT(F$10),0),MATCH(F$8,INDIRECT(F$11),0))


    The formula initially worked fine until i used the code to clear ACT04 and then downloaded some fresh data, thus resetting the dynamic range... INDIRECT since then has returned the #REF error, suggesting that it lulled me into thinking it was OK with dynamis ranges only to fall over later....


    I just wondered why it initially worked & then failed.... all because of .clearcontents.....


    Will

    Will play with this one, not so sure you can drag intrect as its suppost to be text regardless what you call, but im telling you what you know sorry !


    So you can drag the range as its not a range its text and stays that way. Saying that lets hope im proved wrong!



    Jack


    =INDEX(INDIRECT("F9"),MATCH($E14,INDIRECT("F10"),0),MATCH(F$8,INDIRECT("F11"),0))

    Hey Will


    I don't know how you got it to work in the first place. I can't get indirect to work with dynamic ranges at all.


    The only way I can get it to work is the take out the dynamic calcs and make the name a fixed ref.


    Not much help

    There are three types of people in this world.
    Those who can count and those who can't.

    One of your past PMs said "Never Never Never Give Up!" - how did he know about Excel?


    Have a look at the attached sheet. I've tried to keep to your references and sheet name.


    I think I've just invented another way to create dynamic ranges.


    The ranges are created in the VisonAct sheet in Row 1. Using the address function and counta. I then called each of the three ranges the relevant range name.


    In the table sheet there are two method of getting the table. The first uses two INDIRECTs per range the second just one INDIRECT per range - probably the better way.


    Will - I don't know if this is workable for you, but it handles dynamic ranges within an INDIRECT function.


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

    Slight error - not major in the second table in Table sheet. See attached file for correction - it was just a ref left the same as the first table.

    There are three types of people in this world.
    Those who can count and those who can't.

    Neale,


    I'd used something simlar to your second formula to get it to work, not sure how I even got it to work in the first place now but i think the fact that my claculation was set to maual was something to do with it initially!!


    Here's an xample book, although I'm getting the idea that I will have to refer to the range names themselves as opposed to the cell references.


    thanks for the input


    Will

    Hi Will,


    The use of INDIRECT and dynamic named ranges appears very flakey indeed!


    I have modified your example to use INDIRECT on dynamic cell content. It appears to be a little more stable.


    Hopefully this will be of use.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Andy,


    Thanks.... you thoroughly deserve your new title :biggrin:


    I still think INDIRECT is a bit too flaky to use until I have tested your example to destruction, but it's certainly stable enough to return data (which beats my efforts to date!! :o )


    Thanks for the input....


    Will

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login