Offset Errors with Ranges

[Zeitlin]

Folks,

I am trying to create a dynamic chart using the following defined
name:

=OFFSET('Data Input'!$C$116,0,0,0,(VLOOKUP(Report_Month,Lists!$A$1:$B$56,2)))

It didn't seem to work, so I copied and pasted the formula into an
excel cell and got a #ref error. I then experimented for two hours
and can't get it to recognize a proper formula. I can't use the
'acount' function since I have some cells with blank data.

I went back to remedial Offset 101 training and even tried from
scratch to build what MS has in their help area on 'offsets' as an
example:

=OFFSET(C3:E5,-1,0,3,3)

and with that I get a #value! error. No matter what I do, I can't get
Excel to recognize a range for the offset.

I am at a loss. What am I doing wrong? Is there a bug or an option I
need to turn on?

Any help is greatly appreciated.

v/r

Paul Zeitlin

 

[Frank Kabel]

Hi
several things:
1. Your formula:
=OFFSET('Data
Input'!$C$116,0,0,0,(VLOOKUP(Report_Month,Lists!$A$1:$B$56,2)))
would result in a range which has heigth (3rd OFFSET parameter) or
zero. As this is not a valid reference you get a #REF error
Some additional notes:
- not sure what your name 'Report_Month' represents. It should refer
only to a single cell
- you should also add the 4th paramater to the VLOOKUP function (FALSE)
to ensure an excat match

2. Your formula
=OFFSET(C3:E5,-1,0,3,3)
would return a multicell range (not a single cell) what would your
expected result be for a formula like
=C2:E4
in a single cell. This would also return #VALUE
You may try something like
=SUM(OFFSET(C3:E5,-1,0,3,3))
this should return the sum for this range

 

[jeff]

hi,

Enter you offset =OFFSET(C3:E5,-1,0,3,3)
as an array (ctrl+shift+enter). go from there.

jeff

 

[Zeitlin]

Gentlmen,

Thanks for your help. The information helped clear things a bit.

Report_Month refers to one cell. It is a drop down list of dates that
sync to the vlookup range.

What I am trying to do is the classic dynamic chart that adds months
as time goes by. So what I am trying to do with the offset is, based
on the 'report_month' chosen by the user, the graph will use a data
range that goes from inception to that date.

I understand the height cannot be 0, and I fixed that. However, the
answer the resultant is '0'. When I use it as the defined name in my
graph I get an erroneus graph.

Anything else you can clarify. This is one of a very select few
problems I can't seem to figure out with my excel experience.

Your experise is much appreciated.

73,

PAZ

 

[Zeitlin]

Guys,

I got it to work! Thanks for your help.

However, got one more problem with my dynamic chart. The defined
offset name works when I start a new graph (i.e. the month I choose
from my drop down list is represented on the graph). However, If I
change the reporting month, the graph does not change. But, if I
start with a brand new graph it will demonstrate the new month
appropriately. I tried F9 thinking I needed it to manually
recalculate...to no avail.

Any further guidance you guys can help with?

Thanks.

v/r

Paul Z.