Dynamic reference in Offset-function for Define Names, Refers to.

[mange_hit]

Is it possible to have a dynamic reference for the Offset-function in
"refers to" for a defined name?

I've tried something like this in "refers to":
=OFFSET("B"&MATCH(1,Sheet1!B:B,0),1,0,CountA(Sheet1!$B:$B),1) and it's
not accepted by Excel.

The idea is to find the cell with the first 1 in column B and use that
cell as reference.
Of course the "B"&MATCH(1,Sheet1!B:B,0) -part works excellent if
entered in a worksheet cell.

Are there any restrictions which formulas can be used with Define
Names?

Any ideas appreciated.

 

[mange_hit]

I found the answer. Adding INDIRECT to the formula works;
=OFFSET(INDIRECT("B"&MATCH(1,Sheet1!B:B,0)),1,0,CountA(Sheet1!$B:$B),1)

 

[mange_hit]

Well the define named seemed to work (=clicking on "refers to"
highlights the correct cell range), however when placing the names as
sources for a chart - Excel either crashes or I am left with blank
chart with no source data.

I have two defined names based on OFFSET(INDIRECT("B"&MATCH(1,Sheet1!
B:B,0)),1,0,CountA(Sheet1!$B:$B),1) - type formulas and another three
names that are offsets to these two.

If I replace the formulas for the 2 defined names with direct cell
references, e.g B17:B57, the chart shows data correctly - and again -
if I replace the orginal formulas the chart goes blank (and the series
are not even found as source data for the chart). The formulas and the
direct cell references refer to the same cell areas.

It seems that defined name/refers to, evaluates formulas different
than a chart?

I am using a column chart as described here: http://people.stfx.ca/bliengme/ExcelTips/Columns.htm

Any ideas are much appreciated.