• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interesting problem - Excel 2007 Offset formula set for range but returning valu

andrewbroadway

New Member
On the tab named "Dashboard" I am playing with the offset function to return a range of rows from the "Missed Time - AD Usage' tab.


=OFFSET('Missed Time - AD Usage'!$B$5,0,1,92,1)


That is the formula I am using. So I am starting at B5, moving 0 rows down, 1 column to the right, the height is 92 and I only want 1 column.


Yes instead of returning #VALUE, the formula actually gives me whats in cell C15.


I am so frustrated! What am I doing wrong?
 
Andrewbroadway


You rarely use offset by itself except to return a single cell


Normally offset is used to return a range and you need to do something with it

eg:


=Sum(OFFSET('Missed Time - AD Usage'!$B$5,0,1,92,1))

=Min(OFFSET('Missed Time - AD Usage'!$B$5,0,1,92,1))

etc
 
Then what do I use to return the range of cells?


I am trying to use Name's to point to a range of cells.


That range will then be used for the series in a chart.
 
Andrew

You can use your formula in a named range and then use the named range as an X or Y value in a series for a chart

These are entered using the Name Manager not into cells


Also check that there aren't any text values in the range
 
Thank Hui, I understand that. The problem I am having is my formula is returning a single value. Not a range. I have the formula setup correctly yes? What else could be the problem.
 
Andrew


Try creating the named range from scratch


Delete the old Named Range and create a new Named Range


Type in =Offset(and then use the mouse to select the starting cell

once that is correct finish the 0,1,92,1) part


That should do it for you
 
Andrew,


what hui means is this:


Your formula is right. Your application is a bit off. copy that formula from the cell and create a range with the name as "test" or something. "refers to" should be filled with your formula.
 
Back
Top