Subtotal with Named Range, result not showing

[matt4003]

Hi all,

I have a formula that looks like this:
=subtotal(4, Data)
Data = OFFSET(Sheet1!$C6,0,1,1,COUNTA(Sheet1!6:6)-2)
*the Data range selects correctly when I look in the "define name" wizard.

When I use the "function arguments" wizard
function_num "4" = MAX
Ref1 Data = {1,2,3,4,5,6}
Formula Result = 6.0

But in the sheet it self the formula does not return a result.

Any idea as to why?

Thanks,
Matt

[NBVC]

I think you need to absolutely reference the cells in the offset formula..

=OFFSET(Sheet1!$C$6,0,1,1,COUNTA(Sheet1!$6:$6)-2)

if you don't want to and it must be relative, then you need to enter the Subtotal formula in the same row... but then you will need to change 6:6 to a defined name that doesn't include the cell you are entering the subtotal formula in.