Imbedding formulas within Offset function??

[roscoe]

I've taken great advantage of using offset to generate dynamic named ranges (for plotting to reduce data point count) such as:

=OFFSET($A$3,0,0,COUNT($A:$A),1)

...however now I find the desire to replace the hard column references within the Offset formula ("$A$3" and $A:$A") with imbedded formulas. Why? I want to be able to reoroganize and/or insert data on my worksheet without having to recode VBA as to where the data gets written. Currently doing this by using Match() against Column header names (in a table) to determine the column number ("13") in which the header occurs, and converting that to a column reference ("M"). Then in VBA I use vlookup to find the column number/reference and use those to point where the data should be written. Sounds convoluted and there's probably a better way but...

So far so good. However, I then use dynamic ranges as above to plot the data and this is where I'm stuck. I have already created strings "$A:$A" and "$A$3" from the table above but I can't seem to get the named range offset formula to work when I insert them. I get a generic error that says that my formula is wrong but without enough guidance to figure out what I need to fix (or maybe I can't do this).

The formula to determine the column reference for Header "Test" is:

="'Graph Data'!$" & VLOOKUP("Test", Graph_column_Table,3,FALSE) & "$3" [produces "$A$3']

where "Graph_column_Table" is the 3-column range with (1) header names (2) calculated column numbers and (3) calculated column references. When I try to insert this formula into the Offset formula above it fails:

=OFFSET("'Graph Data'!$" & VLOOKUP("Test", Graph_column_Table,3,FALSE) & "$3",0,0,COUNT($A:$A),1)

(Note I have an even longer formula for calculating "$A:$A" but left it out to avoid any more confusion that I've already created )

Why is this failing, and is there a better way??

Thanks

 

[jasonb75]

Those are not cell references, they are text strings. To convert them to valid references you need to use INDIRECT

=OFFSET(INDIRECT("'Graph Data'!$" & VLOOKUP("Test", Graph_column_Table,3,FALSE) & "$3"),0,0,COUNT($A:$A),1)

Is there a better way? Usually where OFFSET and INDIRECT are involved, there is a better way. Both are volatile functions, excessive use of them can cause noticeable lag in calculation times, however for what you are trying I don't think that it will be possible, or at least not practical with an alternative method.

 

[roscoe]

Yea, I realized INDIRECT() was the answer about 69 seconds after I hit send. Sorry to have wasted your time. As for lack of a better another method, alas I was afraid that might be the case.

Thanks

 

[jasonb75]

One method that might work, using 3 named ranges as shown in bold. Note that the formula for the second range refers to row 100000, this row needs to be beyond the maximum scope of your data but not too close to the end of the sheet. If your data goes beyond the row in that range, or if the range moves up when you delete / move data then it will fail. Likewise if the row is too close to the bottom of the sheet, inserting rows of data could cause it to exceed the sheet limit of row 1048576.

ColTop =INDEX($1:$1,0,MATCH("Test",$1:$1,0))

ColBot =INDEX($100000:$100000,0,COLUMN(ColTop))

FullRng =ColTop:INDEX(ColTop:ColBot,COUNTA(ColTop:ColBot),0)

 

[roscoe]

I'll have to play with that, thanks!